Adding multiple items with the same header for an API Post

[Annika_Cederblad]

I am trying to pull orders from one system and post them in a different system using their respective APIs. Each order has a header (with the order number, date, customer, etc) and at least one item, but it could have an infinite number of items.

I have flattened and expanded the JSON that was pulled out of the first system so that the individual line items for each order have their own row and a column for each data point. I have two tables: one with the header information and a second with all the line item rows.

I would now like to put the order header and all of its line items back into a single json body to create the orders in the second system. How can I achieve this? See example below. I would love recommendations for the entire workflow but am most curious about how I can both merge the values in table two based on their Order No field while also indicating what the JSON format should be.

[sachi]

Hey @Annika_Cederblad - In general, when needing to create JSON bodies like this, we use a combination of the Insert Column step and the Merge Values step. I took your dummy data and made this sample flow to show you the steps you’ll need:

In my flow, the Pull from Google Sheets step is your Table 1 and Pull from Google Sheets (1) is your Table 2.

First, I take the Table 2 data and create a new column using the Insert Column step:

Then, I use the Merge Values step to merge my items into a single cell per Order No.

Next, I use the Combine Tables step to combine Table 1 and Table 2 together using Order No. as the matching criteria.

Lastly, I use another Insert Column step to create my JSON column:

My output looks like this:
{"Order No":"1000","Date":"8/1/2020","Customer":"Paul","Line Item":[{"Item":"Orange","Price":2.00,"Quantity":5,"Subtotal":10.00}]}

{"Order No":"1001","Date":"8/1/2020","Customer":"John","Line Item":[{"Item":"Orange","Price":2.00,"Quantity":10,"Subtotal":20.00} , {"Item":"Apple","Price":1.00,"Quantity":2,"Subtotal":2.00} , {"Item":"Banana","Price":0.50,"Quantity":9,"Subtotal":4.50}]}

Hope that helps!