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)

Set the last argument to False to prevent an error being generated when no match is found.

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.

Example

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

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.