Last updated December 12, 2022

You can set up several types of relationships between objects. This article covers how to handle these relationships with Heroku Connect when writing data to Salesforce.

Simple Relationships Using Salesforce IDs

A simple relationship can be created in two steps. The first step is inserting the parent record. The second step is inserting the child record with a reference to the parent.

Salesforce’s native relationship fields use 18-character unique record IDs from the foreign record. You can always create a relationship from your Postgres database when you have available IDs. After inserting a record, the 18-character IDs are available in the sfid field. You can establish a relationship between existing records using an INSERT or UPDATE statement when the sfid for the parent record isn’t null.

For example, the standard Salesforce Contact object has a predefined reference to an Account object through the AccountId field. You insert an Account record with:

INSERT INTO salesforce.account (name, description)
 VALUES ('My new account', 'Very important account.')

The record (with id 1234) propagates to Salesforce and Connect writes the record’s assigned Salesforce ID back to the sfid field. You can then establish the relationship using the 18 character sfid value and the AccountId field:

INSERT INTO salesforce.contact (firstname, lastname, accountid)
SELECT 'John', 'Smith', sfid
FROM salesforce.account where id=1234;`

While this method works in most cases, it’s inconvenient if you want to insert the Account and the Contact at the same time. The following section discusses how to insert both at once.

Simple Relationships Between Two Objects and Relationship External IDs

While using Salesforce IDs works in practice, it complicates things when using Heroku Connect or the Salesforce API. The ideal approach is to set up both insertions, without waiting for the Salesforce ID from the first insertion to make the second.

The child record insertion can refer to the parent record via the parent’s External ID. Heroku Connect allows you to map an extra relationship field on the child record, configured to use the parent’s External ID. If you have multiple External IDs on the parent record, use the one configured as the unique identifier.

For example, assume that you’ve configured an External ID field called External_ID__c on the Account object. Contact has a standard reference to Account through the AccountId field. On the Contact mapping create/edit page, add the relationship field Account__External_ID__c to populate this reference in two steps.

INSERT INTO salesforce.account (name, description, external_id__c)
 VALUES ('My new account', 'Very important account.', gen_random_uuid());

Assuming the previous insert captured primary key id, insert the Contact record:

INSERT INTO salesforce.contact (firstname, lastname, account__external_id__c)
SELECT 'John', 'Smith', external_id__c
FROM salesforce.account where id=1234;

This relationship now accurately populates in Salesforce when Heroku Connect processes the generated trigger log entries.

Circular References Between Two Objects

A circular reference occurs when two things refer to one another. For example, here’s how to define a reference from Account to the primary contact for the account.

In Salesforce, add a field to the Account object that points to the primary contact (primary_Contact__c). In Salesforce, add an External ID field to the Contact object called ext_id__c. In Heroku Connect, edit the Account mapping to map the relationship field primary_Contact__r__Ext_ID__c. You can use this field to set up the cyclical relationship.

Assume the Contact example in the previous section has an ext_id__c field that was set during that INSERT statement (ext_id__c = 4321). The following SQL snippet sets up the cyclical relationship:

UPDATE salesforce.account set primary_contact__r__ext_id__c = c.ext_id__c
FROM salesforce.contact AS c where c.id = 4321 and salesforce.account.id = 1234;

Self-Referential Relationships

A self-referential relationship is a special case of circular reference where the reference is to another record in the same object. Salesforce can’t insert a child record referencing a parent from the same object in one SOAP CREATE message. The self-reference must be done in a subsequent update rather than in the original insert.

The Account object has a ParentID field that refers to another Account. You can map the Parent__External_ID__c field and use it to establish the relationship between a child Account record and its parent. You can create two Account records in this way using inserts:

1. INSERT INTO salesforce.account (name, description, external_id__c) VALUES ('My new account', 'A very important account', gen_random_uuid());

The account row inserted by the statement includes an id column, which is an autoincrementing integer column and serves as the primary key for the table. This column is different from the sfid column, and is a database-specific column, not part of Salesforce. Let’s say the autogenerated value of the id column in this case is 1234.

2. INSERT INTO salesforce.account (name, description, external_id__c) VALUES ('Child of My new account', 'Sub account of 1234', gen_random_uuid());

An id column is included in the row inserted, and let’s assume the autogenerated value now is 1235.

Capture the id values from the first and second INSERT statements and establish the relationship:

UPDATE salesforce.account AS c set parent__external_id__c = p.external_id__c
FROM salesforce.account AS p WHERE p.id = 1234 AND c.id = 1235;

Polymorphic Relationships

A polymorphic relationship is a relationship where the referenced objects can be one of several different object types. For example, an Event object can be related to an Account, or a Campaign, or an Opportunity through the ‘What’ polymorphic relationship. Salesforce can identify the related object in a polymorphic relationship via the object ID and the object type. To create a polymorphic relationship with Heroku Connect, use the 18-character Salesforce ID as described in Simple Relationships using Salesforce IDs.