Flow triggered 'when an item or file is modified' causing sequential numbering problems as flows run in parallel

[danru]

Hi,

I need advice to understand how power automate processes its flows that are triggered by a sharepoint datasource with trigger 'when an item or file is modified'.

I have a spreadsheet with a unique number column which needs to increment for each new row added.

So if I have this column:

UniqueNo

1

2

3

I need the flow to add 4 for the next row. I have the logic in place for this. 

I get the column values of UniqueNo into an array and get the max value and add 1. 

This works fine.

The problem seems to be the trigger. 

I have a powerapp connected to this sharepoint approvals list and it allows approvers to approve requests. When they approve a request, the app updates a status column in the main sharepoint approval list to 'approved' and the flow runs based on this change (see trigger of the flow above).

I'm finding that the flows, instead of running sequentially are running in parallel and the change from the first approved request hasn't been processed in spreadsheet so both requests a (first) and b (second) get the same unique number (4 in the above case).

Is this expected behaviour? Unless the flows are running in sequence but very close together such that there isn't enough time for sequential numbers to be created.

If anyone has had this issue or has any advice on how to handle it I would be most grateful.

Regards,

Daniel

[v-jefferni]

Hi @danru ,

Could you please share more details about your scenario? Could you post your flow in Edit mode and expand actions to show detailed inputs? 

Generally speaking, if you need to create a new record where includes increment numbers, you'd better get the new number and add a new row with this number immediately. So, for your case, I think you have the new number too early.

Best regards,

[danru]

Hi @v-jefferni ,

Thanks for getting back to me.

My flow runs fine if it is not immediately preceeded by another flow run that was triggered almost immediately before.

Users are approving/triggering one request quickly one after the other. So if they approve and then wait 10 seconds before they approve the next request, then it all works fine.

The problem is when the flow runs run too close to each other. The changes to the Excel table from flow 1 haven't registered by the time flow 2 runs. Hence it's working with the same array (new unique number from flow 1 not added yet) and so it generates the same number for both flows. 

Assuming the last number in the sequence is 3, flow 1 should get 4 and flow 2 should get 5 but because 4 hasn't been registered in the Excel table when flow 2 is getting the table and checking the max number (should be 4 but it's really 3 as 4 hasn't appeared in the datasource from flow 1).

Remember flow 2 runs sequentially after flow 1 but virtually immediately after so it's as if it runs in parallel.

I thought of a work around to get the prior sharepoint item item compare it's modified time to the current sharepoint ID's modified time and if it's within 10 seconds (or 5- I'm still trying to find the magic number) of each other then dynamically set a variable for the delay action so that flow run 2 will wait the 10 seconds for flow 1 to finish and then hopefully it will get the updated array from the table.

Please let me know your thoughts on this and if you have a better way.

Thank you for your help!

Regards,

Daniel

[v-jefferni · Accepted solution]

Hi @danru ,

I understood. Although you may be able to get the latest or largest number from the Excel table use below method:

order by the number column in descending, and use expression in Add a row action, due to Excel connector limitations, if the table grows too large, the List rows action won't act properly.

add(int(outputs('List_rows_present_in_a_table')?['body/value'][0]['Column1']),1)

I suggest you use SharePoint list and the default ID column instead.

Best regards,