Variable change without action

[ScottRMeredith]

So this has been happening to me no matter how I try to attack the problem.  I have a list of employees (almost 16000 records) in a SP List(record of source).  I also have a pre-existing Model Driven App with dataverse tables with the same data and some more fields added.  What I am trying to accomplish is weekly run an flow that will compare the two lists and apply the changes in the SP list to the dataverse table and add any new records from the SP list to the dataverse table.  Fairly straightforward, but the problem I was having is in SP the employee number field was text, as well in dataverse (leading zeros), but for the life of me I couldn't get the query in the apply to each loop to find a matching record with the two fields as Single line of text fields.  So in the SP list I made it a number field.  This then dropped the leading zeros and created a new problem.  So I fixed this with a switch and length combination and got the flow to run fairly successfully once (24 records errored out of 15711).  I made some adjustments to try and account for the errors and the main problem I am having returned.  This is the 3rd iteration of a flow created from scratch so nothing was copied and pasted from previous flows that had the same problem could creep in from.  

So now that I have bored you to death, my problem is in my apply to each loop when I set my employee number variable, or just use the found record from the SP Get Items, once I move on to the query of the dataverse table, the value changes, and there is no other action in between that would change them.  This causes every record to error.  I have spent now nearly 60 hours trying to figure this out and cannot find a solution anywhere.

Here is how my flow is built.....

And here are the outputs I am getting.....

Here is when the variable is initially set in the apply to each loop...

 It passed through my switch without any manipulation....

Here is where it queries the Dataverse table....the employee number it is using is not the one in the variable it is set to use.

I have no hair left, so please help.

Thank you

[ScottShearer · Accepted solution]

@ScottRMeredith 

If you have concurrency control turned on for your loop, turn it off as that is likely the cause of your issues.  Setting the value of a variable inside of a loop with concurrency turned on will cause unpredictable values for the variable.

[ScottRMeredith]

I do have that turned on to speed things up, but will turn it off and see how that goes.  I will let you know.  Thanks.

[ScottShearer]

@ScottRMeredith 

Assuming that I am correct about the issue, you can eliminate the need for the variable by using an expression instead of the variable in your List Rows action.

[ScottRMeredith]

I am using the variable because I am in my switch action getting the length of the employee number (4, 5, or 6 characters) and then adding back in leading zeros.  In Dataverse the employee number is stored as a SLT, while in SP it is a number field.  So I was using the variable to be able to change it based on the length and still reference the same named piece of data.  I am open to learning a different way to accomplish the same thing, but don't know how currently.

Also for the limited time it has been running, each record is not erroring out right off the bat like it was previously so it looks promising.

Thanks

[VictorIvanidze]

Use this:

concat(substring('000000', 0, sub(6,length(variables('varEmpNum')))), variables('varEmpNum'))