Multi-level Filter Query in Power Automate

[RobinWhite5133]

I have created a Risk Register using a SharePoint list and am trying to create a flow that will send a reminder email when a Risk is due for review.

The issue I am having is with the filter. I have a date when the last review was carried which I want to use, and 2 other columns which have numeric value based on the Residual Impact & Residual Probability of the Risk occurring creating a residual Risk Score.  When the Risk is due for review is dependent on 3 Elements 'The Last Review Date', 'The Residual Impact' and the 'Residual Probability'. For example, if the Review date was 90 days ago and the Residual Risk Score is Greater than or Equal to 4 and the Residual Probability is Greater than or Equal to 4 then an email reminder is sent to the Risk Owner.

Where it gets complicated (well for me anyway) is when the if the Review date was 180 days and the Risk has a Residual Risk score of between 8 & 15 I.E. 'Residual Impact' * 'Residual Probability' then I need the reminder email to be sent.

If anyone out there can help out it would be greatly appreciated.

This is my current flow:

                                                           

                                                          

                                                         

                                                            

[Matthy79]

Hello @RobinWhite5133 ,

you should filter the data before you use the "apply to each" action. In that case you can loop through all the remaining items and do not have to check the condition inside the loop. Best way is during the "get items" action. Another way would be "filter array". Of course you need to implement the logic too but it would save time during the loop.

Can you please explain again all the conditions. In your sample you talk about 90 days in another sample you talk about 180 days and in the flow I only see the 180 days condition.

[LuisArellano]

@RobinWhite5133 I am also confused a little between the explanation and the screenshot, but if I understood  the question correctly, you can use this expression in your condition to check the Risk, just replace the variables inside the mul function.

if(or(greaterOrEquals(mul('Residual Impact','Residual Probability'),8),lessOrEquals(mul('Residual Impact','Residual Probability'),15)),true, false)

[RobinWhite5133]

Matthy, I only copied the 180 Condition as the flow has parallel branches and was too big. Below are the 90 day & 360 day conditions I have put in:

90 Days

                          

                       

360 Days

                                     

                                 

In essence what I am trying to do is send an email reminder if the follwing conditions are met:

if the following is true:

• Residual Risk Score (Residual Impact*Residial Probability) is equal to or greater then 16, and days since last review date is 90 days.
• Residual Risk Score (Residual Impact*Residial Probability) is between 8 & 15, and days since last review date is 180 days.
• Residual Risk Score (Residual Impact*Residial Probability) is equal or less than 7, and days since last review date is 360 days.

The above is based on a 5x5 matrix as shown below:

Hope that all makes sense.. I just can't get my head around it...

KR

Robin

[Matthy79 · Accepted solution]

This is one way to do it.

{"id":"b11ed289-74e4-4388-9b8c-93a516e8d6cf","brandColor":"#8C3900","connectionReferences":{"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-b959cb53-5ecb-4b41-805f-06e7795a0bd2"}},"shared_commondataserviceforapps":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps/connections/62947705b1a746319ca1e59aae985afc"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_90,_180,_360_Filter","operationDefinition":{"type":"Scope","actions":{"Compose_Dummy_Response":{"type":"Compose","inputs":[{"Last Review Date":"2022-06-25","Residual Impact":4,"Residial Probability":1,"Risc Owner Email":"dummy@whatever.com"},{"Last Review Date":"2022-06-25","Residual Impact":3,"Residial Probability":2,"Risc Owner Email":"dummy2@whatever.com"},{"Last Review Date":"2022-06-25","Residual Impact":4,"Residial Probability":3,"Risc Owner Email":"dummy3@whatever.com"},{"Last Review Date":"2022-06-25","Residual Impact":3,"Residial Probability":5,"Risc Owner Email":"dummy4@whatever.com"},{"Last Review Date":"2022-06-25","Residual Impact":5,"Residial Probability":5,"Risc Owner Email":"dummy5@whatever.com"},{"Last Review Date":"2023-03-22","Residual Impact":4,"Residial Probability":4,"Risc Owner Email":"dummy6@whatever.com"},{"Last Review Date":"2022-12-22","Residual Impact":4,"Residial Probability":3,"Risc Owner Email":"dummy7@whatever.com"}],"runAfter":{}},"Select_Response":{"type":"Select","inputs":{"from":"@outputs('Compose_Dummy_Response')","select":{"Days":"@int(first(split(dateDifference(item()['Last Review Date'], take(utcNow(),10)), '.')))","Score":"@mul(item()['Residual Impact'],item()['Residial Probability'])","Risc Owner Email":"@item()['Risc Owner Email']"}},"runAfter":{"Compose_Dummy_Response":["Succeeded"]}},"Filter_Response":{"type":"Query","inputs":{"from":"@body('Select_Response')","where":"@equals(or(and(equals(item()['Days'], 180), and(greaterOrEquals(item()['Score'], 8), lessOrEquals(item()['Score'], 15))), or(and(equals(item()['Days'], 90), greaterOrEquals(item()['Score'], 16)), and(equals(item()['Days'], 360), lessOrEquals(item()['Score'], 7)))), true)"},"runAfter":{"Select_Response":["Succeeded"]}},"Apply_to_each":{"type":"Foreach","foreach":"@body('Filter_Response')","actions":{"Compose_Send_email":{"type":"Compose","inputs":"No send an email to @{items('Apply_to_each')['Risc Owner Email']}","runAfter":{}}},"runAfter":{"Filter_Response":["Succeeded"]},"runtimeConfiguration":{"concurrency":{"repetitions":50}}}},"runAfter":{}}}

You won't need variables or branches and the flow will run very quickly. Just copy that source in you clipboard and add it to a dummy flow to see what is going on. If you have problems implementing it in your flow, just give me an update, but I will need some details (structure) of the fields you get from the sharepoint list.

[RobinWhite5133]

Hi Matthy

Sorry but I'm a little confused. Where in the flow do I put the code above? Is it as an expression or just into the Filter Query in the Get Items action? KR Robin

[Matthy79]

You can't use this source in your flow. As mentioned I don't have the information to send you an update. This is just a sample how to do it. Put it in a dummy flow and see how it works. After that you can update your flow. If you need further assist, you will have to give more details about the fieldnames. But please have a look at the sample first.