Data Shape Issues with API Builder

[Infuzion_Solutions]

Scenario:

I’m pulling in orders for the day to be sent to be routed.
API is called and logic is all contained within the lookup steps.
First step is to do a lookup (Can’t use export step in API)

Problem:
With an export, my first data lookup becomes the JSON envelope
In the API Builder, my first data lookup tries to ‘enrich’ a blank envelope

Data Structure:
HEADER Data {
…..
DETAIL Data {
…..
}
}

Mapping messes up that structure in flipping it for lookups on the DETAIL data. (Path to Many = DETAIL)

Details

I have the data pulling in the correct format in the first lookup step, but normally, in an export, that JSON shape is passed to the next step.

In the API Builder, I need to map that data for it to pass to the next screen.

I've tried mapping each field expressly, but I cannot map data[], so I have to use data[0], which only returns the first record: (Path to records = value, I’ve tried it with data[0].value[*] and value[0] as well)

I've tried mapping the response array into the output, but since I can't map to the root of the array (Effectively mapping to "blank") I have to give the output array a title:

This brings all rows in but messes up the file structure for future lookups. (path to many doesn't work when it's nested deeper than at the root level)

So, I reached out to support to ensure I was approaching this properly, and if so, where my misstep was. I've tried transforming the lookup response on the first lookup section, but the mapper continues to map my reshaping inside a data array on the input, then places the output inside the record object.

So how can I map similar to the first image above, field to field, and have Celigo bring in all the records instead of just the top one?

[kaustavroy]

Thanks for sharing @Infuzion_Solutions. We’re looking into it and will provide an update soon.

[tylerlamparter]

I reviewed the ticket, and it seems like you're just missing a star on the right side. Assuming your lookup returns multiple orders and you want those multiple orders, you'd need to have a star-to-star mapping like this:

After doing that, though, you're running into an issue because you need a subsequent lookup that has a one-to-many mapping (say, orders.SalesOrderSalesLines), but that won't work since one-to-many only supports going one array deep. To get around this limitation, include the additional entries in the result mapping so you get an orders array and a separate lines array at the record root level. For subsequent steps, perform your lookup on the one-to-many path pointing to lines. Then, after your final lookup, add a postResponseMap script to merge the enriched line data back into the normal order array and its lines.

[Infuzion_Solutions]

Thanks Tyler, I will try this out! Also Bradley highly recommended paying attention to what you have to say, so happy to follow your advice! Will let you know how it goes.

Tony @ Infuzion

[Infuzion_Solutions]

Here is my understanding:
Basically bring in the whole dataset into an array called “orders.”

Bring in all the lines into a new dataset called “lines.” (Header No added for future reference)

This way, I can choose my Path to Many as “orders” to enrich the header, or “lines” to enrich the lines.

Very clever. I like it.

This is done.
NOTE: Not sure why this only brought in one line record, as there are many in the original array.

Went all the way to the end of my mapping, added the following as my postResponseMap hook:

function postResponseMap(options) {
  const bundles = options?.postResponseMapData ?? [];

  for (const bundle of bundles) {
    const orders = Array.isArray(bundle.orders) ? bundle.orders : [];
    const lines = Array.isArray(bundle.lines) ? bundle.lines : [];

    // --- Build an index: Header_No -> (Line_No -> enrichedLineObj) ---
    const byHeader = new Map();
    for (const ln of lines) {
      const header = String(ln?.Header_No ?? "").trim();
      if (!header) continue;
      const lineNo = String(ln?.Line_No ?? "").trim();
      if (!byHeader.has(header)) byHeader.set(header, new Map());
      byHeader.get(header).set(lineNo, ln);
    }

    // --- Merge: copy enriched fields onto matching order lines ---
    for (const order of orders) {
      const orderNo = String(order?.No ?? "").trim();
      if (!orderNo) continue;

      const lookupForOrder = byHeader.get(orderNo);
      if (!lookupForOrder) continue;

      const orderLines = Array.isArray(order.SalesOrderSalesLines)
        ? order.SalesOrderSalesLines
        : [];

      for (const ol of orderLines) {
        const lineNo = String(ol?.Line_No ?? "").trim();
        if (!lineNo) continue;

        const enriched = lookupForOrder.get(lineNo);
        if (!enriched) continue;

        // Copy only new fields—don't overwrite the original structure/values
        for (const [k, v] of Object.entries(enriched)) {
          // Skip keys that belong to the lookup context or would collide with existing core fields
          if (
            k === "Header_No" ||
            k === "Line_No" ||
            k === "@odata.etag" ||
            k === "No"
          ) {
            continue;
          }

          // Only add if the field doesn't already exist (avoid overwriting)
          if (ol[k] === undefined || ol[k] === null || ol[k] === "") {
            ol[k] = v;
          }
          // If you want to ALWAYS keep enriched values without touching existing keys,
          // you can namespace them instead:
          // else {
          //   if (!ol.Enriched) ol.Enriched = {};
          //   if (ol.Enriched[k] === undefined) ol.Enriched[k] = v;
          // }
        }
      }
    }
  }

  // Return the same envelope with enriched orders/sublines
  return options;
}

The reverse copy is working (Still only one record. Annoying but all test data, script should work for all once data flow is good.)

My input to the final API response step disappeared after adding the postResponseMap hook, so playing with that….

[tylerlamparter]

So it looks like the result mapping is only returning one, and in my screenshot it had 2 because I had 2 orders. I missed that I should have gotten more than 2 lines in the output. Given that, the rest of the idea stays the same, but you make your result mapping return all the data, then use a postResponseMap script to do the same thing.

function postResponseMap(options) {
  for (const record of options.postResponseMapData) {
    const orders = Array.isArray(record.orders) ? record.orders : [];
    record.lines = [];

    for (const order of orders) {
      const salesLines = Array.isArray(order?.SalesOrderSalesLines)
        ? order.SalesOrderSalesLines
        : [];

      // If you do not need deep cloning, you can replace this push with:
      // record.lines.push(...salesLines)
      for (const line of salesLines) {
        record.lines.push({ ...line }); // shallow clone is usually enough
      }
    }
  }

  return options.postResponseMapData;
}