Handling Object Relationships in Heroku Connect
Last updated October 18, 2022
Table of Contents
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
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
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
When using the Merged Writes algorithm, it isn’t possible to accomplish circular references reliably with relationship external ID fields. Use the 18-character Salesforce IDs as described in Simple Relationships using Salesforce IDs instead.
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 (
In Salesforce, add an External ID field to the Contact object called
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;
When using the Merged Writes algorithm, you can’t set self-referential relationships reliably using relationship external ID fields. Use 18-character Salesforce IDs as described in Simple Relationships using Salesforce IDs instead.
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:
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 above will include an
id column, which is an autoincrementing integer column and serves as the primary key for the table. This 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
INSERT INTO salesforce.account (name, description, external_id__c)
VALUES ('Child of My new account', 'Sub account of 1234', gen_random_uuid());
id column will be included in row inserted above, and let’s assume the autogenerated value now is
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;
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.
Heroku Connect doesn’t support mapping external IDs for polymorphic relationships. External ID relationships for What and Who polymorphic relationship fields aren’t available when creating and editing your mappings.