Comparing two SharePoint lists to determine which items are missing

[kylemanor]

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.  

[efialttes]

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

[kylemanor]

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).  

[efialttes]

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