How to create a Flow in Power Automate to refresh a Date column in Lists with several rows under it?

[gdemedeiros1171]

I need to create a flow in MS Power Automate that will open and refresh a Purchase/Renewal Date column in MS Lists. This column is used to calculate a Days To Renewal column that returns the amount of days until renewal. I'm using the following expression below in the Days To Renewal column to calculate the days: (IF(ISBLANK([Purchase/Renewal Date]),"",[Purchase/Renewal Date]-TODAY()).)

I would like to run the Flow, so i don't need to open the Purchase/Renewal Date rows individually to refresh the date and the Days To Renewal calculation to proceed.

Any help is appreciated. Thanks!

[David_MA]

This is very similar to something I have. This is my flow:

Explanation:

• The flow runs once per day.
• The Get items action has a filter query to only return relevant items. For me, it is Status eq 'Active' and POExpires ge '@{formatDateTime(startOfDay(utcNow()),'yyyy-MM-dd')}'.
  • If you have more than 100 items in the list that you need to update each day, you will need to enable paging in the get items action.
• The first condition checks to make sure items were found from the filter query. It uses the length expression and makes sure it is greater than 0.
• In the Apply to each, the condition checks to make sure that the PO expiration date is still in the future. It uses this expression: dateDifference(formatDateTime(utcNow(),'yyyy-MM-dd'),items('Apply_to_each')?['POExpires']). It is basically making sure it is not less than 1. Without this, when there were dates in the past I had errors in the flow and this resolved it since I don't care about items where this date is in the past.
• The update item then calculates the number of days like in your scenario. I use this expression: float(substring(dateDifference(formatDateTime(utcNow(),'yyyy-MM-dd'),items('Apply_to_each')?['POExpires']),0,lastIndexOf(dateDifference(formatDateTime(utcNow(),'yyyy-MM-dd'),items('Apply_to_each')?['POExpires']),'.')))

The result (I use JSON formatting in the list view to calculate this on the PO Expires date field).

But I need a real number in the Days til Expire field because I have other flows that get triggered when the item is updated and it equals 30, 60 or 90 days in order to send out notifications.

[gdemedeiros1171]

HI, David...

Thanks for your help, as you can see i really need it :)!

My Lists has 72 rows with Purchase/Renewal Date housing the date the application is to be renewed. I created a calculated column callen Days To Renewal that takes that date and based on Today's date it calculates how many days are left (or has gone over) to renewal.  Below is just a sample of my Lists. Also, I was able to configure the Get Items, however, having questions on how to configure and link the Get Updates part. 

Here's what I have so far...

Also, 

Purchase/Renewal Date

Days To Renewal

My MS Lists:

[David_MA]

I don't understand what you're asking with regards to the "Get updates part." I don't see anything in your flow that has that name.

[gdemedeiros1171]

I my Lists, I have a column called Purchase/Renewal Date. It is a Date and Time type colum that houses the date the application is set to renew its license agreement. From that column date and created a new calculated column called Days To Renewal with the claculation expression "=IF(ISBLANK([Purchase/Renewal Date]),"",[Purchase/Renewal Date]-TODAY())". The calculation works fine, however, for it to be triggered, i need to manually open the Purchase/Renewal Date column and double-click on the date field to refresh it. Then the calculation return the correct number of days to renew the application. 

I was looking if we could set up a Flow and schedule it to open/refresh the Purchase/Renewal Date column and all the rows under it, thus triggering the claculation on the Days To Renewal column. 

This is what I have so far, however, test is failing 😞 

[gdemedeiros1171]

Updated my Flow with the following, however, it is still failing 😞

[gdemedeiros1171]

[David_MA]

A flow cannot open your list items so it recalculates a calculated column. What I outlined in my original post would require you to create a column that is not a calculated column to calculate the value and populate it into a number column. In my example, Days to Expire. I thought that is what you were originally asking how to do.

[gdemedeiros1171]

Thanks again, David! Sorry for being such a novice, however, are you saying I need to create an additional column in MS Lists? If so what kind of column should that be (I'm assuming "Date") and what data should I upload to it? Thanks again for your assistance and support...learning a lot :)!

[David_MA]

You can use either a single line of text or a number column. I would use a number column because then you can format the view with data bars. This is how it works in a list I have, which I believe is what you are trying to achieve: