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.

RefinableInt00>100

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

00000101

How time flies! I have now been 5 years at Microsoft. I started as a Program Manager, which changed into Product Manager, and now I’m an Engineer.

Despite the title changes the work I do has not changed much over these five years. I have always been a jack of many trades, and I enjoying doing many things at once. When everything is interesting and challenging, it is hard to cut something out. And I have been lucky to be allowed to work to my strengths while still learning – and I love to learn!

What has changed over the years is that I have learned to appreciate process and governance more than before. Which is why I have onboarded to internal roles as privacy champ, compliance champ and responsible AI champ – the last one becoming increasingly important as Microsoft focus all energy on Copilot. Luckily for me loving search as a function and search tech, is that Microsoft Search plays an important role also in Copilot.

What the next one or five years will bring I have no idea, and it’s not important. I’m still a day to day kind of person, with no long term plans. If what I do is fun, challenging and benefits Microsoft customers, I will keep doing it. And for those who know me, I’m not giving up om my long term mission to cut ties from classic SharePoint search features still having dependencies in the modern world of Microsoft Search. Some cuts are hard but still important to ensure the success of future experiences.

Cake time!