Unanswered

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

(0) Share

Report

Posted on by Krickner

Spoiler (Highlight to read)

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.

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.

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (0)

Scott_Parker 1,090 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Scott_Parker 1,090 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report
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" ] } } } } } }

Was this reply helpful? Yes No
Scott_Parker 1,090 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report

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:

Was this reply helpful? Yes No
Krickner 81 on at

Like (0)

Report

Here is another visual of the data:

Was this reply helpful? Yes No
Scott_Parker 1,090 on at

Like (0)

Report
You seem to have the wrong array in your From parameter of your action name "Select"
Change the From parameter of your action named "Select" to be outputs('Parse Order Routing JSON')['Data']
Move the "Select" action outside of the apply to each action
Delete the now empty apply to each action
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.
items('Get_Routing_Items_for_Each_Order_Number')['PONumber']
item()['jobNumber']
etc.
Outside of your apply to each loop, add a compose action with the formula outputs('Select')
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:
Join together on what is hopefully a unique string which doesn't occur within the text itself.
Now that we have a string, replace the array breaks with just a comma.
Reconvert back into JSON.
After all of this you can delete your customerDescription variable and actions related to it.

Was this reply helpful? Yes No