How to set up DateTime Managed Property in SharePoint Online

How to set up DateTime Managed Property in SharePoint Online


Assuming that your SharePoint custom list metadata is created as Site Columns and there is some content stored in those columns, SharePoint Search will automatically create Managed Properties for those columns and you will be able to use them in your solution without any manual effort. However all the automatically managed properties are added as of type text or boolean which is not always ideal you would like to take the advantage of KQL filtering by date or further refinement.

SharePoint Search is mostly used for SPFx custom development

When developing SPFx webparts I encourage my colleagues to create few site columns, maybe add them to content type and then add the content type to a list so we can take the advantage of the site columns being automatically added to the managed properties and the ability to use SharePoint Search for main data retrieval mechanism to ensure that a client side web part would behave normally no matter of the site collection data source.

More SharePoint Search development because of going flat with SharePoint Site Hubs

The new shift with the SharePoint Online Site Hubs announced by Microsoft on MS Ignite 2017, will bring a new information architecture. Site Collections in a flat structure would be more valued than the traditional tree level architecture with root site and sub sites. This would require even more moving away from Caml queries to SharePoint Search (KQL) queries in custom SharePoint development. SharePoint Search Keyword Query ensures cross site collection data flow something that is not possible with the site collection scoped Caml queries.

The problem, SharePoint Search adds DateTime site column as managed property of type Text

Your custom site column of type DateTime would be added as mapped managed property with type Text. This is a problem, If we would like to take advantage of some KQL date filtering options because the site column is treated as text. If you would like to filter custom date field by date a KQL query should look similar to:


path:https://xx.sharepoint.com/sites/xx/SitePages AND contenttype: "Custom Event" AND CustomEventDateOWSDATE>2018-04-17

However, this is not going to work. Why? Going to the SharePoint Admin Managed Properties reveals that the site column is mapped as of type text.

SharePoint Search KQL to filter CustomDateOWSDATE by date

How to KQL filter by date site column then?

SharePoint came with that interesting concept of default unused managed properties few years ago. There might be a very good limitation reason behind that and why they are doing it that way. We can map our automatically created managed property to a default unused Date or RefinableDate managed property and that way we can take the advantage of filtering by date.

Go to the SharePoint admin panel and select search. In the managed properties search input add RefinableDate or just Date. The default unused managed properties should appear on the screen.

SharePoint Online default unused managed properties

Good idea is to add alias. Should more easily be recognized as used default property and also the alias can be used in the search queries instead of RefinableDate00 for example.

SharePoint Online managed property alias

Add mapping to the custom date time site column.

SharePoint Search map managed property date time

Now after the search index crawls the change, should be possible to filter by date.


path:https://xx.sharepoint.com/sites/xx/SitePages AND contenttype: "Custom Event" AND RefinableDate00>2018-04-17

the better option is to use the alias instead of RefinableDate00 so the developers have idea what is that about


path:https://xx.sharepoint.com/sites/xx/SitePages AND contenttype: "Custom Event" AND VisionEventDate>2018-04-17

More information can be found here Inconvenient Managed Properties and getting a list of all custom Managed Properties in SharePoint 2013 and Mikael’s best practice for managing Managed Properties in SharePoint 2013/SPO–and how to deal with dates

Another option is to use OOTB DateTime site column mapped to managed property

SharePoint Search Out-Of-the-Box site column of type Date can be used instead of custom column to achieve the same result. Here is a list with crawled and managed properties in SharePoint. So instead of creating our own custom site column, we can add already existing one to the content type or list. As example we can select "StartDate" from the list and re-use it. The only problem I see is that I cannot prefix it and I am used to organize the business stuff so I can easly find them i.e. adding prefix, namespace or something common with the business scenario.

Conclusion

Having SharePoint Search default unused managed properties seems as a workaround on limitation or restriction, but it works. We can map multiple site columns to managed property so we have plenty of options. Unfortunately, I am not aware of an automated way to map managed property so any scripting scenarios or automated provisioning are still uncertain and we still have to go to the admin portal and do it the old school way by clicking buttons. In case that process should be automated, we are left with the other option to use OOTB site column mapped to managed property.

Posted on

Tags: SharePoint Online, SharePoint Search, DateTime Managed Property, crawled properties, Office 365 admin, searchable site columns

Comments
Faheem Zafar | 29 Jun 2018

Amazing posts and tricks Velin. I got lot of help around Search and Managed Properties from your articles :)