Tuesday, June 25, 2024

Search nugget: The beauty of SharePoint Site Columns


The scenario I’m sharing is not a new one, but (somewhat) recent changes to SharePoint indexing of items may make the scenario easier to solve.

Before we dig into the details, let's talk about Microsoft Syntex, as it has bearing on the nugget I will share. Microsoft Syntex is a service that uses AI and machine learning to automatically sort and classify documents across SharePoint, Microsoft Teams, OneDrive for Business, and Exchange.

Or simply put in terms for SharePoint: Pulling structured data out from unstructured data and store it as metadata for easier use and retrieval. Something we have done manually or via customized processed for the longest time in SharePoint lists and libraries.

Microsoft Syntex is licensed via a pay-as-uou go model, where you pay for items processed. See https://learn.microsoft.com/en-us/microsoft-365/syntex/syntex-pay-as-you-go-services and  for more details and the offering currently includes these features:

  • Prebuilt document processing
  • Structured and freeform document processing
  • Unstructured document processing
  • Autofill columns
  • Content assembly
  • Image tagging
  • Taxonomy tagging
  • SharePoint eSignature
  • Document translation
  • Optical character recognition (OCR)
  • Microsoft 365 Archive
  • Microsoft 365 Backup (Preview)
  • Microsoft 365 Backup Storage (Preview)
A Microsoft Syntex enabled library also enable a better context query user experience. See https://learn.microsoft.com/en-us/microsoft-365/syntex/metadata-search for details.

Let's get to it!

Say you have a library where you add a new column. In this case a number column.


In a search scenario for the library you want to filter results based on the number value. One example being all items with a value above 100.

In my case the column has an internal name of PnPNumberColumn. If the column was manually created at the library it would output a crawled property named ows_PnPNumberColumn. This crawled property could be mapped to RefinableInt00 and after re-indexing you could use the below KQL query to list all items with a value above 100. If you add an alias to the managed property you could use this instead.


Now, let us go back to the site column scenario. Site columns will in addition to the default crawled property, create an additional crawled property and an automatic managed property. This is described at Automatically created managed properties in SharePoint Server which also apply to SharePoint Online despite what the article is tagged as.

The caveat of the automatically created managed properties is that they are of type text, which is not very useful when it comes to numbers. The example column above yields an automatic managed property named PnPNumberColumnOWSNMBR. This means I can query with:

PnPNumberColumnOWSNMBR:500 or PnPNumberColumnOWSNMBR=500 and get a match, as it matches on text. PnPNumberColumnOWSNMBR>500 would however be invalid KQL.

And here is the nugget. In addition to PnPNumberColumnOWSNMBR we now also get an additional property PnPNumberColumnOWSTNMBR. Note the extra T.

A word from our sponsor: This tip was brought to you by indexing advancements made for Microsoft Syntex. Microsoft Syntex greatly automates creation of site column for the average user. What are you waiting for?

And don’t be fooled by the schema admin page saying the property is of type text, as it is indeed of type number. A fact somewhat called out in a notice when you view the property settings, so you just have to trust me.



Below is screenshot showcasing the behavior where the query PnPNumberColumnOWSTNMBR>499 return the sample document. Easy as pie!


PS! The property is not refinable so you can not use it as refinement filter. If this is your case keep on as you always have and map to e.g RefinableInt00