How to use the condition output as variable / Sending an email to unique users

[scorpio24]

Hi All,

What is the formula if I want to use the output (true) of my IF statement to set as a variable so I could collect the data from SharePoint?

Here is the scenario,

-Lookup of the same items from List1 to List2 (Get items - BPICRR to Get items - DocsCompletion)

-If GroupName from List1 is NOT FOUND in List2, it will send an email to the AccountOfficer

-I wanted to create a table with the unique items and send it to each AccountOfficer without the loop

In my flow, Officer 1 is receiving 3 emails while Officer 2 is receiving 2 emails

Please give me solution on how to send only 1 email per Officer under the same Group Name without ruining my Lookup function. Thank you in advance!

[Nived_Nambiar]

Hi @scorpio24 

What you meant by OUTPUT = TRUE, is OUTPUT a sharepoint column or so ?

[scorpio24]

@Nived_NambiarI mean the RESULT of the condition, apologies on the confusion 

[Nived_Nambiar]

in filter query field, you have to specify OData query like below

Let's say you have a column column1 in sharepoint list, you want to filter it for values with Data, 

So Odata query would be 

column1 eq 'Data'

This is the way you should write filter query 🙂

More details refer here

https://www.spguides.com/power-automate-odata-filter-query/

[scorpio24]

@Nived_Nambiar Can you please give me a sample flow regarding this?

[Nived_Nambiar]

Hi @scorpio24 

Did you meant on how to write a condition in filter query or the complete flow itself ?

[scorpio24]

@Nived_Nambiar appreciate the complete flow itself. Thank you!

[Nived_Nambiar]

Ok I would be happy to help 

Before that i need few information.

1. What is the datatype of officer column in sharepoint 

2. So is one officer is mapped to one group or would there be a chance of multiple groups (like to know whether there is multiple tables would there ?)

3. Could you show how other list looks like ?

[scorpio24]

@Nived_Nambiar

1. datatype is String - Single line of text

2. there are chances that 1 officer handles several group names

3. that is the only list I'm concerned about

Thank you!

[scorpio24]

@Nived_Nambiar you can also refer this for the sample list

[Nived_Nambiar]

Hi @scorpio24 

So in the case of multiple group, how is the table should be, would it be multiple tables in one email or single table together in a email ?

Also do you have any columns for finding account officer email id so that we can sent them email ?

[scorpio24]

Hi @Nived_Nambiar single table together in a email is really what I am trying to do right now to prevent over notifying the officer.

Yes we do have a column for email as well for AO (Account Officer)

[Nived_Nambiar]

Ok @scorpio24 

List1 and List2 should be simmilar right ?

[scorpio24]

Hi @Nived_Nambiar if GroupName in List1 is not found in List2, then it will notify the Account Officer (with the table of GroupName under him/her) via email

[Nived_Nambiar]

Hi @scorpio24 

One doubt, if you are sending table data for group name, would it be that group name or all group names under that person ?

For more clarity, could you share how final email should look like ?

[scorpio24]

Hi @Nived_Nambiar thank you for your effort. for your info, this is what the email should look like:

with all the GroupNames in the table which is not found in List2, under the same AccountOfficer

[scorpio24]

Hi @Nived_Nambiar were you able to figure this out? or is it really difficult?

[Nived_Nambiar]

Hi @scorpio24 

I am busy yesterday , so could not look into this. 

Give me sometime, i will share how to do it 🙂

[scorpio24]

@Nived_Nambiar thank you so much!

[Nived_Nambiar · Accepted solution]

Hi @scorpio24 

For demo purposes, i used two lists 

List1 

List2 

Here we loop through items in List2 to check whether the group name from list1 exists or not grouped by credit ao email.

here is the flow

Step 1: initialize array variable as empty (will use it later)

Step 2: Get all items from list1 (GroupList1)

Step 3: Get the Credit AO emails as array from Get items output

Step 4: Use apply to each to loop distinct creditao emails like below

Inside the loop do the following

  Step 4 a: 

Set the user_array variable as empty  (to make it empty at start of every iteration)

Step 4 b: Use filter array to filter the values from get items with current value of user emails to retrive items with that credit ao email.

Step 4 c: Select only group names from the output of the filter array

Step 4d: Loop through distinct group names obtained from select operatin using apply to each like below

expression:  union(body('Select_Group_Names'),body('Select_Group_Names'))

Inside the above loop, do the following:

Step 4d 1: use get items to get items from GroupList2 with filter query using GroupName to check whether the group name items are present in grouplist2 or not?

Step 4d 2: Use condition to check whether the output of above get item is empty or not ?

expression :  length(outputs('Get_items_from_grouplist2')?['body/value'])

Step 4d 3: If the condition is false, then in No branch ypu have to do below steps :

Now add the array output of above action to user_array array variable using append to array action like below

Now out of the loop with name : Apply to each group name, do the following

Check user_array is empty or not using length function like below

If condition is false, we need to create html table out of user_array variable like below and send that html table tp that user via send email action.

Hope this helps you in designing flow

Mark it as solution if it resolves your query 🙂