HTTPS:(API) to Netsuite Flow

[brettlyder]

I have the following as output from an HTTPS API. I'm trying to use it to import items into Netsuite. I have an Input Hook that removes the "first record" which is the column headers. This allows me to get 1 item in but it only does 1 and there isn't an error. Looking for ideas on what I need to change to have all the exported records to be processed.

Here's the export object, I'm getting from the API.

{
"page_of_records": [
{
"record": {
"masterVariables": {},
"data": [
[
"Product ID",
"Description",
"Std packing",
"Duration",
"Caliber",
"Status",
"Category",
"Hazardous\nmaterial",
"Average cost",
"Product Url"
],
[
"518245",
"3\" Finale to be Tied for Show ",
1,
null,
"3\"",
"Active",
"3 Inch Finale",
null,
null,
"/zambellifireworks/api/product/518245"
],
[
"NEO3TESTPRODUCT",
"This is my description",
null,
null,
null,
"Active",
"4 Inch",
null,
null,
"/zambellifireworks/api/product/NEO3TESTPRODUCT"
],
[
"PatDummy1",
"Test Test Test",
72,
3,
"3\"",
"Active",
"3 Inch",
"UN0335",
null,
"/zambellifireworks/api/product/PatDummy1"
]
]
}
}
]
}

[amanjotsingh]

Hello Brett,

Thank you for reaching out. Could you share what application are you getting data from using HTTPs API?

About the data structure, we are working on providing better parsing capabilities for a 2D array. As a workaround, could you convert your record structure to look like the below in the same hook you are removing the first record? Let us know if this solves the problem!

"data": [
{
"Product ID",
"Description",
"Std packing",
"Duration",
"Caliber",
"Status",
"Category",
"Hazardous\nmaterial",
"Average cost",
"Product Url"
},
{
"518245",
"3\" Finale to be Tied for Show ",
1,
null,
"3\"",
"Active",
"3 Inch Finale",
null,
null,
"/zambellifireworks/api/product/518245"
},
{
"NEO3TESTPRODUCT",
"This is my description",
null,
null,
null,
"Active",
"4 Inch",
null,
null,
"/zambellifireworks/api/product/NEO3TESTPRODUCT"
},
{
"PatDummy1",
"Test Test Test",
72,
3,
"3\"",
"Active",
"3 Inch",
"UN0335",
null,
"/zambellifireworks/api/product/PatDummy1"
}
]

[scotthenderson]

I think Aman meant to say that you should use a preSavePage hook in your source export bubble to covert the data into this much more usable format for your flow.

"data": [
{
"Product ID": "518245",
"Description": "3\" Finale to be Tied for Show ",
"Std packing":1,
"Duration":null,

...

},

{
"Product ID": "518245",
"Description": "3\" Finale to be Tied for Show ",
"Std packing":1,
"Duration":null,

...

}

]

Sorry for any typos, and hopefully the structure above makes sense. We can provide sample hook code if you need it. Let us know.

[brettlyder]

Thanks Scott, I used a PresavePage Hook to with an object.assign to remove the header line and put generic labels on the data. Does this Output look usable for the Netsuite Import?

{
"data": [
null,
{
"0": "1153008141085",
"1": "30mm Vshape - 8 x 14 Silver Comets",
"2": 1,
"3": 14,
"4": "30mm",
"5": "Active",
"6": "1.4g Comet",
"7": "UN0431",
"8": null,
"9": "/zambellifireworks/api/product/1153008141085"
},
{
"0": "518245",
"1": "3\" Finale to be Tied for Show ",
"2": 1,
"3": null,
"4": "3\"",
"5": "Active",
"6": "3 Inch Finale",
"7": null,
"8": null,
"9": "/zambellifireworks/api/product/518245"
},
{
"0": "NEO3TESTPRODUCT",
"1": "This is my description",
"2": null,
"3": null,
"4": null,
"5": "Active",
"6": "4 Inch",
"7": null,
"8": null,
"9": "/zambellifireworks/api/product/NEO3TESTPRODUCT"
},
{
"0": "PatDummy1",
"1": "Test Test Test",
"2": 72,
"3": 3,
"4": "3\"",
"5": "Active",
"6": "3 Inch",
"7": "UN0335",
"8": null,
"9": "/zambellifireworks/api/product/PatDummy1"
}
],
"abort": false,
"newErrorsAndRetryData": []
}

[scotthenderson]

I would remove the null object from the beginning of the array.

Other than that, the output format looks good assuming you are okay with referencing the fields via their numeric indexes. If you wanted to have more human readable field labels in the objects, then you could use the first object in the array that actually has the column header labels to establish the field labels across all objects, etc... Hopefully this makes sense.

[brettlyder]

Thanks for sticking with me @scotthenderson, I don't mind the generic field labels. But I do wonder about the comment about being okay with referencing the fields via their numeric value. If I explicit reference data[0] or data[1], I am getting the data. But If I use data[*] I only get 1 record, guess I was assuming it would iterate through all of the results but it isn't, I'm still only getting one record to import into Netsuite.

[scotthenderson]

I would need to see your entire flow to understand what is not configured correctly to iterate over all the records, but I highly recommend opening a support ticket instead and someone in support should be able to unblock you pretty quickly. If for some reason support cannot help, then please share lots of screen shots here on the community so we can see what is going on.

[brettlyder]

Thanks again @scotthenderson, I have an open support ticket and am scheduled to meet with a support rep later today. We'll see how it goes, I'll give you much more info about my flow if I can't get anywhere. Thanks!

[brettlyder]

@scotthenderson well, support stood me up (Must've got tied up). I have an API Get giving me recently changed or created items in the format that I showed in my original post. I'm using a preSavePage hook to make the export data look like this:

Then on the Netsuite import I'm using a PreMap hook to basically remove the field heading record. It looks like this:

If I replace the * with a specific number I can get the flow to import that specific record but I was hoping it would import all of them. I'm assuming if I do this right, then the output will show more than 1 record.

What else can I show you, there isn't too much more to the entire flow?

[tylerlamparter]

@brettlyder If you want to fix what you already have, you would need to specify a one-to-many path on the NetSuite import step and specify the path as data. Then we would iterate of the data array to create/update individual items within the array. Your field names to map would then just be the number index.

If you want to make it a bit easier on yourself, you could use this script on the presave section of your export step. This script basically takes the field names and creates new data objects to more easily map later down the road. You also wouldn't need to specify a one-to-many path on the import step because this script is creating individual records within the page of data.

/*
* preSavePageFunction stub:
*
* The name of the function can be changed to anything you like.
*
* The function will be passed one 'options' argument that has the following fields:
*   'data' - an array of records representing one page of data. A record can be an object {} or array [] depending on the data source.
*   'files' - file exports only. files[i] contains source file metadata for data[i]. i.e. files[i].fileMeta.fileName.
*   'errors' - an array of errors where each error has the structure {code: '', message: '', source: '', retryDataKey: ''}.
*   'retryData' - a dictionary object containing the retry data for all errors: {retryDataKey: { data: , stage: '', traceKey: ''}}.
*   '_exportId' - the _exportId currently running.
*   '_connectionId' - the _connectionId currently running.
*   '_flowId' - the _flowId currently running.
*   '_integrationId' - the _integrationId currently running.
*   'pageIndex' - 0 based. context is the batch export currently running.
*   'lastExportDateTime' - delta exports only.
*   'currentExportDateTime' - delta exports only.
*   'settings' - all custom settings in scope for the export currently running.
*
* The function needs to return an object that has the following fields:
*   'data' - your modified data.
*   'errors' - your modified errors.
*   'abort' - instruct the batch export currently running to stop generating new pages of data.
*   'newErrorsAndRetryData' - return brand new errors linked to retry data: [{retryData: , errors: []}].
* Throwing an exception will signal a fatal error and stop the flow.
*/
function preSavePage (options) {
  
  let nData = [];
  for (let p of options.data) {
    let fieldNameArray = p.data[0];
    for (let [i,d] of p.data.entries()) {
      let r = {};
      if (i != 0) {
        for (let [j,v] of d.entries()) {
          r[fieldNameArray[j]] = v;
        }
        nData.push(r);
      }
    }
  }
  
  return {
    data: nData,
    errors: options.errors,
    abort: false,
    newErrorsAndRetryData: []
  }
}

[brettlyder]

@tylerlamparter thank you , I'll give that a try!! I guess, what I don't understand is why I can't get it to work when I get the data in basically the same format except I just have the generic field names(0,1 2...) Instead of the more human readable names? Is it because I'm doing it in a hook at the import level?

[tylerlamparter]

@brettlyder it's because you would need to specify a one-to-many path on the NetSuite import step. If you set that to data, then you will see how the data structure changes and how you can map to 0, 1, 2, etc.

[tylerlamparter]

@brettlyder you could also fix where you have your script. I see you have a "pre save script" attached where the "transform script" would go. See how at the top of your script you see "transform" stub insert? You'll notice it has different requirements compared to the presave page script. The transform script is fed a single record whereas a presave page script is fed an array of records within a page.

[brettlyder]

Thanks @tylerlamparter!! The script you sent along with the Pre Save Page tip worked like a charm!