Compare Two Array for Each Unique ID and Send Email

(0) Share

Report

Posted on by Odie

I have a SharePoint List that logs a user's completion on a specific training:

This is what I've done so far:

I have three branches:

Branch 1 - Get SharePoint list from this image:

and set it to an array (varMembers_Completed [Title, EID]).

Branch 2 - Get all members from a SharePoint list and set it to an array (varMembers [EID]).

Branch 3 - Get Training List.

--------------------------------------------------------

I need help for the next step as I would like to compare the three branches.

For each title, I would like to compare varMembers and varMembers_Completed.

If an EID matched from varMembers and varMembers_Completed that means that he/she has completed the training, no action needed.

Else, I would like to place it on a list.

So the overall output, for example on an email should look like this:

Training Title: Test

[Names of those who are not on the list yet]

Training Title: Test2

[Names of those who are not on the list yet]

Categories:

General topics

I have the same question (0)

All responses (14)

Answers (1)

DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Odie

You would do an apply to each on the unique course names, then use a filter array action to return those that have attended for each course in the loop, then another filter array on the list of users, where their name is not in the array created by the filter previous.

You will end up with an array of users that haven’t attended in each loop, based on the training course.

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

Was this reply helpful? Yes No
Odie 32 on at

Like (0)

Report
Copy link

Link copied!
Hi @DamoBird365 - Could you help me visualize or provide an example on how to build it in flow?

Array 1 - Is a list of users who completed the training:

[ { "Title": "Test", "EID": "User1" }, { "Title": "trainingtest", "EID": "User1" }, { "Title": "Data Privacy Training", "EID": "User2" } ]

Array 2 - List of all the users:

[ { "EID": "User1" }, { "EID": "User2" }, { "EID": "User3" }, ... ]

Array 3 - List of Trainings:

[ { "Training Title": "Test", "FY": "22", "Start Date": "2022-02-22", "End Date": "2022-02-24" }, { "Training Title": "Data Privacy Training", "FY": "23", "Start Date": "2022-02-08", "End Date": "2022-02-23" }, ... ]
Here's my current flow:

Was this reply helpful? Yes No
Odie 32 on at

Like (0)

Report
Copy link

Link copied!

And this is the whole flow.

Was this reply helpful? Yes No
Verified answer

DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi

I missed out a wee step (i missed a select in my explanation), but the concept is similar to Comparing two arrays without an apply to each? - Microsoft Tech Community

If my attendees are as follows:

[
  {
    "Title": "Test",
    "EID": "User1"
  },
  {
    "Title": "Data Privacy Training",
    "EID": "User1"
  },
  {
    "Title": "Data Privacy Training",
    "EID": "User2"
  },
  {
    "Title": "Test",
    "EID": "User3"
  }
]

I can work out that there are two courses, 3 users and User1 has attended both, User2 needs to attend Test and User3 needs to attend Data Privacy.

I have setup three sample arrays in compose actions as follows:

Then, using an apply to each, we can loop through all courses. In the first filter I check that the title from the training array is equal to the current training title from the loop.

item()?['Title'] eq items('Apply_to_each')?['Training Title']

This returns an array of all attendees.

Then based on the article I have referenced above, I use select to create an array of EID's from the Filter Array using text editor in select. I.e. an array of EIDs of who has attended.

Finally I use another Filter Array on the original Users Array (I previously said the original filter array - apologies), where the select of EIDs doesn't contain the current EID

body('SelectFromFilterArrayEID') does not contain item()?['EID']

This will return the users that have not attended the course.

Below we can see that User2 did not attend the Test course.

And similarly, User3 did not attend the Data Privacy Course.

This is what the final flow looks like:

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

Was this reply helpful? Yes No
Odie 32 on at

Like (0)

Report
Copy link

Link copied!

Thanks! It worked perfect. Can you give me an idea on how to achieve this output:

Training Title: Test
[Names of those who are not on the list yet]

Training Title: Test2
[Names of those who are not on the list yet]

...

I would like to use the output and send it using Outlook Email.

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @Odie

If you add a select and compose to your apply to each and then two composes outside of your apply to each like so, this will bring everything together for 1 email. I am assuming it's 1 email, rather than an email per course.

Select:

item()?['EID']

Compose :

Training Title items('Apply_to_each')?['Training Title']

Join(body('SelectEIDFromDidntAttend'),', ')

ComposeReturnLine

Literally slap a return key in there 👍

ComposeString:

join(outputs('ComposeStringObject'),outputs('ComposeReturnLine'))

Output is as follows:

And users will be comma separated if more than 1.

I'll probably do a wee video on this concept as it is a really good use case. Thanks for the challenge 😉

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@Odie p.s. what is in your for each? Just being nosey.

Damien

Was this reply helpful? Yes No
Odie 32 on at

Like (0)

Report
Copy link

Link copied!

@DamoBird365 - I basically have 3 SharePoint list for the three branches.

Mapped the list to a select, then used a forEach to create an array and assign it on my array variable.

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@Odie

Mmmm... Select is an array and you have defined the two key/value pairs. You only need an array in a variable action if you want to update it, otherwise a select or compose will serve the purpose of a read only variable.

What's happening in union?

Just wondering if you need all of that extra stuff and make your flow even more efficient.

Damien

Was this reply helpful? Yes No
Odie 32 on at

Like (0)

Report
Copy link

Link copied!
Hi @DamoBird365 - It seems that the output from the compose returns an object.

The output from the SelectFromDidntAttend is as follows:
[ "User1", "User2", "User3" ]

It seems that the "EID" key is also being appended to the compose. What can I do to extract only the value?

Was this reply helpful? Yes No