#804 OIC REST API for Lookups

 

Above, my lookup.

Here are the Lookup REST APIs -

Retrieve is easy -

Per default, retrieve does not show me the row values e.g. USD, GBP etc.

I just need to add the following query parameter, in order to retrieve the contents -

Update - let's add a couple of new entries

EURO / 3 and CAD / 4

So what does my request look like?

Now let's check the Lookup in OIC - 

As you can see, the original entries have been overwritten.

In this case, update means essentially replace..

So how can I have a scenario where I just want to augment the lookup list?

Simple enough with OIC - I create a REST connection in OIC -

 

I will essentially do what I did in Postman - with one exception -

Here is my completed integration -

The REST Trigger Request payload is as follows -

GetLookupData Invoke is configured as follows -

The Map to GetLookupData  is as follows -

I set expand to "datarow"

UpdateLookupData Invoke is configured as follows -

Map to UpdateLookupData is configured as follows -

Here I need to merge the original entries from the initial GET with the new entries in the REST Request payload.

I begin by repeating the rows below - 

I map the result of the GET to 1 of 2 rows -

 

I map the new values from the initial request to 2 of 2 rows -

Time to test - here are the current contents of the Lookup - 

 

Here is my test payload -

Here is the result -

#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!