Speed Up Flow when Adding items to List

[jackp514]

Need help on what direcition to take this flow.

I am setting a timer to to pull our Work Orders from our ERP software using API Requests every hour. If the Status of the work order is set to "Billed" then I add it to a SharePoint list. 

Pulling 13,000 work orders every poll, 12,000 in billed status. Did a big run to get all the historical data so my list is populated correctly now. But now every run, flow is having to check through all records and fail out due to duplicate WO IDs in the SharePoint list just so I can add the one or two newly completed work orders.

What is the better way to handle this? Even with flow set to 50 concurrent runs it takes over 3 hours for the flow to complete.

How do you filter down the array to only the new work orders before I start the "Create new item" step.

[abm]

Hi

Can you get the status billed ones only from the API?

Also you mentioned "But now every run, flow is having to check through all records and fail out due to duplicate WO IDs in the SharePoint list just so I can add the one or two newly completed work orders." 

Can't see the above logic from your screenshot? Could you please explain?

[jackp514]

No unfortunately the API has no filtering capabilities of its own. Best I can do is query all work orders.

Also, to explain further, I have the option set on the SharePoint list "Do Not Allow Duplicate Values" on the primary column which is "Work Order ID#"

So I'm relying on the SharePoint list to reject all items and only add new ones. 

[fchopo]

Hello @jackp514 

You could count the number of items returned by the API call and the number of items stored in your SharePoint list. If the number of items returned by the API call is greater that the number of items stored in your SharePoint list, it means there are new items, so you could get the most recent items and create them in SharePoint list.

This would work assuming that the API returns the records always in the same order. Is that possible?

Regards,

Ferran

[jackp514]

I like this train of thought. Would get me a little closer. Let me go back to the API and see how it is ordering data.