Using HTTP to get API data from multiple endpoints and consolidating that data into a usable object for SharePoint

[Krickner]

Spoiler

UPDATE:  I've figured out a successful method, which I've identified in the thread below.  However, I'm leaving this open to gain feedback on a more efficient or faster approach.

Hi, I've been riding the struggle bus for a few days and could use help.  I feel I'm missing a big-picture issue with how I'm handling my data in Power Automate.

In simple terms:
I just need to add customer names to each of my orderNumber/jobNumber's

The not-so-simple explanation:
Using the HTTP action, I created a flow that GETs a JSON array from my ERP's API.  I can get the majority of the fields I need from an endpoint called "order_routing".  However, that table doesn't have my customer's name.  Therefore, I had to use two HTTP actions and get data from two different tables.

First I get all of my open order numbers from a table called "orders".  Then, I use the "orderNumber" in an apply to each loop to get all of the associated "order_routing" using the same field "orderNumber."  The order number will be something like "200024" and each "order_routing' item has multiple "jobNumber"s that are like "-01, -02, -03, etc."  

So while the "orders" endpoint has "200024", the "order_routing" endpoint may have "200024-01, and 200024-02, and 200024-03."  To further complicate matters, not only will the "order_routing" endpoint return multiple "jobNumber"s, each "jobNumber" has multiple manufacturing steps, all of which come over in the HTTP call.

I'm attempting to use loops and select to get customerDescription, customerCode, and PONumber from the "orders" endpoint and add the data to all "order_routing" items that have the same "orderNumber" from the "order_routing" HTTP call.

Below is my best attempt:

I've pasted a successful run below to show why my flow isn't working as intended.  Eventually I need to get this API data in a SharePoint list, but as you can see I didn't make it that far.

[Scott_Parker]

Are you certain that 'jobNumber' is a property of the objects in body('Parse_Order_JSON')['Data']? The simplest explanation is it that it isn't. This causes items('Get_Routing_Items_for_Each_Order_Number')?['jobNumber'] to return null. Perhaps you just want to use item()['jobNumber'] instead?

[Krickner]

I'll double-check, and try using item()['jobNumber'] 

[Krickner]

I changed the job_number value in the Select action to item()['jobNumber'] and got the following error.
"The template language expression 'item()['jobNumber']' cannot be evaluated because property 'jobNumber' does not exist.  

That formula is searching 'orders' not 'order routing'.  Orders only houses customerDescription, customerCode, and PONumber.

[Scott_Parker]

Okay. Then I guess I'm confused why you're doing a Select action if you're not selecting anything from the array that Select action is operating on?

Do you know what your SharePoint schema is going to look like? It would be helpful to see a list of your entities and which fields should be under each.

[Krickner]

You're probably correct, I've been working on this for three days and I think I've gotten myself turned upside down.  I wasn't using a "Select" originally.  I'll post both schemas below. 

Here is my schema that gets my customerDescription, customerCode, orderNumber, and PONumber:

{
  "type": "object",
  "properties": {
    "Data": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "orderNumber": {
            "type": [
              "string",
              "null"
            ]
          },
          "customerCode": {
            "type": [
              "string",
              "null"
            ]
          },
          "customerDescription": {
            "type": [
              "string",
              "null"
            ]
          },
          "PONumber": {
            "type": [
              "string",
              "null"
            ]
          },
          "status": {
            "type": [
              "string",
              "null"
            ]
          },
          "uniqueID": {
            "type": [
              "integer",
              "null"
            ]
          }
        }
      }
    }
  },
  "required": [
    "Data"
  ]
}

Here is my schema that gets "order_routing" data, which is the bulk of my data.  

{
  "type": "object",
  "properties": {
    "Data": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "actualEndDate": {
            "type": [
              "string",
              "null"
            ]
          },
          "actualPiecesGood": {
            "type": [
              "integer",
              "null"
            ]
          },
          "actualPiecesScrap": {
            "type": [
              "integer",
              "null"
            ]
          },
          "actualStartDate": {
            "type": [
              "string",
              "null"
            ]
          },
          "burdenRate": {
            "type": [
              "integer",
              "null"
            ]
          },
          "departmentNumber": {
            "type": [
              "string",
              "null"
            ]
          },
          "description": {
            "type": [
              "string",
              "null"
            ]
          },
          "employeeCode": {
            "type": [
              "string",
              "null"
            ]
          },
          "estimatedEndDate": {
            "type": [
              "string",
              "null"
            ]
          },
          "estimatedQuantity": {
            "type": [
              "integer",
              "null"
            ]
          },
          "estimatedStartDate": {
            "type": [
              "string",
              "null"
            ]
          },
          "itemNumber": {
            "type": [
              "integer",
              "null"
            ]
          },
          "jobNumber": {
            "type": [
              "string",
              "null"
            ]
          },
          "laborRate": {
            "type": [
              "number",
              "null"
            ]
          },
          "lastModDate": {
            "type": [
              "string",
              "null"
            ]
          },
          "leadTime": {
            "type": [
              "integer",
              "null"
            ]
          },
          "machinesRun": {
            "type": [
              "integer",
              "null"
            ]
          },
          "numberMachinesForJob": {
            "type": [
              "integer",
              "null"
            ]
          },
          "operationCode": {
            "type": [
              "string",
              "null"
            ]
          },
          "orderNumber": {
            "type": [
              "string",
              "null"
            ]
          },
          "uniqueID": {
            "type": [
              "integer",
              "null"
            ]
          },
          "partNumber": {
            "type": [
              "string",
              "null"
            ]
          },
          "shift2DefaultEmployeeCode": {
            "type": [
              "string",
              "null"
            ]
          },
          "shift3DefaultEmployeeCode": {
            "type": [
              "string",
              "null"
            ]
          },
          "status": {
            "type": [
              "string",
              "null"
            ]
          },
          "stepNumber": {
            "type": [
              "integer",
              "null"
            ]
          },
          "timeUnit": {
            "type": [
              "string",
              "null"
            ]
          },
          "totalActualHours": {
            "type": [
              "number",
              "null"
            ]
          },
          "total": {
            "type": [
              "number",
              "null"
            ]
          },
          "totalEstimatedHours": {
            "type": [
              "number",
              "null"
            ]
          },
          "totalHoursLeft": {
            "type": [
              "number",
              "null"
            ]
          },
          "vendorCode": {
            "type": [
              "string",
              "null"
            ]
          },
          "workCenter": {
            "type": [
              "string",
              "null"
            ]
          },
          "workCenterOrVendor": {
            "type": [
              "string",
              "null"
            ]
          }
        }
      }
    }
  }
}

[Scott_Parker]

So those are the JSON schemas that you're getting back from the API calls. And you're trying to produce a single array in the end? So only a single flat list in SharePoint? Which means you want to add the properties from your parent object, orders, to the child object, order_routing?

[Krickner]

Yes.  I really only need "order_routing" data as I'm using the information to create a schedule in SharePoint.  My ERP API gives me everything I need in the "order_routing" endpoint except for 'customerDescription', 'customerCode', and 'PONumber'.  So, I'm trying to figure out a way to add that data to "order_routing" data before bringing it into SharePoint.  Both data sets have an 'orderNumber' field.  

My SharePoint data structure will look like this:

[Krickner]

Here is another visual of the data:

[Scott_Parker]

You seem to have the wrong array in your From parameter of your action name "Select"

1. Change the From parameter of your action named "Select" to be outputs('Parse Order Routing JSON')['Data']
2. Move the "Select" action outside of the apply to each action
3. Delete the now empty apply to each action
4. Inside of the Select, add everything that you want to have in your list. Use item() for Order Routing level properties and items('Get_Routing_Items_for_Each_Order_Number') for all Order level properties. e.g.
   1. items('Get_Routing_Items_for_Each_Order_Number')['PONumber']
   2. item()['jobNumber']
   3. etc.
5. Outside of your apply to each loop, add a compose action with the formula outputs('Select')
   1. You can delete this action later, this is just for illustrative purposes.

So, now you have an array of arrays containing all of the information. There isn't a good way to flatten arrays out of the box, but there is a janky way we can do it using string manipulation. Create a compose action with the following formula: json(replace(join(outputs('Select'),'@#$%%$@#'),']@#$%%$@#[',','))

The reasoning behind the formula:

1. Join together on what is hopefully a unique string which doesn't occur within the text itself.
2. Now that we have a string, replace the array breaks with just a comma.
3. Reconvert back into JSON.

After all of this you can delete your customerDescription variable and actions related to it.