Answered

Date comparison condition expression for "30 days before today"

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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-X-days/td-p/70009'

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?

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (1)

Sort by

ScottShearer 25,290 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

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

Was this reply helpful? Yes No
RezaDorrani 12,145 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous

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?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
ScottShearer 25,290 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
ScottShearer 25,290 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

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.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
v-bacao-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous ,

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:

Hope it helps.

Best Regards,

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No