cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kylemanor
New Member

Comparing two SharePoint lists to determine which items are missing

Hello,

 

I have built a Kamishibai audit process at work and have the audit results submitted through Microsoft Forms.  The results are then sent through Flow to a SharePoint list.  There are 16 possible audits each week.  I would like to build a Flow that evaluates the list of submitted audits and returns any audits that have not been completed. 

 

I attempted this by creating a second SharePoint list that simply lists all possible audits.  I then created a flow to compare the list of submitted audits against the list of possible audits.  I set it up to send an email for any audit from the list of possibles that did not have a corresponding item in the list of actuals.  I set up a test with 11 of 16 possible submitted, and expected to receive 5 items back as "incomplete".  However, I received over 200 emails (multiples of each one).  I'm not sure what I'm doing wrong here.

 

The audit names are identical in each list (users must choose their audit from a drop down list to avoid spelling for formatting mismatches)

 

Please see attached screenshots.  

 

screen1.jpgscreen2.jpg

3 REPLIES 3
efialttes
Super User
Super User

Hi!

So the problem is you are getting 200 emails and you expect only one?

If so, I think the current approach is not the right one.

I assume 'Card Nu...' is a unique identifier, so only each SP list can have one single element with a specific value, right? What's the format you declared on your SP list? I mean, is it of type 'single line of text', or 'lookup', or 'choice'?

I also assume once you iterate over all items on SP list#1, you just need to check if there is an item in SPList#2 with the same "Card nu...". Is my assumption correct?

I finally assume you expect a single email with an HTML table displaying items from SPList#1 that do not have a 'mirror' item (i.e. same "Card nu...") in 'SPLIst#2', right?

 

hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Hi!  So you are correct about your first two assumptions.  List #1 will only have up to 16 entries based on the 16 available choices.  List #2 simply lists all 16 choices.  Both are "Single Line of Text".  However, I would like an individual email for each item from list #2 that does not have a match in list #1 (in other words, which audits were not completed).  

Thanx for your clarifications!

So, if you expect to send an an individual email for each item from list #2 that does not have a match in list #1, I would replace your nested 'Apply to each' approach and use a single 'Apply to each' action block, assigning as its input 'Get items' from list#2. Inside it, I would add a 'Filter array' action block, assign as its input 'Get items' from list#1; define its condition rule to compare 'Card Nu...' from List#1 and 'Card Nu..." from List#2, you can select them from the dynamic content menu.

Still inside your 'Apply to each', I would add a Condition action block, assuming you leave the default name assigned to 'Filter array' unchange, on the left side of the condition rule I would add the following WDL expression:

 

length(body('Filter_array'))

 

On the left side of the condition rule I would type number 0. Finally, choose operator 'is greater than'

Next, on the false branch I would add the 'Send an email' action block

 

If you are not familiar with WDL expressions yet, please check this article

https://flow.microsoft.com/en-us/blog/use-expressions-in-actions/

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,879)