cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
leyburn19
Level 10

compare sharepoint lists

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.

 

Any ideas?

3 REPLIES 3
Community Support Team
Community Support Team

Re: compare sharepoint lists

Hi @leyburn19,

 

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:

List 1:9.JPG

List2:10.JPG

 

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 proper trigger, here I use Flow Button trigger.
  • Add a Variables-> "Initialize variable" action, Name set to ResultsArray, Type set to Array and Value set to empty.
  • Add a "Get items" action, specify Site Address and List Name set to List 1. 
  • Add a "Apply to each" action, input parameter set to output of the "Get items" action.
  • Within "Apply to each" action, add a "Get items 2" action, specify Site Address and List Name set to List 2. Add a "Filter array" action, From set to output of the "Get items 2" action, click "Edit in advanced mode", type the following formula:
@equals(item()?['TaskName'], items('Apply_to_each')?['TaskName'])

       Add a "Condition" action, click "Edit in advanced mode", type the following formula:

@empty(body('Filter_array'))

Within "If/yes" branch of Condition, add a "Append to array variable" action, Name set to ResultsArray and Value set to following formula:

items('Apply_to_each')
  • Under "Apply to each" action, add a "Compose" action, Inputs field set to ResultsArray variable.

Image reference:11.JPG

 

12.JPG

The flow works successfully as below:

 

13.JPG14.JPG

 

 

 

Best regards,

Kris

 

 

 

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
leyburn19
Level 10

Re: compare sharepoint lists

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.

Aaronpatel
Level: Powered On

Re: compare sharepoint lists

@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 theMS_Flow_092718.jpg 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.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 146 members 4,531 guests
Please welcome our newest community members: