This article describes different patterns to implement a one-way data sync of relational objects, for example, Companies and Contacts, where contacts (child objects) are linked to a Company (parent objects).
The challenge of data sync with relational is that the parent object must exist in the destination before the child can be created in the destination.
On top of that, the child object must be linked to the parent object in the destination. This link is in many cases done via a Foreign Key, meaning the id of the parent in the destination. This can be a challenge since this id is different from the id in the source.
- Source Webshop: Contact A with id 123 is linked to Company B with id 567
- Destination CRM: Contact A with id 777 must link to Company B with id 888
In the following example, we sync Contacts from a Demo Webshop to a Demo CRM. We assume that the parent objects (companies) already exist in the destination Demo CRM. We search for the existing company by VAT number, in order to have its id that we use as Foreign Key.
When we upsert (create or update) the contact (child object), we use the id (Foreign Key) of the company from the destination:
In the following example pattern, we upsert the parent object first, and then use the response from this upsert to have the id of the parent (used as Foreign Key when upserting the child):
Sometimes it's impossible to quickly find the parent object in the destination, for example, because the "Get company by VAT number" is not available. In that case, we can retrieve a full list of parent objects from the destination, and do lookups to find the Foreign Key. This pattern will only work if the number of parent objects is limited (e.g. thousands of records but not millions of records).
The following pattern can cause racing conditions. The parent objects are synced first, and then the children are synced. Let's assume the first part (syncing the parent objects) takes 10 minutes, and during this loop, a new parent object is created in the source. This new parent will not yet be part of the loop and will not be created in the destination. When the children are synced, the new parent may be missing in the destination, causing the creation of the child to fail.
Example pattern that may cause racing conditions:
The solution is to handle the racing condition (catch the error), and create a parent object when missing:
Use the error formula to read the error from the block "Add or update contact":
Updated 5 months ago