Grouping of Mulltiple Rows

[Gautham001]

Hi , 

I'm updating a SQL DB with multiple rows at the same time which has the Same Requestor 

Requestor	Item	Quantity
PersonX	Apples	10
PersonX	Oranges	20
PersonY	Berries	30
PersonY	Avocado	40

In this example , i would need to trigger adaptive cards to teams , 2 cards since there are 2 different requestors.

1 to Person X and the other to Y. The content of the card is the items and quantity 

Also i need to use a scheduled flow to check the DB every 5mins(time can change).

So , i need to check the DB for new records every 5 mins(since the last flow ran) , and group them according to the user and send them notifications?

i'm struggling to query the records for every 5 mins , any help on how the flow would look like?

Thank you!

[Jronash]

I've seen this general problem pop up somewhat regularly, and I don't know of a quick fix, unfortunately.

Basically, what you're wanting to do is loop through each Requestor, grab their items, and send an adaptive card to each.

Easy enough, but how do you get the list of Requestors without including duplicates?  There's no built in way to do this, so we have to take a few steps to make it happen.  Here's how I would do it:

First, I create a variable with an empty array.  We're going to need this later.

Next I have a compose block with my data.  This is the data I'm using for this test:

[
 {
  "requestor": "a",
  "item": "apple",
  "quantity": 10
 },
 {
  "requestor": "a",
  "item": "orange",
  "quantity": 3
 },
 {
  "requestor": "b",
  "item": "banana",
  "quantity": 1
 },
 {
  "requestor": "b",
  "item": "apple",
  "quantity": 3
 }
]

Now we use a select block to create a new array that only has the requestor information.  We set the From field to our Compose block with all the data, and map one column (requestor) to item()?['requestor], which is the requestor data in our Compose data block.

Now the output from the Select will look like this:

[
  {
    "requestor": "a"
  },
  {
    "requestor": "a"
  },
  {
    "requestor": "b"
  },
  {
    "requestor": "b"
  }
]

We've narrowed it down to the requestors, but we still have duplicates.  But now we can use the union() expression to remove any duplicates.  Only problem is that union() needs to have two arrays to compare.  This is where that empty array comes in.  We run union() and give it our array of requestors, and our blank array variable.  The expression looks like this:

union(body('Select'),variables('array'))

The output of that expression is:

[
  {
    "requestor": "a"
  },
  {
    "requestor": "b"
  }
]

That's what we want!  Now we can start an Apply to Each block based on this new array, and we'll know that it will only run once for each requestor.  We can use a filter block on your original data to return only the items associated with each user, and post your adaptive card to the appropriate place.

You also mentioned that you're having trouble querying your records every 5 minutes.  Can you say more about what problems you are running into?  How are you currently trying to make this happen, and how is it failing?

[Gautham001]

@Jronash  Brilliant , thanks for the clear explanation! 

But could you show what you mean by Applying a Apply to Each loop on a filter block on the original data. I tried it out but could not figure it out.

Querying records issue - I'm using a scheduled flow for every 5 mins.  Then i use a get rows function and under the filter query   
 I'm trying to subtract the TimeOfRequestSubmit -   TimeNow < 300s ( 5min) ? 

Don't think this logic is feasible , do you have a better approach?

Thank you for helping!

[Jronash]

Here's one way to handle the loop and filters.

You start by creating an Apply to each block using the data from your union() expression.  It will loop once for each requestor.

Then you can use a Filter to select all of the items related to that requestor.  Set the From field in the Filter back to your complete data set.  Then you want to filter out only the items where the requestor matches the requestor in our current loop iteration.

To specify that we want to look in the requester column of the data, we use this expression:

item()?['requestor']

(or you may be able to just select it from dynamic data)

To specify that you want it to match the current requestor in our loop, you use this expression:

items('Apply_to_each')?['requestor]

I also added a Create HTML Table block just so I can show you the results when I run the flow. 

Iteration #1:

Iteration #2:

Can you post a screenshot of your database filter query?  Those can be tricky to get right.  Most likely you will have to create a timestamp that is five minutes in the past, and use an expression like [dateModifiedColumnName] ge [timestamp]

[Gautham001]

@Jronash  Please take a look at the flow i have created.

  - I have a field called usersubmittime  , I haven't figured out the filter part since i don't have earlier steps to "pick" dynamic content.  

 - In your example you used sample data , but is this similar to what you meant?

 - I am not sure what to choose in the select and Filter array fields which are left blank.

Please guide me with the missing steps and fields and if anything i have done is incorrect!.

Thank you.

[Jronash]

I have found that trying to filter dates with the OData filter in the SQL connector is a huge pain and rarely works right.  It SHOULD work like this, but your mileage may vary.

First, I'd use the Get Past Time block to create a timestamp 5 minutes in the past.

Then, in your SQL filter, set it to

usersubmittime ge '[Past time]'

[Past time] is the time you can select from your dynamic content.

If that works, great!  If not, you can take a look around the web and you'll find lots of people who say they've found ways to make this work using a variety of different methods.  I haven't had much luck with any of them.

If you can make changes to the database itself, I often end up creating a stored procedure in the database itself that queries the data I want, and has a parameter for the time I want to query by.  Then I can call that stored procedure using 'Execute stored procedure (v2)', pass it the time, and get back the data I want.

For your filter - in the empty box, you want this expression:

items('Apply_to_each_2')?['RequestorName']

Items() references a collection, and you want to point to the collection in the current loop.  Your loop is named 'Apply to each 2', so in the expression that becomes items('Apply_to_each_2').  Then you need to tell it which column you want data from.  In your Select block, you named your column 'RequestorName', so you add the ?['RequestorName'] to the end of your expression.

[Gautham001]

@Jronash  Thank you so much for all the help again.

But i get this error.

The error - 

The execution of template action 'Filter_array' failed: The evaluation of 'query' action 'where' expression '@equals(items('Apply_to_each_3')?['RequestedFor'], 'items(''Apply_to_each_2'')?[''RequestorName'']')' failed: 'The template language expression 'equals(items('Apply_to_each_3')?['RequestedFor'], 'items(''Apply_to_each_2'')?[''RequestorName'']')' cannot be evaluated because property 'RequestedFor' cannot be selected. Array elements can only be selected using an integer index.'

And a quick question , 

Do i add more fields under RequestorName?

Like date location etc , and the process remains the same?

Thanks again.

[Jronash]

I think we need to back up a step. I made things confusing when I created my own data source rather than using the SQL connector like you are.  Here's what the entire process looks like using the SQL connector.  Now, I don't have a usersubmit column like you do, so I'm grouping things by marital status instead, but the process is the same. 

As before, I start by creating an empty array.  Then I have my Get rows block to get all of the data I want to process.

Then I have my select block to focus in just on marital status.  In your case of course, this would be focused on the Requestor instead.  You don't want to add more columns to this select block, because the purpose is to create a distinct list of Requestors (or marital statuses) with no duplicates.  Adding more columns will likely add duplicates.

Then we have the compose block that uses union() to remove duplicates from our Select block output.

Then we have the Apply to each loop. It's looping over the data in the Compose block with the union() expression.

Next we have our filter.  We want the filter to look at ALL the data, which means that the From field and the left side of the filter need to both point back to our source - the SQL Get Rows block.  From should be Value from the Get Rows block, and the left side of the expression should be a column from the same block.  In my case it is marital_status, in your case it looks like it would be RequestedFor.

In the Right side of the expression, we need to refer to the current requestor (or marital status) in our newly created list with no duplicates.  We can't select this from the dynamic content, so we have to use an expression.  The expression in my case is

items('Apply_to_each')?['MaritalStatus']

You will have to modify this in your case.  'Apply_to_each' may have to be changed, depending on what your loop is called (it may be 'Apply_to_each_2' or 'Apply_to_each_3', but make sure it's pointing to the current loop).  And 'MaritalStatus' will need to be changed to whatever you named the column you created in your Select box.  From your screenshots, it looks like it would be 'RequestorName'.

I have three marital statuses in my data - single, married, and null.  So when I run this flow, my loop with iterate three times.  The first time it will give me all the records where the marital status is Single, the second time the records where the status is Married, and the third time the records where the status is Null.

Hope this helps.

[Gautham001]

@Jronash  

Worked like a charm!.

But how do i send emails to each requestor in the output of the filter array.

Example  { 
                   

RequestedFor : "PersonA", 
                       Item : "Apple"
                } , 
                      {

RequestedFor : "PersonB" , 
                        Item : "Oranges"

}

I need to send a email to both Persons here with the Item they chose.

In the "To" column of the email function i used the 

items('Apply_to_each')?['RequestorName'] , but i guess they need to be separated with ' ; ' ? Not sure how to display the content!

The Rest is working fine , thank you so much!

[Jronash]

If you want to use the RequestorName data in the To field, it needs to be an email address.  In your test data, you have "PersonA" and "PersonB".  In your actual data, are those email addresses rather than names?

[Gautham001]

@Jronash  ,  I do have a Email field which i can use instead of the name i guess. But how do i display content? 

Edit:  I tried out the email field and no luck.

Thank you.

[Jronash]

You can email the data in any number of ways... the easiest may be to use a Create HTML Table block and use your Filter Array block as the source. Then you can just take the output of the Create HTML Table block and put it in the email body (as long as your email block is also in your Apply to Each loop). As for the problems with your email field, you'll have to be more specific about what's not working.

[Gautham001]

@Jronash  , 

In a  ,  Example  { 
                   

RequestedFor : "PersonA", 
                       Item : "Apple"
                } , 
                      {

RequestedFor : "PersonB" , 
                        Item : "Oranges"

}

I want to send a email with content like "Hey PersonA , Here is your requested item : 'Apple' ". Similar for person B and every other person in the loop. The HTML Body block isn't similar to what i wanted.

Any suggestions on how i can get this? Thanks for all the help!

One of the issues i am having is when i select a field from dynamic content , the flow adds a apply to each loop.

[Jronash]

The first issue is that you are trying to insert data from your original SQL action.  That action still has ALL of the data in it, for all of the requestors, so you don't want to be using it to build your message.

Rather, you need to use the data from our Filter action.  The output from that action will only contain the data for the relevant person.

But you still have a second problem. 

In your original question, your data had multiple items for each requestor.  The problem we were solving is - How do you group all of these requests and send them all at once, so that the requestor doesn't get multiple emails, one for each request?  We want them to get ONE email that contains ALL of the requests, right?

Our solution was to create a Grouping function, so instead of one array with all the items in it, we now effectively have an array for each requestor, that contains ONLY their items.

The way you want to format your message (Hey PersonA , Here is your requested item : 'Apple'), you are acting as if the PersonA will only have 1 item.  But PersonA could have many items, and Flow knows this, so when you try to insert one of those values, Flow will automatically put it in a loop, because it knows that there could be more than one. 

Create HTML table gets around this by grouping all of PersonA's items into one output that can be displayed without a loop.  The disadvantage is that you have less control over the output.

If you want to have more control over the output, you're going to have to do something like create a string variable, loop through each of PersonA's items and append something like "You requested: Item" to your string variable.  Then, when you send the email, you can just use that variable.

You say you're new to Automate, and this is not a beginner-level flow that you're trying to build.  I'll try to explain things as best as I can, but until you have a better understanding of how Automate works with arrays vs individual items, this is going to be confusing for you.

[Gautham001]

Hey , @Jronash 

I wanted to use the second method you mentioned - If you want to have more control over the output, you're going to have to do something like create a string variable, loop through each of Person A 's items and append something like You requested: Item to your string variable.  Then, when you send the email, you can just use that variable.

I have created a string variable named Items Variable  , and created two append to string actions since i am not sure if i should create  a Apply _ to _ each loop before the filter array or after. Also i am not sure what the input to the apply to each loop would be , i am assuming it should be value , but i am not sure what to expression would be like to filter only Person A's items. Could you please show me how to carry out this process? 

Is there a way to dynamically filter each person's items? (In this scenario i have two people - Person A & B , but that is just an example , is there a way to make this dynamic? In a scenario of 10 people , i would like to create a Adaptive card for each member and send only their requested items. Is this possible?) .

Thank you so much for the continued help , this is the last puzzle in my flow , there won't be any more questions lol

This is my flow now , 

Is this the right way to use the string  append variable?

[Jronash]

One of my favorite things about Automate is that if you look at Run History, you can see the output of every single action.  This is a great way to see how you are transforming your data with the various actions and expressions that you are using.

One thing to remember is that once an action has completed, its output does not change.  So when you run Get Rows (v2) at the top of your flow, it gets all the rows for all the items and people.  If you use a Filter Array later on to filter those results down, it doesn't change the results of Get Rows (v2), instead, it creates its OWN output, containing a copy of any of the rows from Get Rows (v2) that matched your filter.

In your current flow, you are looping over the output from Get Rows (v2).  As you have already learned, this doesn't work for your purposes, because it loops over ALL of the rows, instead of selecting only the rows for a specific person.

If you run your flow the way it is and check the output of the various outputs, I think you will find that you already have what you need.  You ask if there is a way to filter each person's items.  That is what we are doing in the Apply to each block (the second one, at the end of your flow).  We are looping over each person in your data, and running a Filter Array to return just their items.  If you check the run history and look at the output from Filter Array, you will see that it contains only the items for the person in the current loop iteration. 

This means that the loop that you use to append to your string variable will need to be based on the output from the Filter Array block, not the Get Rows block.  It also means that your new loop will need to be built INSIDE the loop with the Filter Array block - where you currently have your Create HTML Table block.

After you have created your loop and appended all the values from Filter Array, you can send your Teams notification.  This will also need to be in the Apply to each loop (the one with the Filter Array block, not your new nested loop with the Append to String block).

You'll need to remember to reset your string variable back to blank at the end of the loop, so that it doesn't carry over data from one person to the next.

[Gautham001]

Hey @Jronash  , 

Thank you for the clear explanation which really helped out thoroughly.

I carried out the steps you mentioned in the last reply and i am very close but stuck with just one flaw which i am not able to solve myself.

Is this similar to what you meant ?

If this is what you meant , 

1. The body of the Filter array would return the entire record(all the columns in the row/record). I want to get only the Field Name "ApproveEmail" . Should i filter out the output of the filter array before adding them to string variable? Or is there any other way. I need this column because it stores the emails of the users the notfications gets sent to which i need to use in the team card action.

Should i use something like body('Filter_array')?['ApproverMail']     ? to get only the mail fields

2. If there are 3 different people in the output of the filter array , the string can have more than one different approver mail. Can i use that string variable in the teams card "To" Field. Because that action does not allow multiple users when used with multiple emails separated by " ; ".

Output of filter array in run history : 

As you see , the whole record is returned. Which is Perfect , but i want to know how to filter out only the "ManagerEmail"/"ApproverMail" field.

And if there's multiple values , i think the apply to each loop (3) -  in the pics i posted , should add all of them to the string variable.

Thanks for the continued help , really appreciate it.

[Jronash]

Here's how I would do it:

Now originally, we were grouping by requestor name.  So in our first loop, we would have been looping over a list of names.  After we realized that we needed the email address in order to send the Teams notification, we talked about updating the flow so that it would group by the email address, not the name.  Did you make that change in your flow?

Assuming you did, here's the breakdown.

After the filter, we create a second Apply to Each.  This one is looping over the output of our Filter.  This isn't where you set the email address, this is where you create the body of the notification.  You can use dynamic content to create your message, but make sure you are using dynamic content that comes from the Filter array block.

That's all you want in your second loop.  Everything else should be back in the first loop.

Then I created a "Pretend this is a Teams Notification" block, just so you can see how I set those values.

For the To: field, I use the current ManagerEmail with the expression:

item()?['ManagerEmail']

This might be different in your flow.  The last time you posted that part of your flow, you were calling this 'RequestorName', and it was a name, not an email address.  If you don't remember, check your Select box.  Make sure the value on the right side points to the email address that you want to send the Teams message to, and make sure the name you typed on the left side is 'ManagerEmail' (or whatever you want to use).

For the message body, I use my text variable. 

After that, I have to reset the text variable so it won't include these results for the next message it sends.  I do this by setting the variable to null().

Here's what that block looks like when I run the flow.  I have two email addresses in my sample data, so it runs twice.  This is the first run:

This is the second:

[Gautham001]

Hey @Jronash  , 

Can you share the expression for the variables "quantity" & "Item" in your image.

I would need to group by "RequestorEmail" and later pick the "ManagerEmail" from the output of filter array (like you have done for quantity and item) and use that as the "To" email field in Teams Notification.

I carried out the steps you mentioned and it works like a charm for the first run ,

However the second run onwards the variable has 2 values of "ManagerEmail" , looks like the append to string variable with null should be applied earlier?

This is the flow i'm running now.

 - The expression for the variable "ManagerEmail" is , items('Apply_to_each_3')?['ManagerEmail'] , i am guessing this is wrong. Please correct if so.

Run History : 

Run 1 :

Works Fine 

Run 2 and onwards (IF Filter Array output has more than 1 distinct/unique emails)

Value is appended to the string variable.

Ex : If value in string variable is "email1@test.com" , the Teams Notification is sent to "email1@test.com"
Run 2 : String variable is "email2@test.com" , The Teams Notification is sent to email1@test.comemail2@test.com - how do i stop this?


Also just to be clear again , i wanted to group by "requestoremails"  and in the output of the filter block get the "ManagerEmail" of the records/items in the output of the Filter Array

Example : Output of filter array :

{
Item: A
Quantity : 1

RequestorEmail : abc@test.com

ManagerEmail : efg@test.com

}

{
Item: B
Quantity : 2

RequestorEmail : xyz@test.com

ManagerEmail : tyu@test.com

}

I want to pick up the 2 Manager emails and send the notifications for them , Like you have done for quantity and item.

Apologies For the unclear explaining earlier.

Thank you for your continued support , much appreciated.

[Jronash · Accepted solution]

Your problem with email addresses in the second loop iteration is because you are using "Append to a string variable" to set null() instead of "Set variable"

Append adds the value to the end of the current variable.  So if your variable is currently 'email@email.com', and you append null to it, the new value is 'email@email.com(null)" (except of course you don't see null, because it's null).

If you change that append block to a Set variable block, it should solve that problem.

As for how I set quantity and item on my flow, I selected them from the dynamic content.  Just be sure to select them from the Filter Array output, not from your original data output.

If you don't see it there, you can always use an expression.  Assuming this is in the Apply to each block that is looping over your Filter Array output, and assuming your data is organized they way you have presented it here, you could access those values with these expressions:

item()?['quantity']
item()?['item']