Forum Discussion
Grouped by aggregate value rules in Sharepoint list
Hello everyone,
I am working on an inventory in Sharepoint Online. I want to set up a rule to that when the grouped by aggregate value reaches a certain number (eg 2) an email is sent. I thought Power Automate could work, but not sure how to go about accessing group by aggregate values.
For example, in the image below, I would get an email reminder for Ionomycin Sigma if I delete 2 of its children (called "derivatives" in the groupby header) and I set up the rule for "2".
Any help is appreciated.
Thanks!
sflows Power Automate can't access the grouped by aggregate values but you can still use a flow to send you an email when the stock level reaches whatever level you want to set.
So in this example there are 3 Smart Irrigation Systems in stock. We're going to send the email when the stock level goes down to 2.
1. The trigger in the flow is the SharePoint "when an item is deleted". Next initialize a float variable which will hold the count of the items.
2. Add a compose action and select the column with the name of the item, in my case it's the Title column. Next add a SharePoint "get items" action and add a filter query of Title eq '' and inside the single apostrophes select the outputs of the previous compose action. This will limit the items brought back to just the same one as the item you deleted which will improve performance if the list is large.
If your get items is likely to bring back more than 100 items you need to go to the items settings, turn on the paging toggle and set the threshold to a higher number.
3. Next add an apply to each and select value from the get items section of the dynamic content box. Add an increment variable action, select your variable and type 1 in the Value field.
4. Outside the apply to each add a condition of varCount is less than or equal to 2. Leave the red if no channel empty. In the green if yes channel add another compose action and select varCount for the inputs field.
5. Finally, still inside the green if yes channel, add the send an email action. In the subject field I've just added the outputs of the original compose with the name of the item. Add in any text and select the outputs of previous compose to display the stock level in the email.
And this is the result.
Hope that helps.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
