I have two SP lists. I want get a result of all the items in list one that do not appear in List two. I have looked at some of the responses here but fail to get the result I want. List one has 200 records and list two has 500 records but there will be instances in where items in list one are not in list two and these I want to report on.
Does your SP List one has same data structure with your SP List two?
Further, do you want to get a result of all the items in List one that don't appear in List two?
I assume that your SP List one has same data structure with your SP List two and there is a unique column in both SP lists. I have created two SP lists on my side and the data structure of them as below:
Note: The TaskName column in my two SP lists is a unique column which could identify one item uniquely.
I have made a test on my side and please take a try with the following workaround:
Add a "Condition" action, click "Edit in advanced mode", type the following formula:
Within "If/yes" branch of Condition, add a "Append to array variable" action, Name set to ResultsArray and Value set to following formula:
The flow works successfully as below:
I am doing exactly as you say but I think I have an issue with this as when I do a full run it was going for over an hour before I simply cancelled.
List1 has 199 records
List2 has 454 records at present
My obejcetive is to find and list the details of all the records in List1 that have no matches in List2
Both lists are similar but not the same. List one is a veh details list. List 2 is a list of vehicle bookings. List one has a unique column for a veh registration. List2 has same column but it is not unique as a veh can be booked on multiple occasions. I want to do a daily summary to my sites of all veh regos that have no bookings at all in List2.
So in principal if I was looping an array in something like VBA, as soon as a if find a match it would go to the next rego. If it looped all teh way through it would put that rego into a new array for my report
I did everything you said. To see a result, I reduced the rows to be 10 in List1 and 100 in List 2. It does work with a result array that had nothing but empty strings so I am assuming there were matches against every row. This took 2 minutes. When I expanded the get items to manage the whole list of 199 vs 454 I gave up after an hour. It was still working but that sort of time makes this a little tough.
Do you have any other ideas. The one you gave really opened my mind to variables and arrays so thank you for that as well.
@v-xida-msft. I want to thank for such clear instructions. I did exactly as per the described steps but I can't open the OUTPUTS in the very last Compose step. The only option is to save and when I open a saved file it says Unknown File Type. The flow compiles fine, but don't can't see the results.
Also, I have the same object (unique key) in both my SP lists and I want to compare just base on that one column. How could I define the filter array to just filter on that one object?
Any help is appreciated.
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!
We've given our badges an overhaul and also added some brand new ones!
Learn how to build the business apps that you need.
Find out where you can attend!
Watch & learn from the Power Automate Community Video Gallery!