Answered

Multi-level Filter Query in Power Automate

Like (0) Share

Report

Posted on 19 Jun 2023 11:33:37 by 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:

I have the same question (0)

All responses (6)

Answers (1)

Matthy79 4,178 Super User 2024 Season 1 on 21 Jun 2023 at 10:09:27

Like (0)

Report

Re: Multi-level Filter Query in Power Automate

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.

Was this reply helpful? Yes No
RobinWhite5133 21 on 21 Jun 2023 at 08:06:56

Like (0)

Report

Re: Multi-level Filter Query in Power Automate

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

Was this reply helpful? Yes No
Verified answer

Matthy79 4,178 Super User 2024 Season 1 on 20 Jun 2023 at 12:02:15

Like (0)

Report
Re: Multi-level Filter Query in Power Automate

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&colon;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.

Was this reply helpful? Yes No
RobinWhite5133 21 on 20 Jun 2023 at 09:10:36

Like (0)

Report
Re: Multi-level Filter Query in Power Automate

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

Was this reply helpful? Yes No
Luis Arellano 247 on 20 Jun 2023 at 00:43:17

Like (0)

Report

Re: Multi-level Filter Query in Power Automate

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

Was this reply helpful? Yes No
Matthy79 4,178 Super User 2024 Season 1 on 19 Jun 2023 at 21:20:17

Like (0)

Report

Re: Multi-level Filter Query in Power Automate

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.

Was this reply helpful? Yes No