When you are building a Scribe Online Integration Services advanced map that requires logic to handle updates and inserts, you have a few different design choices to choose from to handle the requirement. In this post I want to explore the choices with you and discuss their pros and cons. In particular, I want to point out the impact that certain block types can have on performance and error handling.
There is an option for an all-in-one Update/Insert block, there are separate blocks (for Lookups, Updates, and Creates) you can use to create your own logic, and some connections will support Upsert. So, which block or blocks should you choose?
This block is an all-in-one Lookup + Update or Insert logic. It will first perform a Lookup operation, and depending upon the results, perform the second operation of Inserting or Updating a record.
- You will only need one block to configure for lookup matching criteria.
- You can create one set of field mappings that will work for either of the update or insert operations.
- You can only run one lookup and have no option to run additional lookups if the first does not return a value (i.e. you cannot lookup a contact, then lookup a lead).
- This block is not ideal to map different fields for an Update versus Create because you would need to write IF() formulas on each field.
- 1:N Updates are not supported.
I like to use this block when I know I need to reprocess child-related records, such as activities related to an account or order line items related to an order. When I have simple matching logic, this block gives me the peace of mind that all of my Order Line Items have the correct quantities and sales price.
- You can define error handling for each operation.
- You can configure the field mappings for the Update block and the Create block separately. This is nice if you want your source to provide address information when the Create block is performed, but you do not want to overwrite the address in the target with data from the source when the Update block is performed.
- You can add additional block within the IF or ELSE block:
- Lookups (If your source provides an email address and you are going to search for a Contact, and that Contact isn’t found, you can perform an additional Lookup on the Lead entity.)
- Creates (Let’s say your If/Else allows you to update and create Leads respectively. You can add an additional Create operation on the branch for when a new Lead is created to have a new Task related to that Lead.)
- Updates (You can use a source update operation when a record is created in your target for key cross referencing).
- You will spend extra time configuring the Update and Create blocks if they have similar mappings.
I like to set my Lookup to error on multiple records found, and return the target entity’s primary key, then use that in my Update’s match criteria. In the If/Else control block, I like to use the Lookup Operation’s Result.RecordsMatched = 1 as my condition.
When you can specifically design your own path like this, you will find that future changes to your logic are easier to configure! It’s also easy to implement key cross referencing later and preserve your Create and Update field mappings. This is typically done by removing the Lookup operation altogether and changing the If/Else criteria to make a decision based on a source field foreign key.
This is a single operation sent to the target, allowing the target (not Scribe) to choose whether to update an existing record or create a new one. The Upsert block is only available for connections where the underlying API being used supports that type of API call. For example the Salesforce and Marketo APIs support upsert so the Upsert block type is available for those connectors.
- As one operation, this block will perform faster than the alternatives (because there’s no Scribe Lookup), and it typically supports batch processing too.
- Upsert is a very efficient use of API hits, especially when using batch processing. It’s a must-have for connections (such as Salesforce) that count API hits used by integrations.
- The matching criteria the target application uses is either defined by the API [and unchangeable] or maintained in the application (i.e. a Salesforce Admin must define an External Id) – sometimes there’s not a lot of room to adjust how you’re matching.
Any time Upsert is available, you’ll typically want to use it; it’s a combination of great performance, efficiency, and easy configuration.
I prefer to use (1st) Upsert when available, (2nd) build my own Update + Insert logic, and (3rd) use the Update/Insert block for edge cases.