Thursday, October 8, 2020

#803 OIC 4 Netsuite - Polling for New / Updated Customers





Introduction

A simple use case here - 

poll for new/updated Netsuite customers and update a custom on-premise CRM app.

Here I will write directly to the custom app DB, CUSTOMER table.

The DB table is basic - 





First step is to work out the fields to filter.

I created a new customer in Netsuite and looked at the xml representation.

I found the following -


 


But net, net - when I create a new Customer in Netsuite both of the above fields are set to the same dateTime value.

Ergo, lastmodifieddate will be the only value on which I need to filter.

Here is an example - 







I look at the xml representation - 









So here's my business logic -

Have a scheduled job in OIC - with a schedule date parameter - dateLastRun

Do a search in Netsuite for customers where the lastmodifieddate is after dateLastRun.

For each of these customers, do an upsert into the Oracle DB CUSTOMERS table.

Update the dateLastRun parameter with the current dateTime.


The Anatomy of the Integration












Now, step by step - 

Schedule parameter definition -









Get Current Timestamp invokes the following JS function -












ts is set to the schedule -> start time -




z is set as follows - 




Here is the Lookup - 







Here is the Search - 














I can set the number of records to be fetched here -


























The only field I need to map is lastmodifieddate -











operator is set to "after"

I then Log the number of new/Updated customers found -








I do an UPSERT in the loop -

















and that's it.

Now to Testing this - 

DB table is empty -






I start the schedule off from Oct 1st.









I get 5 entries in the DB -






Now I go and edit Dublin Philosophy and change the company size














I re-run the integration - 


great stuff agus araile!