JSON Document Creation Error

Integrator.io
, , , ,
1

I am trying to create a JSON file to be sent to the NetSuite File Cabinet for further processing. the data being converted comes from a custom connection, and I am able to get the results that I want when I do a preview:

Here is the data being received:
{
 "record": {
   "id": "1661302356107665744",
   "commandId": "95cc747705554e0f9597267308d56d38",
   "records": [
     {
       "record_0": {
         "internal_study_name": "Cobalt Captain",
         "leadname": "Chris Cortinhas",
         "id_lead": "c32cce83-6208-4716-10bf-08da2133bf5b",
         "date_admin": "2023-08-28",
         "date_reviewed": "2023-08-28",
         "subject": "100-999",
         "visit": "Visit 4",
         "scale": "PACC5",
         "review_type": "FormAndAudio",
         "visit_form_id": "2b19a329-e7a3-4c98-8bf1-c70c8fe97948"
       }
    ]
   }
}
Here is the template:
{
 {{#each record.records}}
   {{#each this}}
     "{{@key}}": {{{jsonSerialize this}}}
       {{#unless @last}},{{/unless}}
   {{/each}}
 {{/each}}
}

When running, I am encountering the following error even if the preview is right:

{{# each batch_of_records}} not found in the template.

I tried following this article, but it still shows the error. I badly need help on this as the project will be delayed if this isn't working, since there are a lot of this to do after the file has been processed.

2

Hi @bobrementizo,

I'm moving your post to the Troubleshoot custom flows section for more visibility. I'm also reaching out internally to see if we can find a solution for you quickly.

Thanks,

Kathyana

3

@bobrementizo for NetSuite file imports this way, there is a currently logged bug where the UI is not showing the correct input. When it actually runs, it would have this:

{
  "batch_of_records": [
    {
      "record": {
        "id": "1661302356107665744",
        "commandId": "95cc747705554e0f9597267308d56d38",
        "records": [
          {
            "record_0": {
              "internal_study_name": "Cobalt Captain",
              "leadname": "Chris Cortinhas",
              "id_lead": "c32cce83-6208-4716-10bf-08da2133bf5b",
              "date_admin": "2023-08-28",
              "date_reviewed": "2023-08-28",
              "subject": "100-999",
              "visit": "Visit 4",
              "scale": "PACC5",
              "review_type": "FormAndAudio",
              "visit_form_id": "2b19a329-e7a3-4c98-8bf1-c70c8fe97948"
            }
          }
        ]
      }
    }
  ]
}

Now, in your case, you probably enabled skip aggregation on the import and set your page size to 1 on your export so that you would only ever get one record put into this JSON file and it wouldn't be wrapped in brackets. However, do you expect record.records length to always be 1? If not, the way you're going about this won't work. If so, then you would have this:

{
{{#each batch_of_records}}
 {{#each this.record.records.0.record_0}}
   "{{@key}}": {{{jsonSerialize this}}}{{^if @last}},{{/if}}
 {{/each}}
{{/each}}
}
4

Thanks @tylerlamparter for the detailed explanation. To answer your question, record.records length will have more than 1 as I am pulling data from an SQL query, and worked my way into getting each row to be a JSON object, which is stored on the “records” object. They have the index count attached to the key.

Given that, will the suggested solution still work for multiple results?

5

@bobrementizo and you have to have this JSON structure where the records aren't wrapped in brackets? What processes the file in NetSuite after it's placed there?

6

@tylerlamparter I have updated the JSON structure of the output to be enclosed in square brackets, but it's still not passing through, and throwing the same error. Here's the sample, more than 1 record to be precise:

[
  {"internal_study_name":"Cobalt Captain","leadname":"Chris Cortinhas","id_lead":"c32cce83-6208-4716-10bf-08da2133bf5b","date_admin":"2023-08-28","date_reviewed":"2023-08-28","subject":"100-999","visit":"Visit 4","scale":"PACC5","review_type":"FormAndAudio","visit_form_id":"2b19a329-e7a3-4c98-8bf1-c70c8fe97948"},
  {"internal_study_name":"Larry Lobster","leadname":"Isabella Hauptman","id_lead":"05a6f4f6-02fe-43c8-10bd-08da2133bf5b","date_admin":"2022-04-10","date_reviewed":"2022-09-09","subject":"100-2200","visit":"Visit 18","scale":"CDR Review","review_type":"FormAndAudio","visit_form_id":"1cc05eb6-0a16-4908-8f0b-adc4d3e04bd8"},
  {"internal_study_name":"Cobalt Captain","leadname":"Chris Cortinhas","id_lead":"c32cce83-6208-4716-10bf-08da2133bf5b","date_admin":"2023-09-27","date_reviewed":"2023-09-27","subject":"Test Site-999","visit":"Visit 1","scale":"PFCSRT","review_type":"FormAndAudio","visit_form_id":"f90e8291-2be0-4175-9bfc-a79b2b202b6b"},
]

The output above is the result of the handlebar expression below as JSON document:

[
 {{#each record.records}}
   {{#each this}}
     {{{jsonSerialize this}}}{{#unless @last}},{{/unless}}
   {{/each}}
 {{/each}}
]

And here is the file coming in to the action:

{
    "record": {
        "id": "1661302356107665744",
        "commandId": "95cc747705554e0f9597267308d56d38",
        "records": [
            [
                {
                "internal_study_name": "Cobalt Captain",
                "leadname": "Chris Cortinhas",
                "id_lead": "c32cce83-6208-4716-10bf-08da2133bf5b",
                "date_admin": "2023-08-28",
                "date_reviewed": "2023-08-28",
                "subject": "100-999",
                "visit": "Visit 4",
                "scale": "PACC5",
                "review_type": "FormAndAudio",
                "visit_form_id": "2b19a329-e7a3-4c98-8bf1-c70c8fe97948"
                },
                {
                "internal_study_name": "Larry Lobster",
                "leadname": "Isabella Hauptman",
                "id_lead": "05a6f4f6-02fe-43c8-10bd-08da2133bf5b",
                "date_admin": "2022-04-10",
                "date_reviewed": "2022-09-09",
                "subject": "100-2200",
                "visit": "Visit 18",
                "scale": "CDR Review",
                "review_type": "FormAndAudio",
                "visit_form_id": "1cc05eb6-0a16-4908-8f0b-adc4d3e04bd8"
                },
                {
                "internal_study_name": "Cobalt Captain",
                "leadname": "Chris Cortinhas",
                "id_lead": "c32cce83-6208-4716-10bf-08da2133bf5b",
                "date_admin": "2023-09-27",
                "date_reviewed": "2023-09-27",
                "subject": "Test Site-999",
                "visit": "Visit 1",
                "scale": "PFCSRT",
                "review_type": "FormAndAudio",
                "visit_form_id": "f90e8291-2be0-4175-9bfc-a79b2b202b6b"
                }
            ]
        ]
    },
    "job": {
      "_id": "6788bd0bc01e987923878ab9",
      "type": "import",
      "startedAt": "2025-01-16T08:02:19.862Z",
      "parentJob": {
        "_id": "6788bd0bc01e987164878aba",
        "type": "flow",
        "startedAt": "2025-01-16T08:02:14.862Z",
        "flowExecutionGroupId": "6788bd0bc01e98ccfc878abb"
      }
    },
    "testMode": true,
    "settings": {
      "integration": {},
      "flow": {},
      "flowGrouping": {},
      "connection": {},
      "iClient": {},
      "import": {}
    }
}