Using the Content Query Web Part to display documents About to Expire

Hi Everyone!

We're using SharePoint Online and we have retention policies setup by content type which feeds the Expiration Date column for all documents on each site.

I'm looking to provide site users more visibility into which documents are about to expire.  I added a page and then added the "Content Search" web part to that page.  I can't quite seem to figure out just how to change the query to be Expiration Date > Today+15.  In fact, I don't even see the field "Expiration Date" available as a property.

Any and all help is appreciated.  I'm also very much open to other ideas if there is a better way to surface these documents.

Thanks!

September 8th, 2015 1:13pm

Hi,

As I understand, you want to know how to change the query to be Expiration Date > Today+15 using the content query web part in SharePoint online.

You could create the managed property for the expiration date in search section in SharePoint admin center. Then you could change the query in the content query web part.

The article below is about how to create a new managed property in SharePoint online.

https://support.office.com/en-za/article/Manage-the-search-schema-in-SharePoint-Online-d4fab46d-ba41-4c03-9d4c-32b5b33198b6#__toc351360841

Best regards,

Sara Fan

Free Windows Admin Tool Kit Click here and download it now
September 9th, 2015 3:01am

These are the steps you will need to make sure the Content Search WebPart is returning the results you require.

1. Do a Full Crawl to get the "Crawled Property" for your newly added custom column "Expiration Date"

2. Create a "Managed Property" and attach the "Crawled Property" to it. Make sure you tick Searchable, Queryable, Retrievable checkboxes as well as select "Yes - active" for Sortable drop-down.

3. Do another Full Crawl for this Managed Property to be picked up by Search Result Sources.

4. Now go back to your page and add "Content Search" WebPart and edit it.

5. Click on "Change Query" and select "Local SharePoint Results (Systems)" as the result source.

6. Click on "Property filter" and click --Show all managed properties--

7. Next time when you select the drop down for Property filter your new Managed Property will be visible. Select that.

8. Select "Equals" and select "Today's date (-5 days)" from "Select Value" drop down box. Hit "Add property filter".

9. Finally change the "Query text" from {Today-5} to {Today+14} for a displaying documents that will expire in 15 days

Let me know if this doesn't work

September 9th, 2015 3:47am

Thanks so very much!

I got to step 2 and failed!  First I checked to see if the Expiration Date filed is a crawled property and it is ... great!  Then I went to create a Managed Property as you instructed and I got stuck on the Type option.  It is set to TEXT, however I got to thinking that this needs to be a DATE AND TIME type of property in order for the formula to work.  However that field type is not available in the search schema for SharePoint Online.  Looks like they only allow TEXT or YES/NO.  The radio buttons for INTEGER, DECIMAL, DATE AND TIME, DOUBLE PRECISION FLOAT and BINARY are all grayed out.

Please let me know if this is an issue.  If not, then let me know and I'll create it as a TEXT field.

Thanks!

Free Windows Admin Tool Kit Click here and download it now
September 9th, 2015 3:00pm

What if I use one of the generic Managed Properties such as RefinableDate00 - RefinableDate09?  Looks like they are set as DATE AND TIME Type and are already marked as Queryable, Retrievable, and Sortable ... however the checkbox for Searchable is unticked and grayed out. 

September 9th, 2015 3:09pm

To be able to create a new Managed Property from Central Admin, you have to be the Farm Administrator. But I forgot that you are using SP Online. Anyway there is still ways of doing it. Check this link https://samlman.wordpress.com/2015/03/01/creating-managed-properties-in-a-site-collection-in-sharepoint-2013/

You can use the RefinableDate00-09 in your case. Hope this helps

Free Windows Admin Tool Kit Click here and download it now
September 9th, 2015 7:22pm

Correction: As soon as you add a new column in SharePoint 2013 it creates the crawled property which is different than SP2010 where you had to do a full crawl to get it. So, step 1 is not required
September 9th, 2015 7:25pm

Hi,

Have you read the blog in my previous post? In that blog, when you create new managed property, you just have two options of the managed property type text and yes/no. It is by design.

I suppose you could use the generic Managed Properties such as RefinableDate00 - RefinableDate09, add the mapping of the crawled property Expiration Date.

The article below is about managed Properties (Limitation) in SharePoint Online.

https://blog.tallan.com/2015/01/20/managed-property-limitation-in-sharepoint-online/

In SharePoint Online, crawling happens automatically based on the defined crawl schedule. So after you create or change the managed properties, you should wait some time about 15 minutes and you also could re-index the site, the list or library, then you can use the new managed properties in queries, query rules and display templates. For more detailed information, you could refer to the article below.

The article below is about manually request crawling and re-indexing of a site, a library or a list.

https://support.office.com/en-za/article/Manually-request-crawling-and-re-indexing-of-a-site-a-library-or-a-list-9afa977d-39de-4321-b4ca-8c7c7e6d264e?ui=en-US&rs=en-ZA&ad=ZA

Best regards,

Sa

Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 3:02am

Yes, thank you.

The Expiration Date field that I wish to use in the search query is not a field that I created.  It is already part of the system as it is part of the compliance functionality where it populates the Expiration Date field automatically based on the content type indicating when the document will enter a retention stage.  I've set up each document library to display this Expiration Date field so that they users can see when a given document will be removed from the system based on the content type/retention period initially setup.  This is not enough.  Now we need to setup the Content Query Web part on each site that will query and display documents that will be deleted within 15 days.

I've followed all steps provided (mapping the Publishing:ExpirationDate crawled property to the RefinableDate00 managed property) and have adjusted the query for that field but I get now results although I know that there are documents being deleted on the 17th of this month which is less than 15 days from now.  Either I'm not mapping to the right field (Publishing:ExpirationDate) or the search/index of the RefinableDate00 is not working.  I'm not sure where to go from here except to wait over the weekend and see if the search finally picks it up on Monday.

Thanks

September 11th, 2015 8:01am

After further research, the Expiration Date field that I need to map a managed property to may be _dlc_ExpireDate so I mapped this crawled property to RefinableDate02 but still no search results.  Perhaps I should wait until Monday and try it again if it takes time for the search to pick up this information.
Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 12:22pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics