cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StephaneVedren
Helper I
Helper I

Sharepoint database cleanup base on date difference

Hi,

I am looking to do a sharepoint database cleanup flow.
It would start every month to remove all entries that are more than a year old. I have a date column named "datederetour"

after getting the items, I put a condition action with the following formula
div (sub (ticks (formatDateTime (utcNow (), 'yyyy-MM-dd')), ticks (item ()? ['datederetour'])), 864000000000) is greater than int ('365')

the flow is in failure with a message
"The ticks template language function requires a string parameter that represents a timestamp. The value provided is of type "Null".

I added a formatdatetime before the name of my column without success.
Can you help me?

1 ACCEPTED SOLUTION

Accepted Solutions
ScottShearer
Super User III
Super User III

@StephaneVedren :

 

A better way to approach this would be to retrieve only the items that should be deleted rather than looping through all items and using a condition.  To accomplish this, you would use an OData filter - see my example below.

 

In my Example, I am only retrieving items that are at least 365 days old.  Please note the single quotes before and after my expression.  Also note the Flow uses a columns internal name in OData filters - if you have no spaces in the column name the internal name is probably the same as the display name.

 

Here is the expression that I used:

formatDateTime(addDays(utcNow(),-365),'yyyy-MM-dd')

 

After retrieving the items, just loop through and delete each on - no conditional required.

 

DewleteOldItems1.jpg

 

If this addresses your issue, please mark your post as Solved.

If you like my response, please give it a Thumbs Up.

 

Scott

 

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

Scott

View solution in original post

3 REPLIES 3
ScottShearer
Super User III
Super User III

@StephaneVedren :

 

A better way to approach this would be to retrieve only the items that should be deleted rather than looping through all items and using a condition.  To accomplish this, you would use an OData filter - see my example below.

 

In my Example, I am only retrieving items that are at least 365 days old.  Please note the single quotes before and after my expression.  Also note the Flow uses a columns internal name in OData filters - if you have no spaces in the column name the internal name is probably the same as the display name.

 

Here is the expression that I used:

formatDateTime(addDays(utcNow(),-365),'yyyy-MM-dd')

 

After retrieving the items, just loop through and delete each on - no conditional required.

 

DewleteOldItems1.jpg

 

If this addresses your issue, please mark your post as Solved.

If you like my response, please give it a Thumbs Up.

 

Scott

 

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

Scott

View solution in original post

v-bacao-msft
Community Support
Community Support

Hi @StephaneVedren ,

 

I tested it with the Expression you provided and it is correct.

div(sub(ticks(formatDateTime(utcNow(),'yyyy-MM-dd')),ticks(item()?['datederetour'])),864000000000)

The error you are experiencing is because there is a null value for the specified field.

 

You could use Filter array to exclude items whose specified fields are empty.

The Expression in Condition does not need to be changed. You need to replace the traversed value with Body of Filter array and Item Id is obtained using Expression.

item()['ID']

Image reference:

20.PNG

 

Please take a try.

 

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.

@ScottShearer 

Thanks for your help,

After a few tries your solution proves to be perfectly effective ...

I just have to see my column names with spaces that did not work very well.

 

Stephane

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (97,689)