cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leizhaoyn
Level: Powered On

Date comparison condition expression for "30 days before today"

The goal here is to implement something like what's found in the thread below, to automatically delete some files out of a Sharepoint document library when a date field meets a certain condition.

 

https://powerusers.microsoft.com/t5/Using-Flows/Automatically-delete-files-in-Document-Library-after...

 

The flow is set up as such:

Screenshot from 2019-09-16 14-48-52.png

And the condition is using this expression:

greater(utcNow('yyyyMMdd'), formatDateTime(addDays(item()?['Marketing_x0020_ID_x0020__x0028_Lookup_x0029__x003A_Expire_x0020_Date']['Value'], 30), 'yyyyMMdd'))

Where "utcNow('yyyyMMdd')" should be the current date, while the latter half of the formula is grabbing the "Expire Date" field from the document library's files, then adding 30 days to it. My understanding is that this should be triggering for items where the "Expire Date" is older than 30 days ago from now. However, the condition evaluates to false, and I'm not clear why. Can someone provide some pointers here?

1 ACCEPTED SOLUTION

Accepted Solutions
leizhaoyn
Level: Powered On

Re: Date comparison condition expression for "30 days before today"

Thanks, the OData filter query was the way to go here ultimately. Here's how I got this working:

 

1. "Get items" from the desired Sharepoint document library. Importantly, needed to make sure this step had "Pagination" enabled since this document library was large (> 2000 items).

2. "Filter array" on the items returned from Step 1, applied the following OData filter query in advanced mode, applying the conditions of the item being in "Expired" status and with an expiry date more than 30 days in the past.

 

@and(equals(item()?['Marketing_x0020_ID_x0020__x0028_Lookup_x0029__x003A_Current_x0020_Status']?['Value'], 'Expired'), greater(utcNow('yyyy-MM-dd'), formatDateTime(addDays(item()?['Marketing_x0020_ID_x0020__x0028_Lookup_x0029__x003A_Expire_x0020_Date']['Value'], 30), 'yyyy-MM-dd')))

3. "Apply to each" using the "Body" from Step 2 - action is "Delete item" using the "ID" from the filtered array 

View solution in original post

10 REPLIES 10
Super User
Super User

Re: Date comparison condition expression for "30 days before today"

@leizhaoyn 

I haven't tested this, but I suspect that your issue is with your date formatting.  Try 'yyyy-MM-dd' as the format string.

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Dual Super User
Dual Super User

Re: Date comparison condition expression for "30 days before today"

Hi @leizhaoyn 

 

Your formulas seem correct, I believe your issue would be that items are probably not deleting on the 30 day but a day later?

Is that the case?

leizhaoyn
Level: Powered On

Re: Date comparison condition expression for "30 days before today"

OK so I tried updating to use 'yyyy-MM-dd' but the expression in the condition still returns false. There are definitely files in the document library that should meet this condition, so again, I'm not sure why this isn't working.

Super User
Super User

Re: Date comparison condition expression for "30 days before today"

@leizhaoyn 

ry this:

On the left side of the condition, put this via the expression window:  formatDateTime(utcNow(),'yyyy-MM-dd')

On the right side, put this:  addDays(item()?['Marketing_x0020_ID_x0020__x0028_Lookup_x0029__x003A_Expire_x0020_Date']['Value'], 29,'yyyy-MM-dd')

Use greater than for the comparison.

 

Let me know if this works please.

 

 

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
Highlighted
leizhaoyn
Level: Powered On

Re: Date comparison condition expression for "30 days before today"

OK, just tried this, and for some reason, this flow still only evaluates to false and only picks up files that are not meeting this condition. 

Super User
Super User

Re: Date comparison condition expression for "30 days before today"

@leizhaoyn 

Please go to list settings in SharePoint for your list.  Click on the Marketing ID Lookup Expire Date column and provide a screen shot of the column configuration.

Also, can you provide a screen shot of the output from Get files for one of the items retrieved.

On another note, you should probably be using an OData filter for this anyway rather than a condition.  Once you get the syntax worked out, you may want to convert to an OData filter.

 

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott
leizhaoyn
Level: Powered On

Re: Date comparison condition expression for "30 days before today"

Whoops, didn't mean to mark off the accepted solution. I think this column is set up as a Lookup, which may be a problem?

 

Screenshot from 2019-09-16 17-24-10.png

leizhaoyn
Level: Powered On

Re: Date comparison condition expression for "30 days before today"

Here's an example of what's coming out in terms of the Expire Date vs. utcNow() field, as I've grabbed it from a test email where I copy/pasted the expressions used in the condition. This is being output when

 

formatDateTime(utcNow(),'yyyy-MM-dd') greater than 
addDays(item()?['Marketing_x0020_ID_x0020__x0028_Lookup_x0029__x003A_Expire_x0020_Date']['Value'], 29,'yyyy-MM-dd')

is false

 

Now
2019-09-16

Expire Date + 29 days
2020-01-29
 
 
 
Community Support Team
Community Support Team

Re: Date comparison condition expression for "30 days before today"

 

Hi @leizhaoyn ,

 

Please try this configuration. Add the Date field to be compared by Add to time action for 30 days.

Then compare the Date of the current date and out of Add to time action.

Image reference:

187.PNG

Hope it helps.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
leizhaoyn
Level: Powered On

Re: Date comparison condition expression for "30 days before today"

Thanks, the OData filter query was the way to go here ultimately. Here's how I got this working:

 

1. "Get items" from the desired Sharepoint document library. Importantly, needed to make sure this step had "Pagination" enabled since this document library was large (> 2000 items).

2. "Filter array" on the items returned from Step 1, applied the following OData filter query in advanced mode, applying the conditions of the item being in "Expired" status and with an expiry date more than 30 days in the past.

 

@and(equals(item()?['Marketing_x0020_ID_x0020__x0028_Lookup_x0029__x003A_Current_x0020_Status']?['Value'], 'Expired'), greater(utcNow('yyyy-MM-dd'), formatDateTime(addDays(item()?['Marketing_x0020_ID_x0020__x0028_Lookup_x0029__x003A_Expire_x0020_Date']['Value'], 30), 'yyyy-MM-dd')))

3. "Apply to each" using the "Body" from Step 2 - action is "Delete item" using the "ID" from the filtered array 

View solution in original post

Helpful resources

Announcements
firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,012)