PowerAutomate/SP List - Need help developing a flow for status/choice column to change based on dates in Renewal Date Column

[SudeepGhatakNZ]

@Anonymous ,

You can achieve this using a Scheduled trigger.

The flow could run each morning and check if any items meet that crietra by comparing the current date with Renewal/Expire date 

If yes, use the Update item action to update the Status

[ManishSolanki]

Hi @Anonymous 

Pls refer the below sample flow:

1. We will create a schedule flow that runs on daily basis and check the dates:

You may set the time as per your need.

2. Next, we will filter out the records for Expired cases using 'Get items' action:

You can just copy paste the below filter query if internal name of column 'Renewal/Expiry Date' is same:

'@{startOfDay(utcNow())}' gt Renewal_x002f_ExpiryDate

3. We will iterate the results and set the status to 'Expired':

4. Similarly, we will use another 'Get items' for filtering 'Review' case records:

Below is the expression used in 'Get items 2' filter query, you may copy & paste if internal name of column 'Renewal/Expiry Date' is same:

Renewal_x002f_ExpiryDate lt '@{startOfDay(addDays(utcNow(),-180))}'

here, we are subtracting 180 days for getting past 6-month date.

5. Now, we will iterate the results of 'Get items 2' action and set the status to 'Review':

If this helps, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Anonymous]

Hello @ManishSolanki thank you for the detailed screenshots 

Could you please help me understand the second filter that you provided? A bit confused here about the syntax as I am new to the power platform

In using the second filter, the statuses on my list do not change to "Review" based on the dates that I set in my Renewal or Expiry date column.

To provide you with an example: If I have a record that is set to expire in 12/2/23 - it should be under "Review" for the last 6 months that it is good for. Thus, it shall show as "Review" for the month of June, July, August...... December

Thank you again 

[ManishSolanki · Accepted solution]

Hi @Anonymous 

As per my understanding for second case, you want to make the status 'Review' if renewal date is between today and (today + 180) days (within 6 months). In that case, pls use the below expression as a filter query in 'Get items 2':

Renewal_x002f_ExpiryDate ge '@{startOfDay(utcNow(),'yyyy-MM-dd')}' and Renewal_x002f_ExpiryDate lt '@{startOfDay(addDays(utcNow(),180),'yyyy-MM-dd')}'

You may directly copy & paste to filter query parameter in 'Get items 2' action.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Anonymous]

Thank you @ManishSolanki Manish! This has solved my use case and has helped me better understand the nature of filtering

[Anonymous]

@ManishSolanki Manish, how can I stop this reminder for when a column is updated for a specific line item? I want users to be able to take action on the item in submitting a microsoft form with two questions: 

1) Did you take Action on this notification - yes or no 

2) Comment - Free Text 

I want to send these form answers to two columns in my list which will indicate whether or not the flow will continue sending reminders for the specific line item. how can I halt the flow once list updates come in? 

The list uses a "Record ID" Column to reference to individual records

[Anonymous]

@ManishSolanki  

Thank you in advance