Extract data from file created in Sharepoint and put it into Excel

(0) Share

Report

Posted on by ADP5

Hello, I have the following automated process:

1. Invoices are being received by e-mail

2. Someone uploads the invoices to the proper location on Sharepoint

3. Data is extracted from invoices and put into an Excel file through the use of Power Automate (AI Builder and flow)

The flow's trigger is "When a file is created (properties only)". So, when an invoice is uploaded to Sharepoint, the flow starts running. My problem is that if another invoice is uploaded before the flow finishes running (for the previous uploaded invoice), there will be two identical entries in the Excel file. Basically it inserts the data only from one of the invoices twice. The same happens if multiple invoices (in bulk) are uploaded at the same time. Therefore, it seems that invoices can only be uploaded individually/separately and the person who uploads them must wait for the flow to finish running for each of them, in order for the data in the Excel file to be correctly entered.

The flow trigger's concurrency control is on and the degree of parallelism is set to 1. Do I need to add a condition in the trigger's settings? Or do something with the Split-On Tracking Id/Custom Tracking Id? Maybe add a column in Sharepoint where the invoices are uploaded, and create some kind of condition based on it?

The strange thing is that when I manually test the flow in edit mode and I upload an invoice, and then I upload another one right after, the data in the Excel file shows accurately (two separate rows with different data). Nothing changes for bulk uploads, though.

If save and exit the flow, and then upload two invoices one right after the other, I get the issue I stated.

If anyone has a solution to this situation, I'd be really grateful. I researched but couldn't find anything. If the only way to do it is by uploading one invoice at a time and waiting for the flow to run for each, then so be it. My concern however is that there will be cases where employees will try to upload invoices in bulk or one right after the other which will result in the inaccurate population of the Excel file.

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Community Power Pla... on at

Like (0)

Report

Can you post a screenshot of your flow to give an idea of what we're working with? 😀

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report

That's definitely not expected behaviour if you've enabled concurrency and set the degree of parallelism to 1 (should be running sequentially). I would leave Split-On as it is.

I'm wondering if it's because the consecutive flows aren't picking up the updated data from Excel. There are issues where you can add or update a row in Excel, then retrieve the values again, and it won't retrieve the data you added/updated until a slight delay.

Are you able to show your flow to get a better idea?

Was this reply helpful? Yes No
ADP5 45 on at

Like (0)

Report

Hi, thank you for support. I've managed to figure out that the issue is related to "Get files (properties only)".

If "Top Count" is set to "1" and I upload two invoices one right after the other, the flow takes the data only from the last one (the one that shows at the top, as per the "Modified desc" order) and creates two separate rows in the Excel file with the same data. In the flow run history I can see that it runs for each uploaded invoice, in queue (as it should).

If "Top Count" is set to "2" or more and I repeat the process, the flow takes the data from both invoices and creates two separate rows in the Excel file with the correct data. However, this is not a solution unfortunately because if someone uploads just one invoice, it will take and enter the data from that invoice but also from the one(s) below it depending on what number "Top Count" is set to.

I'm not sure if I'm explaining properly or if there is a solution to this situation, but if you need further details please let me know.

Was this reply helpful? Yes No
ADP5 45 on at

Like (0)

Report

Hello, thank you for your support. You can find more details in my reply to @grantjenkins.

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report

When a file is created (properties only) will already give you many of the properties of the file that was just created, so no need to use Get files to get that file again.

If you wanted to get addition properties from the file that was just created, you can use Get file properties and pass in the ID from the trigger for the Id.

Was this reply helpful? Yes No
ADP5 45 on at

Like (0)

Report

Thank you for your suggestion. Here are the steps in my flow:

Step 1: When a file is created (properties only)

Step 2: Get files (properties only)

Step 3: Apply to each

If I skip Step 2 or replace it with "Get files properties", I won't have the option to insert "value" in "Select an output from previous steps in "Apply to each" (Step 3). And if I insert other dynamic values like "ID" or "body" for example, I receive an error saying it should be an array.

So, do you think it would be possible to adjust the flow from Step 3 onward as per your suggestion?

Was this reply helpful? Yes No
Verified answer

ADP5 45 on at

Like (0)

Report

I solved the issue by inserting "Get file content" after the trigger and then, in the first field from "Apply to each" I put the body from "Get file content" converted to array.

Was this reply helpful? Yes No