D365 F&O and Power Automate to compare two tables and find differences

Hello all,

Today’s blog post will be quick. I decided two write this blog because I thought this was an easy problem to solve using Power Automate. Turns out there is more to it, so I am hoping you can learn from my mistakes, also a good way for me to document the solution.

Business case: Let’s say for example you are extracting a data list from D365 F&O (in my case I am using the Workers entity as an example) and then storing the results in another database such as SQL or a SharePoint list. Let’s say you want to create a flow to keep the outside table in sync with D365 F&O, in other words a flow that will update the outside table with the new records from D365F&O. I found some answers in the Power Automate community that helped me with the solution but still had trouble coming up with the answer.

Solution:

Let’s build the flow, in my example below I am using a manual trigger to demo but in a production solution I will probably set a trigger so that the flow runs once or twice a day (Depends on your need to have the latest data available) at a set schedule.

1. The first step is to retrieve the list of values from D365 F&O (Use the D365 F&O list items present in table connector), since I am using the Worker entity,  let’s limit the results of this pull to only give me the Personnel Number. See the Select Query clause below

2. the second step is to retrieve the information from our SharePoint list, (use the get items from a SharePoint list).

after a few times of not getting the results I was expecting, I realized that this connector above has a threshold that limits the amount of records it returns by default to 245 records. So if your list has more than 245 records you need to change that amount. Click on the three dots and select “Settings”

then change the pagination threshold to something that you know will return all of the records in your list. I changed it to 5000

3. Third step is to select just the “PersonnelNumber” column from the data being returned from D365F&O and build an array list with just those values. (I will be using the Personnel number to compare the lists, in essence this is the list’s primary key). To achieve that use the Select operator in Power Automate

4. Fourth is to do the same but for the SharePoint list, use the same process as above

The whole purpose of this steps is to build two lists of values that we can compare, in order to have an apples to apples comparison list of values.

5. Fifth step is to create an Array variable that will store the results of the comparison, in other words it will store the list of personnel numbers that are not yet in our SharePoint list.  (use the initialize variable operator in flow) leave the initial value as blank.

6. The sixth step is where the magic happens, you need to loop through the list of personnel numbers from D365F&O and see if they are already in the SharePoint list. To do this first insert an apply to each step in your flow

the list of values you want to loop through is the D365F&O array of values you built in step 3. Then you must insert a condition control to check if each of one of the Personnel numbers from D365 F&O are already contained in the SharePoint list.

The result should be like this

Honestly, I am not sure if this is the most efficient way to achieve this process. If you know of a better way please comment below. I do know that it works, although the process could take some time to loop through all the records if you have a big list. It took 3 minutes to loop through 347 records during my test, that’s about two seconds per record.

The result should be, if the personnel number is already in the SharePoint list then do nothing. That will be our “yes” route, if the personnel number is not in the SharePoint list then append the array variable we built in step  5 above. The “No” route

7. The seventh step is to get the results of our array variable and parse them out into something we can use later. For that use the Parse Json function in power automate. Here is the deal, you don’t need to really know how to write Json in order to use this. What I usually do, is save the flow up to this step and then run the flow. What this allows me to see is the Json format that’s being passed as output from the array variable.

Then I copy that sample and use the generate from sample button in the connector to let flow build the Json schema. This can take some practice to get but don’t let the format below intimidate you. Json is basically text that describes your data in between curly brackets.

8. The next step is to use the results from the variable above to now get the details of only the new data in D365 F&O. For that I will use the D365 F&O list items present in table connector to now filter the results of the worker entity to only the new ones. On the filter query box use this to filter PersonnelNumber eq ‘@{items(‘Apply_to_each_2’)[‘PersonnelNumber’]}’

Flow will automatically insert the expression above once you select the output from the array variable

you can get only the columns you need by using the select query box. Make sure the names match exactly the column names on your data source and they are case sensitive. separate them using a comma.

9. Final step is to create the records in your SharePoint list, use the Create item in SharePoint connector and pass the values for the new items only.

I am hoping you found this helpful. Until next time.

Juan Sebastian Grijalba, CPA