Unanswered

Power Automate Comparison of Excel Data vs SP List Data Create New Records

(0) Share

Report

Posted on by Ishmale

Hello,

I am having issues with my Flow adding new records to a sharepoint list after comparing a list of records from an excel file.

Purpose:

I get a file from HR each week that updates headcount and ID numbers. I drop the file in a sharepoint folder and replace the previous week's file. My flow triggers when I drop the new file. My flow should compare all the IDs in the excel file versus the IDs in the SP List, then add any new person and ID to the SP list, if there are no new IDs then do nothing.

Issue:

My flow times out, the typical size of my list in excel is 700-800 people, I have tried filtering on IDs in SP with IDs in excel to reduce the number in get items but this does not help because the new IDs aren't in the SP List. Another point, a lot entries on the list need me to update IDs manually because the EMP ID doesn't exist in the file because the employee start date, so I can't just replace all entries every time I add the new file or I would have to update all emp with old start dates every time and and its quite time consuming.

Picture of flow attached, any ideas would be much appreciated.

Categories:

Building flows

Comparison Flow.JPG

I have the same question (0)

All responses (21)

Answers (0)

Unknown geen idee 1,757 on at

Like (0)

Report
Dear I,

There are a few thoughts I have looking at your situation.
First of all, what you try to achieve seems very reasonable and well within the capabilities of PowerAutomate.

Why timed out? What is the error message? This should not happen.
Does it only says 'timed out' on the top of the page when you let the flow run ... or is there an actual error message in the flow itself. Sometimes the page itself times out, but the flow is still running. Check the 'Run history'.
Does your flow always time out on 250 items? Then turn on pagination in the 'List rows present in table' settings to get row item read in this action.
Move the 'Get items' action out of the loop, place it before the 'Apply to all'. There is no need to repeatedly get new data. Wasting time.
Use a 'Filter array' in the Apply to All loop to determine if an ID already exists. Filter based on current ID and the output from the 'Get items' action. If the output is empty, then you need to create a new record.

Hope the suggestions help,
Happy flowing,
Koen

Was this reply helpful? Yes No
Ishmale 51 on at

Like (0)

Report

Thank Koen,
I will attempt your suggestion now.

Was this reply helpful? Yes No
Ishmale 51 on at

Like (0)

Report

Hello Koen,
My flow has failed, I believe I might have my filter array configured wrong.
Photos attached.

RunFailed2.JPG

RunFailed.JPG

RunFailed3.JPG

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report
So I am confused about what gets added to the SharePoint list vs what gets updated in the SharePoint list, can you help me understand some more detail?

Please clarify or correct anything I have listed below:
HR Provides an Excel Workbook/Table of Employees
Table has Current Employees AND New Hires that have not started
Table has EmployeeID and StartDate for all Current and New Hires
New Hires that have not started, do NOT have an EmployeeID yet
New Hires receive an EmployeeID on or after their StartDate
The StartDate of New Hires can change, so the date might get pushed out between workbooks
The SharePoint list has the data from the workbook with the following conditions:
The List contains Current Employees and New Hires WITH EmployeeIDs
The List contains New Hires WITHOUT EmployeeIDs
The List contains the StartDate of all employees
What I am trying to narrow down is if EVERY person on the SharePoint List has an EmployeeID?
Or do you add people to the SharePoint list without EmployeeID?

Where is HR getting or creating this Excel workbook?

Was this reply helpful? Yes No
Ishmale 51 on at

Like (0)

Report

HR gets it from a database, don't have access to it. The sharepoint houses all information from the workbook including employees without IDs, temps don't get IDs added to the database, so I have to update the the SP list manually for temps, direct hires do get IDs. So the only thing I want is for each new workbook iteration is to transfer all new lines of employees that do not already exist in the share point.

Was this reply helpful? Yes No
Unknown geen idee 1,757 on at

Like (1)

Report
Dear Ishmale,
It seems you are still using a condition to reduce the array ... not sure if this is intended. My suggestion was to:
Use a 'Filter array' in the Apply to All loop to determine if an ID already exists. Filter based on current ID and the output from the 'Get items' action. If the output is empty, then you need to create a new record.
Happy flowing,
Koen

Was this reply helpful? Yes No
Ishmale 51 on at

Like (0)

Report

I guess I misunderstood the difference, I look up filter array and try again.

Thank you, Koen.

Was this reply helpful? Yes No
Ishmale 51 on at

Like (0)

Report

Hello Koen,

I put in the filter array but now my flow duplicates the entries, can you show me an example of how this is suppose to be ran?

Was this reply helpful? Yes No
Unknown geen idee 1,757 on at

Like (1)

Report

Dear I,

If you configure the Filter Array as per below, you will not have a 2nd loop (Apply to each).

Filter Array
From: output 'Get Items'
outputs('Get items)?['body/value'] contains items('Apply_to_each')?['ID']

If the filter array has any length() ... you know there is an existing ID ... if not, then you create a new one.

Hope this helps,
Happy flowing,
Koen

Was this reply helpful? Yes No
Ishmale 51 on at

Like (0)

Report

Hello Koen,

So do I then need a condition after the filter array or does the filter array function as a condition?

Was this reply helpful? Yes No