Design Patterns

This section describes some common patterns when creating integrations involving SageCRM.

Record De-Duping/Duplicate Check

To filter or prevent duplicate records being inserted into CRM use any of the SageCRM lookup functions to check for the existence of the record. E.g.:

SageCRMLookup(“CRM”, “Company”, “comp_companyid”, “comp_name”, %companyname, false)

Use the lookup formula in one of two places prior to importing into SageCRM:

Map Transform

• Use the lookup formula in the id field for the record; map this field against the id field of the entity in the SageCRM connector.
• When the lookup does not return a match the field will be empty and a new record inserted; when a match is found the id field is populated with the record’s id and the record will be updated.
  

The following screenshot shows the Map transform:

The Connector field mapping, mapping the Opportunity ID field:

Filter Transform

• To prevent duplicates or existing records being updated use the lookup formula in a filter transform to remove existing records from the dataset.

Record Synchronisation

To synchronise records between two systems it is suggested the following strategy is employed:

1. Two Integration Jobs
   • One for extracting data from SageCRM, updating the external system and another for the reverse scenario to update SageCRM from the external system.
2. Record Foreign Id or Entity Id
   • Record the foreign record’s ID against the record within CRM, use this field for the basis of the lookup to determine the actual ID.
   • Alternatively record the SageCRM record ID in the external system and supply the field in the extract from the external system.
   • For two way integration record the foreign record id in each application.
3. Use UpdatedDate Field
   • When importing use the UpdatedDate field to determine if a record requires updating.
   • When extracting use the UpdatedDate field in a filter to extract only the updated records.