Automated Flow to notify based on column condition

[chrissmf2023]

Hi 

I have a list with a column showing a 'date recieved' and another column showing 'days since request'.

The 'days since request' is a calculated field using the DATEDIF and TODAY() functions to display the days since the request was added to the list.

If possible I am trying to create a flow whereby if the 'days since request' value is ">25" a flow is triggered to email select recipients

The value of column 'days since request' will update automatically each day based on the formula, therefore the date requested value will remain static and not be updated.

thanks

[rzaneti]

Hi @chrissmf2023 ,

As you technically does not have any "update" in your list, you cannot use this event as a trigger for your flow. However, you can use a recurrent trigger to run the flow in a daily basis. It contains a "Get Items" action with a Filter query for 'days since request' with a value greater than 25 and you add an "apply to each" loop to access the data of each List record. 

Alternatively to the filter query, you can pull all records and do this filter inside the "apply to each" loop.

If you have any trouble to build a flow like this, let me know and I will guide you.

[chrissmf2023]

Hi

Thanks for the reply - if you can show an example on how to build the flow that would be great!

thanks

[rzaneti]

Hi @chrissmf2023 ,

Sure, it will be my pleasure 🙂

I will be working with this SP List for this example:

Note that the "Days since Order" has a number data type.

Step 1: trigger

As you will be running this flow in a daily basis, you need to set a 'Recurrence' trigger.  You can set what time of the day do you want it to run (as a personal preference, I like to always set them during the night or early morning). In this example, the flow will run everyday (see the 'interval') at 5:30 A.M., for the time zone that I selected (-4):

Step 2: capture List items

For all flow runs, you will be accessing your list items and returning those with a "Days since order" value greater than 25. You add the 'Get items' action (attention here, make sure to ad 'Get Items' in plural, as there is also an action called 'Get item').

After select your list, you will add a filter query to make sure that only the records with 25 days since order or more returns. You can write it by using this syntax: [column_name] gt 25. In this example, 'gt' means "greater than":

Step 2.1: getting the correct column name

Note that, in my case, I used these weird "x0020" values in my column name. I did it because Power Automate apparently replaces blank spaces for "_x0020_". I found the correct column name by accessing the output of a 'Get items' action ran without any filter, which output showed me this for the first record:

Each of these bold expressions nested to the "value" are attributes of my list record, and some of them (like Task) represents columns from the list. As you can see, Power Automate is calling "Days since order" as "Days_x0020_since_x0020_order", and that's why I used is a column reference in my filter query. To get your outputs, go the flow run history and click here:

Step 3: looping the List items

As mentioned before, the output of your list will be basically an array called "value" with items representing each record. To access these items, you need to use the action 'Apply to each' and pass the 'Get items' value as parameter (you can do it dynamically, usually):

Step 4 (final): send the email 

Now you just need to capture the data from your record and add it to an email. You can compose/send the email by using the action 'Send an email', and you can set dynamic values to the receiver, subject or body of the message:

In the image above, you can see that I'm using the "received date" as dynamic content to populate the subject and the "task name" as part of my email body.

It is also possible to capture the data from all records that match your condition (more than 25 days since order) and compose a single email (it is a little more complex, but we can work with that as well).

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.