cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Dual Super User III
Dual Super User III

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

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
Highlighted
Dual Super User III
Dual Super User III

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

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

Highlighted
Regular Visitor

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

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!

Highlighted
Dual Super User III
Dual Super User III

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

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!



Highlighted
Regular Visitor

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

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?

Highlighted
Regular Visitor

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

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!

Highlighted
Dual Super User III
Dual Super User III

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

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!



Highlighted
Regular Visitor

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

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?

Highlighted
Dual Super User III
Dual Super User III

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

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (6,635)