Compare Two Array for Each Unique ID and Send Email

[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]

[DamoBird365]

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

[Odie]

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:

[Odie]

And this is the whole flow.

[DamoBird365 · Accepted solution]

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

[Odie]

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.

[DamoBird365]

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

[DamoBird365]

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

Damien

[Odie]

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

[DamoBird365]

@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

[Odie]

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?

[Odie]

Seems that I have to update my flow as it would be running on a huge data. The forEach is really taking time.

No update is happening on the flow, just an email that I have to send for the trainings.

Thanks for the tip!

[Odie]

As for the union, the formula is: 

union(variables('varMembers_Completed'), createArray(item()))

[DamoBird365]

Hi @Odie 

No idea where that has come from.  If you are not doing any further transformation on the select array, I would use the select array.  Take a look at the history output of the select array - you will see the output.  I cannot imagine that the foreach is actually doing anything to it.

Damien

[Odie]

My bad. I've joined it from the FilterWhoDidntAttend. It should be SelectEIDFromDidntAttend.

Thanks for such huge help @DamoBird365!