SOLVED

Sharepoint Library notify when there are items expiring within the next 7 days

%3CLINGO-SUB%20id%3D%22lingo-sub-2363165%22%20slang%3D%22en-US%22%3ESharepoint%20Library%20notify%20when%20there%20are%20items%20expiring%20within%20the%20next%207%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2363165%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20search%20everywhere%20and%20I%20cannot%20seem%20to%20find%20an%20answer%20to%20my%20question.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20document%20library%20(Not%20a%20list)%20on%20my%20sharepoint%20site%20and%20I'd%20like%20to%20set%20up%20a%20flow%20to%20notify%20me%20if%20there%20are%20items%20Expiring%26nbsp%3B%3CU%3E%3CSTRONG%3Eat%20any%20point%20in%20the%20next%207%20days.%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20got%20the%20flow%20to%20remind%20me%20x%20days%20before%20an%20item%20expires%2C%20but%20I%20want%20to%20extend%20this%20to%20search%20all%20items%20and%20if%20there%20are%20any%20items%20that%20will%20be%20expiring%20in%20the%20next%207%20days(so%20if%20it%20expires%20on%20Days%2C%201%2C2%2C3%2C4%2C5%2C6%20or%207)%20to%20email%20me%20a%20list%20of%20those%20items.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20the%20solution%20will%20be%20very%20simple%2C%20but%20it%20has%20eluded%20me%20so%20far.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20reference%2C%20my%20Document%20library%20has%20a%20column%20called%20(Expiry%20Date)%20and%20using%20a%20list%20to%20store%20the%20documents%20is%20not%20is%20not%20suitable%20for%20our%20use%20case.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20enormously%20appreciated.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2363165%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Flow%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPowerApps%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2364509%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20Library%20notify%20when%20there%20are%20items%20expiring%20within%20the%20next%207%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2364509%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20this%20suggestion%20will%20work%2C%26nbsp%3B%20however%20I%20am%20encountering%20an%20error%20when%20inputting%20my%20details%20into%20the%20filter%20Query%20-%20%22Column%20'NextReviewDate'%20does%20not%20exist.%20It%20may%20have%20been%20deleted%20by%20another%20user...%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20Inserting%20_x0020_%20between%20words%20with%20no%20success.%26nbsp%3B%20Also%20tried%20copying%20the%20column%20title%20exactly%20as%20it%20appears%20in%20column%20editor%2C%20with%20no%20joy%20either.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cant%20check%20if%20everything%20else%20works%20as%20its%20getting%20stuck%20and%20failing%20early%20on.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2364525%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20Library%20notify%20when%20there%20are%20items%20expiring%20within%20the%20next%207%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2364525%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420814%22%20target%3D%22_blank%22%3E%40Tom_By%3C%2FA%3E%26nbsp%3Bgo%20to%20library%20settings%20and%20select%20the%20column.%20Then%20in%20the%20address%20bar%20look%20at%20the%20end%20at%20what%20the%20Field%3D%20is%2C%20that's%20what%20you%20need%20to%20have%20in%20the%20flow.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2363973%22%20slang%3D%22en-US%22%3ERe%3A%20Sharepoint%20Library%20notify%20when%20there%20are%20items%20expiring%20within%20the%20next%207%20days%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2363973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420814%22%20target%3D%22_blank%22%3E%40Tom_By%3C%2FA%3E%26nbsp%3Byes%20you%20can%20do%20this%20using%20an%20array%20variable%20as%20shown%20in%20the%20screenshots%20below.%20In%20my%20Documents%20library%20there%20are%204%20documents%20that%20are%20due%20for%20review%20in%20the%20next%207%20days%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%220-SP-Library.png%22%20style%3D%22width%3A%20638px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281477i4329FA6B2292AF0F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%220-SP-Library.png%22%20alt%3D%220-SP-Library.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20flow%20runs%20at%2013.00%20every%20day%20and%20the%20first%20action%20is%20to%20initialize%20an%20array%20variable.%20When%20doing%20date%20comparisons%20you%20need%20to%20have%20the%20date%20format%20as%20yyyy-MM-dd.%20So%20next%20we%20add%202%20compose%20controls%2C%20one%20for%20today's%20date%20and%20one%20for%20the%20date%207%20days%20ahead%20and%20format%20them%20a%20shown%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%221-Flow.png%22%20style%3D%22width%3A%20637px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281478i97EA812BA2D357A4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%221-Flow.png%22%20alt%3D%221-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20add%20a%20get%20files%20(properties%20only)%20action.%20But%20we%20only%20want%20to%20bring%20those%20items%20back%20from%20the%20library%20where%20the%20NextReview%20date%20is%20greater%20than%20or%20equal%20to%20the%20ComposeToday%20and%20less%20than%20or%20equal%20to%20the%20Compose7Days.%20And%20so%20that%20we%20can%20have%20them%20in%20order%20in%20the%20email%20we%20add%20NextReview%20into%20the%20Order%20By%20field%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%222-Flow.png%22%20style%3D%22width%3A%20630px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281479i43FE325CAAFA2053%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222-Flow.png%22%20alt%3D%222-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20add%20an%20Apply%20to%20each%20and%20select%20value%20from%20the%20dynamic%20content.%20I%20want%20to%20convert%20the%20NextReview%20date%20back%20to%20UK%20format%20but%20you%20might%20not%20need%20these%20next%20steps%3A%26nbsp%3B%20inside%20the%20apply%20to%20each%20add%20a%20compose%20control%20and%20add%20the%20NextReview%20column.%20Then%20add%20another%20compose%20and%20format%20it%20as%20shown.%20Next%20add%20an%20append%20to%20array%20variable%20and%20add%20the%20name%20and%20review%20date%20-%20I've%20used%20the%20output%20from%20the%20ComposeFormatDate%20compose%20for%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%223-Flow.png%22%20style%3D%22width%3A%20639px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281480i64A3E5DC394A1D5A%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%223-Flow.png%22%20alt%3D%223-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20-%20%3CU%3Eoutside%3C%2FU%3E%20the%20apply%20to%20each%20-%20if%20you%20want%20to%20you%20can%20add%20a%20create%20html%20table%20action%20and%20add%20the%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFinally%20send%20the%20email%20and%20use%20the%20output%20from%20the%20create%20html%20table%20action.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%224-Flow.png%22%20style%3D%22width%3A%20650px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281481iD6F2A8686E4E21B2%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%224-Flow.png%22%20alt%3D%224-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20email%20looks%20as%20shown%20below.%20It%20is%20possible%20to%20add%20another%20compose%20action%20to%20add%20formatting%20to%20the%20table%20but%20I've%20not%20done%20that%20on%20this%20occasion.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%225-Email.png%22%20style%3D%22width%3A%20548px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281482i5DE6A776932FA4BC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%225-Email.png%22%20alt%3D%225-Email.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all, 

 

I've search everywhere and I cannot seem to find an answer to my question.  

 

I have a document library (Not a list) on my sharepoint site and I'd like to set up a flow to notify me if there are items Expiring at any point in the next 7 days.

 

I've got the flow to remind me x days before an item expires, but I want to extend this to search all items and if there are any items that will be expiring in the next 7 days(so if it expires on Days, 1,2,3,4,5,6 or 7) to email me a list of those items. 

 

I know the solution will be very simple, but it has eluded me so far.  

 

For reference, my Document library has a column called (Expiry Date) and using a list to store the documents is not is not suitable for our use case. 

 

Any help would be enormously appreciated.  

6 Replies
best response confirmed by Tom_By (Occasional Contributor)
Solution

@Tom_By yes you can do this using an array variable as shown in the screenshots below. In my Documents library there are 4 documents that are due for review in the next 7 days:

0-SP-Library.png

 

The flow runs at 13.00 every day and the first action is to initialize an array variable. When doing date comparisons you need to have the date format as yyyy-MM-dd. So next we add 2 compose controls, one for today's date and one for the date 7 days ahead and format them a shown:

 

1-Flow.png

 

Next, add a get files (properties only) action. But we only want to bring those items back from the library where the NextReview date is greater than or equal to the ComposeToday and less than or equal to the Compose7Days. So in the Filter Query field add NextReview ge '{output of the ComposeToday}' and NextReview le '{output of the Compose7days}'

 

So that we can have them in order in the email we add NextReview into the Order By field:

 

2-Flow.png

 

Next, add an Apply to each and select value from the dynamic content. I want to convert the NextReview date back to UK format but you might not need these next steps:  inside the apply to each add a compose control and add the NextReview column. Then add another compose and format it as shown.

 

Next add an append to array variable and add the name and review date - I've used the output from the ComposeFormatDate compose for this:

 

3-Flow.png

 

Next, - outside the apply to each - if you want to you can add a create html table action and add the array.

 

Finally send the email and use the output from the create html table action.

 

4-Flow.png

 

The email looks as shown below. It is possible to add another compose action to add formatting to the table but I've not done that on this occasion.

 

5-Email.png

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

@RobElliott 

 

I think this suggestion will work,  however I am encountering an error when inputting my details into the filter Query - "Column 'NextReviewDate' does not exist. It may have been deleted by another user..."

 

I've tried Inserting _x0020_ between words with no success.  Also tried copying the column title exactly as it appears in column editor, with no joy either.  

 

I cant check if everything else works as its getting stuck and failing early on.  

 

Any ideas? 

@Tom_By go to library settings and select the column. Then in the address bar look at the end at what the Field= is, that's what you need to have in the flow.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Wow, that was a quick reply.

Sadly this has not worked either. I still get the same behaviour.

You'll need to post screenshots of your flow. However the issue appears to b e with the column name. If you have renamed the column that doesn't rename the internal name. That's why I suggested going to the column settings and copying the internal name of the column after Field=

 

 

I've managed to get this to work now. I deleted the flow and rebuilt it and it works perfectly.

thank you so much for all of your help.