Unanswered

Comparing two SharePoint lists to determine which items are missing

(0) Share

Report

Posted on by 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.

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (0)

efialttes 14,756 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
kylemanor 2 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No