Create a one-to-many import, including nested arrays – Celigo Help Center

One to many

The One to many feature is used in cases where you have a single record that internally needs to create multiple records. This applies to advanced use cases where a parent record is being passed around in a flow, but you need to process child records contained within the parent record context.

For example, suppose you're exporting 3PL Central purchase order receipts and importing them into NetSuite as item receipts. Every order receipt will have a one-to-many relationship with NetSuite’s item receipts because each order receipt has more than one item associated with it.

As a second example, let’s say you’re exporting 3PL Central order data into NetSuite as individual items on a custom record.

{
    "orderData":
    {
        "orderId": "1234512345",
        "customerEmail": "john.doe@email.com",
        "items":[ 
        {
            "sku": "Business Cards",
            "itemId": "67890",
        },
        {
            "sku": "Pens",
            "itemId": "12345",
        }]
    }
}

In this case, your orderData has a one-to-many relationship with your items. So, to have the orderData record create multiple records internally, you’d need to set your export as One to many and map your Path to many.

Path to many

If the records being processed are JSON objects, select or enter the JSON path to the child records. If you're entering the path manually, check that the path is provided correctly by clicking Preview and viewing the details. This check is recommended because you will not receive an error if there's a misspelling in your path or the path provided is invalid. This field does not need to be set for array/row-based data. In the same dataset as above, the Path to many would be orderData.items.

{
    "orderData":
    {
        "orderId": "1234512345",
        "customerEmail": "john.doe@email.com",
        "items":[ 
        {
            "sku": "Business Cards",
            "itemId": "67890",
        },
        {
            "sku": "Pens",
            "itemId": "12345",
        }]
    }
}

Note

Using Path to many, you can only access the first-level record and not a nested array. See Nested arrays for more information and workarounds.

Nested arrays

Sometimes, your one-to-many record is in a nested array. In this case, you need to access the items in your 3PL Central purchase order records. Using Path to many, you can access only the first-level record, not a nested array. Three workarounds allow you to access nested records: create two flows that work together or create a preSavePage script or create a postResponseMapHook script.

{
    "orderData":
    {
        "orderId": "1234512345",
        "customerEmail": "john.doe@email.com",
        "items": [
        {
            "sku": "Business Cards",
            "itemId": "67890",
            "serialNumbers": [
            {
                "code": "swRkEQmd"
            },
            {
                "code": "EQ8vELmm"
            }]
        },
        {
            "sku": "Pens",
            "itemId": "12345",
            "serialNumbers": [
            {
                "code": "abvyPHCS"
            },
            {
                "code": "SWFAqWGt"
            }]
        }]
    }
}

After setting the one-to-many feature, you’ll have access to the _PARENT object.

{
  "record": {
    "_PARENT": {
      "orderData": {
        "orderId": "1234512345",
        "customerEmail": "john.doe@email.com"
      }
    },
    "sku": "Pens",
    "itemId": "12345",
    "serialNumbers": [
      {
        "code": "abvyPHCS"
      },
      {
        "code": "SWFAqWGt"
      }
    ]
  },
  "settings": {
    "integration": {},
    "flow": {},
    "flowGrouping": {},
    "connection": {},
    "iClient": {},
    "import": {}
  }
}

Create two flows to access nested arrays

The first flow will pull, modify and POST the data to a Celigo integrator.io webhook listener in the second flow. Have the export pull the receipts from your source application (in this example, 3PL Central). The import includes your raw data, which you can get using the one-to-many feature. Then, map the data or use a transformation to “flatten” it, so your items are the first nested array.
The second flow is a listener triggered once the data is sent in the first flow. In this flow, you’ll have the correct array, which you can map using the One to many feature.

Create scripts to access nested arrays

You can use one of several scripts at various stages of your flow to reach the same goal.

A preSavepageHook script is helpful to format, filter, and perform logic on the data coming from your export before it moves on to the rest of your data flow. The logic applied to the data at this stage applies to the rest of the flow.
A postResponseMapHook script performs calculations and transformations on the raw data returned and runs immediately after response mappings and regular mappings. This script must work with a lookup step that retrieves the one-to-many arrays. Use this hook to process records after response mapping before they are passed on to downstream applications in your flow. This option is preferable if you need the results to be individual records.