Retrieve Custom Fields from NetSuite using Oracle Integration Cloud NetSuite Adapter !!!

This blog is showcasing very specific use case related to NetSuite Custom Field retrieval and how we accomplish that using Oracle Integration Cloud NetSuite Adapter.

In this example we will be retrieving custom fields values e.g. birthDt from NetSuite by passing specific Customer Id. It may sound very easy but it was bit complex to deal with because the way how NetSuite was responding Custom Fields values using NetSuite Adapter.

Here is the use-case diagram -

In my previous blog I have already shown all the configuration which needs to be done to connect to NetSuite using Oracle Integration Cloud NetSuite Adapter and deal with NetSuite Custom fields. Hence, this blog will just focus on specific mapping challenges which I have faced initially during implementation.

I am assuming  the reader already have hand-on understanding of Oracle Integration Cloud about how create connection, Integration, mapping, activation and test etc. In this blog I will talk very specific XPATH statement which we modified to solve that problem.

In order to retrieve Custom Fields values for NetSuite, user has to configure "Get" operation and select "Customer" object as we are going to retrieve Custom Field associated with Customer object from NetSuite.

Once done, above step, next critical steps to do mapping and that's where challenge was existing.

The Oracle NetSuite Adapter does not currently discover and show the custom field directly for you to select. Therefore, you must specify "InternalId" for each custom field before mapping or retrieving its value. In my previous blog, I have highlighted how to get ScriptId and InternalID for a particular Custom Field. 

NetSuite response message shows all custom fields under "CustomFieldList" parent element, which has different child datatype elements e.g. DateCustomFieldRef, DoubleCustomFieldRef, StringCustomFieldRef and BooleanCustomFieldRef etc.

Each sub category element e.g. DateCustomFieldRef  contains concatenated  values from all the Custom Fields of that kind of data type. E.g. if a Customer Object has 2 date type Custom fields then if we map DateCustomFieldRef to right hand side any element then runtime it will show concatenated values for all date type Custom Fields.

To explain further e.g. Customer "John"  as 2 Date type Custom Fields name respectively  "birthDt" and "marriageDt" which are holding two different date values for two different custom field but the response message from NetSuite will only show one datatype which is DateCustomFieldRef which has concatenated values of "birthDt" and "marriageDt" Custom Fields.

If user do simple mapping between source to target as usually we do in OIC.



The runtime outcome of above mapping would be something like this.

Wherein "value" element will have concatenated values of 2 different dates "birthDt" and "marraigeDt". In in our example we were trying to retrieve only "birthDt" date Custom Field value, that's where we were stuck.

Note: To simplified this blog, I have only talked about 2 date type custom fields. However, for the Customer where we have faced this issue they were having 70+ custom fields, so no other logic e.g. substring, trimming and sequencing etc was working, at the same time Customer informed that some Custom fields can have null values has well, so any logic related to positioning of Custom field in NetSuite response can't work.

So, how to retrieve only one "birthDt" date type Custom Field value.

In order to fix this problem, we need to do same mapping as highlighted in above steps. The only additional steps what we need to do is to modify auto generated XPATH statement and include "InternalId" of "birthDt" CustomField.

How to do that-

Just click on mapped element e.g. click on "value" element after mapping-



Above step will take you the XPATH statement which got generated automatically.



Just copy and paste that XPATH statement in notepad-

Now, add "InternalId" of "birthDt" date custom field which, e.g. InternalId for birthDt=369

[@internalId=369]/nsmpr1:value">

Once modified that XPATH statement, come back to mapper, select the same value element, click edit and select "Input Literal"



and paste newly modified XPATH statement and save the mapping.

Activate the integration and now the value element will be having only one date which is the value of birthDt custom field from NetSuite.

I hope, this blog will help you to understand how easily and simply we can retrieve Custom Fields values from NetSuite using Oracle Integration Cloud NetSuite Adapter.

Stay tuned for more useful blog!!!

Happy Blogging 🙂