Bill Conway, NetSuite Practice Lead
Do you struggle to efficiently moving data from system to system in a timely manner?
Performing an incremental sync is one of the most efficient ways to periodically move only the necessary data between different systems. Dell Boomi is one of many solutions that you can use to perform an incremental sync to move data in and out of two disparate systems.
This example will walk through how to perform an incremental sync using the Base Edition of Boomi from a NetSuite environment and into a SQL database.
Full Incremental Sync Boomi Process:
1. Retrieve data from source NetSuite environment
- Set up a connection to the NetSuite environment and an operation to query the data
- Required: Pull last modified by date/time field for records
- Set a filter on the query with the Last Modified value in the operation
- Set the parameters on the connector shape with the Dynamic Process Property that will hold the last modified date of the most recent record synced
- If this is the first process execution, the Dynamic Process Property will be blank and will affect the records pulled. Best practice for first executions is to hardcode the value or remove the filter. Subsequent runs will update the property value and persist it across executions
2. Set a dynamic document property with the modified date for sorting
- This sets a property for each document that comes through that will be accessed in the Groovy script in the next step
3. Use a Data Process Shape to sort the incoming documents in ascending order using a groovy script
- This could also be done when querying data from the connector such as using a saved search in NetSuite
- Here is some additional information on how to create a script
4. Use a function inside the map shape to set Process Property with the lastModified date from the record
- A custom function in the map is written to get the date format from the record and set it to the Dynamic Process property.
- Below is a custom function named “Map Date to Property”
- If the date format isn’t explicitly set, Boomi will use its own internal format and time zone. A custom function can be used to control the change Date Format step should be used in the custom function the make sure that the date is transferred in the correct format and keeps the correct time zone offset
5. Perform an SQL lookup with a decision shape to check if a record is present in the destination database or not
- The first value will be a static value of 1. In this case, 1 indicates that the record exists in the destination and the true path will be followed to update the record.
- The second value should be a select statement to grab the count of the unique field. If the record is present it will return a 1, if not present, then a 0
6. If the record is present perform an update, if not, perform an insert
- Some connections will also support an upsert/merge allowing step 5 and 6 to be done in one single connector shape.
Process Limitations and Optimizations
You can configure a business rules shape to determine the most recent modified date from the incoming documents, only setting the process property when a document contains a more recent modified date.
The business rules shape is part of Boomi’s Advanced Workflow and only available for Professional subscriptions and up. Using a business rules shape also eliminates any need for sorting in the connector call or during the process as it can be configured to get the highest value.
Boomi stores a last run date and last successful run date by default for each process. You can use this in place of the modified date from the data source but is not recommended. The best practice is to explicitly capture the date/times from the data source to ensure that nothing gets missed due to server time differences.
For more information about performing an incremental Update/Insert from NetSuite using Dell Boomi refer to Boomi’s online documentation or contact Blue Horseshoe.