cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Notification of documents older than X years - need help building Flow

Hi friends,

 

I am new to Power Automate and need help building a flow that can notify me of documents (e.g., provide me with a list of documents in a Document Library) that are older than 2 years based on a custom date and time column I've created called "Last Reviewed". In this column, I only have dates entered, not times. I am finding flows that automatically delete/archive based on a time frame, but I want to review old documents, not delete them. Any help is appreciated! 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Hi!

Did you consider using 'Get files (properties only)' and an ODATA filter expression assigned to its Filter Query input?

Assuming your column internal name is Last_x0020_Reviewed I would try with the following:

 

-Add a 'Get past time' action block, assign as its value the expression utcNow, configure it to substract 24 months

-Add a 'Get files (properties only)' with the following ODATA filter expression assigned to Filter query input

Last_x0020_Reviewed lt '@{formatDateTime(body('Get_past_time'),'yyyy-MM-dd')}'

 

Great stuff here

https://baddaz.com/filtering-a-list-of-records-in-flow-based-on-todays-date-5a80292b366d

https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/

 

Hope this helps

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

8 REPLIES 8
Super User III
Super User III

Hi!

Did you consider using 'Get files (properties only)' and an ODATA filter expression assigned to its Filter Query input?

Assuming your column internal name is Last_x0020_Reviewed I would try with the following:

 

-Add a 'Get past time' action block, assign as its value the expression utcNow, configure it to substract 24 months

-Add a 'Get files (properties only)' with the following ODATA filter expression assigned to Filter query input

Last_x0020_Reviewed lt '@{formatDateTime(body('Get_past_time'),'yyyy-MM-dd')}'

 

Great stuff here

https://baddaz.com/filtering-a-list-of-records-in-flow-based-on-todays-date-5a80292b366d

https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/

 

Hope this helps

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Hi! Appreciate the help, but I'm still stuck... 😞

When I ran the Flow, I received the following error:

yinanrx_0-1601394822170.png

And just for completeness sake, here is a screenshot of my Document Library:

yinanrx_3-1601393299418.png

Thanks again for your help!

Hi!

Looks like single quotes are missing, I just highlighted them in red

Last_x0020_Reviewed lt '@{formatDateTime(body('Get_past_time'),'yyyy-MM-dd')}'

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



When I entered the expression that way, I receive the "expression is invalid" error... The only way it would accept it is this:

formatDateTime(body('Get_past_time'),'yyyy-MM-dd')

 

What am I doing wrong here?

UPDATE: I got it to work this way (no @)...

yinanrx_1-1601404315937.png

But when I run the Flow, it's saying that it can't find my "Last Reviewed" column. It works if I used the "Modified" column instead. 

At this point, it returns the output in syntax, I assume? I get this when I click on Outputs (click to download):

yinanrx_2-1601404661155.png

 

I assume I have to format this using more action blocks? Oof, I think I'm in over my head!

Hi!

It seems your Sharepoint Internal column name is not 'Last_x0020_Reviewed' but something else.

Two good tricks to figure out Sharepoint internal column name in this thread:

https://powerusers.microsoft.com/t5/Building-Flows/Sort-in-GET-ITEMS-by-column-with-space/td-p/69396...

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



That's great - it did help me figure out the actual name of the column - thanks!

Could you also point me to where I can convert the output to a readable file? Like I said previously, the output appears to be just code. How can I convert it to a list (e.g., spreadsheet) to be emailed to me as an attachment every time this Flow runs?

Hi!

So the issue originally reported is finally solved, right? If so,

Hurray! Great Job!

Now on your new challenge. Next steps? My humble suggestion:

1.-Mark this topic as "Solved" by clicking "Accept as a solution". THis way others facing a similar problem can find a solution faster. Please also note you can even select several responses as solution, even from different community members!

Flow_AcceptSolution.png

2.- Open a new thread for your new question, following the policy: 'One Question, one post'. This way you have more chances to attrack attention for superusers that are seeking for unanswered topics.

3.- (OPTIONAL) Click on 'Thumbs up' in any answer you found valuable... or even in all of them 😉

Flow_thumbsUp.png

Even though this last step is OPTIONAL, please remember it is the cheapest, easiest way to say thanks to somebody that spent its time simply trying to help, specially on weekends. And just between you and me, everytime an inspiring answer receive a kudo in this community, an ewok escapes from The Emperor menace 

 

Thanx for your help making this community great!

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (108,787)