cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
newgirlflow
Helper II
Helper II

Update Status of Item based on Days before Expiry Date

Hi! I have a SharePoint list which is similar to below. The expected outcome is that X days before the Expiry Date, the status of the item in the list must be updated. The X days is based on a column from the list.

 

Columns:

- Title - name of contract

- Start Date - start date of the contract

- Expiry Date - expiry date of the contact

- Contract Type (Lookup column) - user to choose the type of contract and then in the next column,

- Contract Type: Lead Time - this column would show the corresponding number of lead time days that the user wants for the item to be updated before expiry date (Ex. 1 day before June 25, 2021, the status of Contract 111 should be updated)

- Status (choice column) - Choices are: Not expiring (default), Pending, and Complete

1.JPG

 

I tried to make a flow in which X days before the Expiry Date, the item must be updated from "Not Expiring" to "Pending". The X days would depend on the "Contract Type: Lead Time" column.

 

Below is a screen shot of collapsed version of my flow.

2.JPG

 

Recurrence - scheduled once daily

Get Items - get the items from intended SharePoint site

Apply to each - this is where I try to set-up in which if the (Expiry Date + X Lead Time) = Today, then the status of each item must be updated from "Not Expiring" to "Pending"

3.JPG

Here are my expressions in the filter query:

formatDateTime(items('Apply_to_each')?['ExpiryDate'],'yyyy-MM-dd')

 

eq

 

formatDateTime(addDays(utcNow(),items('Apply_to_each')?['Contract_x0020_Type_x003a_Lead_x/Id']),'yyyy-MM-dd')

 

 

 

Continuation of the 3 step:

4.JPG

 

 

 

When I try to run, here is the error that appears:

5.JPG

 

Hope somebody can help me modify this so it can work perfectly. I am not really knowledgeable with Power Automate, especially with Filter Query.

 

1 REPLY 1
v-LilyW-msft
Community Support
Community Support

Hi @newgirlflow 

Thank you for posting.

I suggest you use Condition to judge whether the ExpiryDate is equal to today plus Contract Type:Lead Time, this will be much simpler.

I created a simple Flow, you can refer to as below:

This is my list:

vLilyWmsft_0-1624591140644.jpeg

Flow in details:

vLilyWmsft_1-1624591140650.png

Expression:formatDateTime(items('Apply_to_each')?['ExpiryDate'],'yyyy-MM-dd')

Expression:addDays(utcNow(),int(items('Apply_to_each')?['Contract_x0020_Type_x003a_Lead_x']),'yyyy-MM-dd')

vLilyWmsft_2-1624591140656.png

 

vLilyWmsft_3-1624591140660.png

After flow runs, the item named Contrat111 will be updated as below:

vLilyWmsft_4-1624591140662.jpeg

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Users online (3,472)