Need help with setting a Netsuite Customer Lookup

Integrator.io
, , , ,
1

Good day!

I have an FTP Data Source tile where it's grouped by "customer code" + "PO" and a Mock Output below.

{
  "page_of_records": [
    {
      "rows": [
        {
          "Customer Code": "1111111",
          "DropShip Code": "TLP6123hH'u}",
          "PO": "123457_CA_CSV",
          "Date": "07/09/2024",
          "Location": "1",
          "Ship To Addressee": "Test Customer_RB (Canada)",
          "Ship To Attention": "Jane Doe",
          "Ship To Telephone Number": "204-229-2946",
          "Ship To Address 1": "1397 Caribou Pl",
          "Ship To Address 2": null,
          "Ship To Address 3": null,
          "Ship To City": "Winnipeg",
          "Ship To State": "MB",
          "Ship To ZIP": "R3T1L6",
          "Ship To Country Code": "CA",
          "Notes": "This is a test dropship order",
          "Part Number": "241900-1010-00",
          "Qty": "3"
        },
        {
          "Customer Code": "1111111",
          "DropShip Code": "TLP6123hH'u}",
          "PO": "123457_CA_CSV",
          "Date": "07/09/2024",
          "Location": "1",
          "Ship To Addressee": "Test Customer_RB (Canada)",
          "Ship To Attention": "Jane Doe",
          "Ship To Telephone Number": "204-229-2946",
          "Ship To Address 1": "1397 Caribou Pl",
          "Ship To Address 2": null,
          "Ship To Address 3": null,
          "Ship To City": "Winnipeg",
          "Ship To State": "MB",
          "Ship To ZIP": "R3T1L6",
          "Ship To Country Code": "CA",
          "Notes": "This is a test dropship order",
          "Part Number": "241900-2001-00",
          "Qty": "1"
        }
      ]
    },
    {
      "rows": [
        {
          "Customer Code": "1111111",
          "DropShip Code": "TLP6123hH'u}",
          "PO": "123458_CA_CSV",
          "Date": "07/09/2024",
          "Location": "1",
          "Ship To Addressee": "Test Customer_RB (Canada)",
          "Ship To Attention": "Jan Doe",
          "Ship To Telephone Number": "204-229-2947",
          "Ship To Address 1": "1397 Caribou Pl",
          "Ship To Address 2": null,
          "Ship To Address 3": null,
          "Ship To City": "Winnipeg",
          "Ship To State": "MB",
          "Ship To ZIP": "R3T1L7",
          "Ship To Country Code": "CA",
          "Notes": "This is a test dropship order",
          "Part Number": "241900-1010-00",
          "Qty": "2"
        },
        {
          "Customer Code": "1111111",
          "DropShip Code": "TLP6123hH'u}",
          "PO": "123458_CA_CSV",
          "Date": "07/09/2024",
          "Location": "1",
          "Ship To Addressee": "Test Customer_RB (Canada)",
          "Ship To Attention": "Jan Doe",
          "Ship To Telephone Number": "204-229-2947",
          "Ship To Address 1": "1397 Caribou Pl",
          "Ship To Address 2": null,
          "Ship To Address 3": null,
          "Ship To City": "Winnipeg",
          "Ship To State": "MB",
          "Ship To ZIP": "R3T1L7",
          "Ship To Country Code": "CA",
          "Notes": "This is a test dropship order",
          "Part Number": "241900-2001-00",
          "Qty": "4"
        }
      ]
    }
  ]
}

We want to add a security check that the "Customer Code" and "DropShip Code" being submitted are the same value in our Netsuite customer data. When it's not the same, then we don't want to process the order and error out. So I am trying to create a lookup below and set the following parameters

  • What would you like to export? "Customer"
  • NetSuite Saved search: "Celigo - Drop ShipCustomer Search"
  • Export type: "All - always export all data"

It's preview data is what I get below

{
  "page_of_records": [
    {
      "record": {
        "id": "999",
        "recordType": "customer",
        "Internal ID_Customer": "999",
        "Customer Name": "Test Quebec Customer",
        "DropShip Code": "A}3'86G_px51",
        "Location": "Warehouse 1",
        "Internal ID_Location": "1",
        "dataURI": "https://0000000.app.netsuite.com/app/common/entity/custjob.nl?id=999&compid=1143708"
      }
    },
    {
      "record": {
        "id": "1111111",
        "recordType": "customer",
        "Internal ID_Customer": "1111111",
        "Customer Name": "Test Customer_RB (Canada)",
        "DropShip Code": "TLP6123hH'u}",
        "Location": "Warehouse 1",
        "Internal ID_Location": "1",
        "dataURI": "https://0000000.app.netsuite.com/app/common/entity/custjob.nl?id=1111111&compid=1143708"
      }
    },
    {
      "record": {
        "id": "2222222",
        "recordType": "customer",
        "Internal ID_Customer": "2222222",
        "Customer Name": "Test Customer_RB (Sweden)",
        "DropShip Code": "b721Q7;AgBE4",
        "Location": "Sweden Warehouse",
        "Internal ID_Location": "26",
        "dataURI": "https://0000000.app.netsuite.com/app/common/entity/custjob.nl?id=2222222&compid=1143708"
      }
    },
    {
      "record": {
        "id": "3333333",
        "recordType": "customer",
        "Internal ID_Customer": "3333333",
        "Customer Name": "Test Customer_RB (USA)",
        "DropShip Code": "r9-q2RS86(;w",
        "Location": "FXR USA Warehouse",
        "Internal ID_Location": "4",
        "dataURI": "https://0000000.app.netsuite.com/app/common/entity/custjob.nl?id=3333333&compid=1143708"
      }
    }
  ]
}

When I set the "Additional search criteria"

  • Field: Internad ID
  • Operator: any of
  • Search Value: {{{Customer Code}}}

I get the preview data error mesage below

[
  {
    "code": "handlebars_template_parse_error",
    "message": "Failed to generate criteria.3.searchValue from template: {{{Customer Code}}}. Details: Missing helper: \"Customer\".",
    "occurredAt": 1720978888878,
    "resolved": false,
    "source": "application",
    "classification": "parse"
  }
]

Would appreciate any advice and thank you!

NOTE: In the article Response mapping, results mapping, and advanced lookups example for NetSuite You can set Internal ID is {{Netsuite ID}}. On my end I only have the options "any of" or "none of".

2

@raulbernales,

Please try setting the search value as {{[Customer Code]}}.

Use square brackets to reference field names or values that contain spaces.

For more details please refer - https://docs.celigo.com/hc/en-us/articles/360038856752-Handlebars-syntax

3

@sreevaniamara

I've just tried {{[Customer code]}}, and this time I got the error message below

[
  {
    "code": "handlebars_template_parse_error",
    "message": "Failed to generate criteria.3.searchValue from template: [{{[Customer Code]}}]. Details: \"Customer Code\" not defined in the model.,[object Object],[object Object] - 1:3.",
    "occurredAt": 1721056417119,
    "resolved": false,
    "source": "application",
    "classification": "missing"
  }
]

4

Hi @raulbernales
Sorry, I missed that incoming data was in rows format. Can you please try using {{0.[Customer Code]}}?
Thanks!

5

Hi @sreevaniamara, We were able to figure it out but your advice confirmed that we were on the right track. We changed our search criteria to the one below.

DropShip Code is {{0.[DropShip Code]}}

Then we were able to attached it to our FTP source data. Many thanks!