NetSuite update based on JSON payload rather than lookup criteria

[mikebranch]

I'd like to perform an update in NetSuite where the records to be updated are determined by an array in the JSON payload rather than the normal lookup filter in an import step. I'll try to explain our problem without going into all the irrelevant details...

We have a NetSuite export that selects Item Fulfillment records.

An additional lookup pulls in a list of serial numbers from a custom record associated with the Sales Order. (Unfortunately there's no relationship between the serial numbers and the fulfillment record.)

We then export the fulfillment and serial numbers to a trading partner's S3. All of this works fine, no problem.

NOW... we need to update the custom serial number records in NetSuite so we know they've been sent to S3. Our data model in NetSuite doesn't give us any way to specify filter criteria for doing this update in an import step. However, the JSON in the flow does include an array of serial number records. I would love to be able to update the serial number records that are listed in the JSON array. This array includes the internal IDs of those custom serial number records. Any way to do this?

I hope this all makes sense. Here's a sample of the JSON in the flow (I've obscured some identifying information). Specially I'd like to update the records referenced by the "serialnumber" array.

{

"postResponseMapData": [

{

"internalid": "50751782",

"fulfillmentnbr": "IF1041048",

"shipdate": "11/29/2023",

"shipcompany": "FedEx",

"shipmethod": "SC - Best Way",

"salesordernbr": "Sales Order #SO1656109",

"salesorderiid": "50748372",

"custordernbr": "POUSZ101217",

"item": [

{

"itemiid": "5133",

"item": "nnnnnn",

"quantity": "1"

}

],

"serialnumber": [

[

{

"id": "4343635",

"recordType": "customrecord_cps_3pl_item_fulfillment",

"Internal ID": "123",

"serialnbr": "2622220030681",

"salesordernbr": "Sales Order #SO1647867",

"dataURI": "xxxxxxxx"

},

{

"id": "4343635",

"recordType": "customrecord_cps_3pl_item_fulfillment",

"Internal ID": "456",

"serialnbr": "2622220030680",

"salesordernbr": "Sales Order #SO1647867",

"dataURI": "xxxxxxxx"

}

]

]

}

],

"_exportId": "654d633c715bc02e45dcdd9a",

"_connectionId": "6241fa67c7f49968933a00e5",

"_flowId": "654d58b4dd6da523102a621f",

"_integrationId": "651d9b31aa8a7b132bc7ef99",

"settings": {

"integration": {},

"flowGrouping": {},

"flow": {},

"export": {},

"connection": {}

},

"testMode": false

}

[davidgollom]

Hey Mike,

Sounds like a NetSutie import using the One to Many option should work. Or, on your lookup step, you can use the Once Type option and you'll therefore mark those serials as having been processed. Would those work?

[tylerlamparter]

@mikebranch one-to-many like David suggested may work, but it looks like you may need to adjust your response mapping for the lookup to NetSuite to get serial numbers because one-to-many only support a single deep array. Right now, your serial numbers is an array of arrays which is messy and I assume it could just be one array with several serial objects. In your lookup, are you grouping results and then have a response mapping of "data" to "serialnumber"? If so, change the response mapping to "data.0" to serialnumber" OR don't group by results because you probably don't need to here. After that, you should have an array of objects and then you can specify a one-to-many path on your NetSuite import to serial custom record.

[mikebranch]

Thanks @davidgollom259 and @tylerlamparter. It seems like that should solve my problem. It's no problem pushing the serial number array to the top level. However I'm having a little trouble configuring the lookup correctly and can't find any clear documentation on this. Assuming I have an array as shown below, how would I specify the "path to many" and a filter to match on the "internalid"? I've tried a few things but the debug info doesn't show any lookup attempts at all, which leads me to believe Celigo doesn't see this info in the JSON.

"serialnumbers": [
{
"serialnbr": "44232900048",
"internalid": "5396499"
}
]

[tylerlamparter]

@mikebranch here is a screenshot and an article that can help.

https://docs.celigo.com/hc/en-us/articles/12581994146843-Create-a-one-to-many-import-including-nested-arrays

[mikebranch]

Success - thanks!