Worked Example - Create, Read, Update & Delete
The Workbooks Connector for Scribe can be used for migration and integrations to/from Workbooks CRM. As described here, the connector has full support for Create, Read, Update and Delete actions and uses the Workbooks API under the covers to offer a user-friendly experience when creating maps in Scribe.
The following worked example, explains how to build a map to take a CSV of People from an SFTP Server (see here for instructions how to set up a source connection to an SFTP Server before trying to build out this example):
- Look for an existing Person in Workbooks using the "Id" column as the query on the Workbooks External Reference field to find a match
- If found, the record is updated. Otherwise, a new record is created. In both examples, only the basic fields such as Name, Email Address and Assigned To Fields are populated
- The map will then delete the record it finds as a final step to show a full example
- The example also provides an Update/Insert alternative approach instead of using separate Create & Update blocks
The first step to building a good map is to understand the data that you have in the source system and what transformations are required to copy the data from the source (in this case, a CSV file on an SFTP server) to a target system i.e. Workbooks. Carrying out this analysis first will also help you to understand the logic that is required to build a map that is robust, and one that deals with duplicate records as much as possible.
In this example, we simply need to copy data from a CSV of contacts to Workbooks People. The map needs to ensure that it doesn't create duplicate People records, and therefore we need to create a map that searches in Workbooks for an existing record, and then either creates a new record if not found or updates an existing record. Looking at the example CSV below, either the Id or Email Address should be unique to the row in the CSV so these can be used in the Query/Lookup block to Workbooks. For this example, we will map the Id to the External Reference column, as the Email can change and therefore may create a duplicate record if the Email Address is ever updated.
Start building your map, by dragging on the necessary blocks to the Scribe canvas in the following order:
The map requires these steps in order of appearance:
- Add a Query block. This should be added from connection to the CSV file which is hosted on an SFTP Server - see here for how to configure an SFTP connection in Scribe.
- Next, lookup People. Add this from the Workbooks connection setting the Entity to People
- Add a Scribe If/Else Control
- Within the "IF", add a Update Block from the Workbooks connection setting the Entity to People
- Within the "ELSE", add an Create Block from the Workbooks connection setting the Entity to People
- Outside of the IF/Else Control block, add a Delete from the Workbooks connection, also setting the Entity to People
The next few steps will revisit each block and configure the settings accordingly.
The Lookup People block added in Step 1 allows us to query a Workbooks database to return any matching records. The Lookup should only return one Person record by matching on a unique field. If a record is found then the map will go on to update the existing Person record, otherwise it will ceate a new Person.
Double click on the Lookup Block, and go to the Lookup Critieria tab. Click the "+" on the right hand side, then select External Reference in the Field column.
In the Value column, configure this to use the "Id" from our Query block by clicking the "fX" button to open the Formula Editor screen:
Next, on the Field List tab, select the Id & Lock Version columns. These fields are needed later if we find a match, to update the record.
In the case of more than one record being found, the map will fail as it does not know which record to update from the multiple records found, similar to the Workbooks Import tool.
- Hover over the Update block added, then click the gear icon to reveal a context menu
- From here, click "Copy".
- Single click anywhere on the Scribe canvas, to reveal the context menu again. Click "Paste"
- From the copied block, hover to reveal the menu again but this time click "Change Block Type"
- In the dialogue box, change the block type to "Create"
Lastly, remove the existing Create block from the map and replace with the new one