"NO" in custom text field returning non-matching results

Neil Davidson

We have a custom, single line text field used to record an identifier. This identifier should always be unique and we have some automation set up which will notify the reporter via email if an identifier is reused. We have found when the identifier starts with "NO", the automation is triggered and an email sent. I tested it out as a search and found it matches similar identifiers starting with other letters.

I get the same result even when no identifiers start with "NO". i.e. while "NO-XX-02" does not exist, it will still match "MJ-XX-02". I do not see the issue when using other letter combinations. So searching for "MJ-XX-01" does not return "NO-XX-01". Tested with a general text search too and see the exact same issue.

Does anyone know why this might be happening? Is NO being treated as an operator in some way? 

Max Foerster - K15t

Hey @Neil Davidson

The search on text fields is sometimes special and requires a bit of a deeper understanding regarding fuzzy searches, searches for exact phrases, stemming, etc. In your case, have you tried searching for the exact phrase?

"Batch Name ~ "\"NO-XX-01\""

There's a lot of material in the documentation explaining how text searches work, so maybe give it a read: Search syntax for text fields.

You can even dig deeper and look at the Apache Lucene Query Parser Syntax. 😄

I can't for sure explain to you why exactly you also get "MJ-XX-01". But I will give you my thoughts about it:

Going through the materials, you will see a few interesting points about the Lucene index. For example, special characters are not stored in the index. It only keeps text and numbers. Let's assume it's saved as "NO XX 01". And now, here's another assumption. The search terms ignore special characters, too, unless you escape them. Your search for "NO-XX-01" might look for a phrase consisting of three separate parts because the search terms ignore special characters. Why doesn't it work the other way around? I have no clue (and no DC instance at hand to dig into this). Long story short, try the exact phrase search and see if it solves your issue... 😄

Best, Max