Universal Search on Small Words
We recently had an issue where searching in the universal search for a company name that included "IT" in the end would return no results. After doing some digging, we discovered that IT is a stop word for full text searching in SQL and doesn't get indexed. That caused problems because SQL would return zero results and therefore Acumatica would return zero results as well. We ended up having to the follow the 3 steps below to remove "it" from the SQL stop list. This does require access to SQL. Hope this helps anyone else with similar issues.
- Added our own stoplist (Inside db instance, go to storage>>Full text stop lists -- right click to create a new one - we did it based on system )
- Removed "it" from stop list (right click on new stop list, go to properties - under actions select delete a stop word, enter stop word, click on OK)
- Assigned new stop list (right click on SearchIndex table, go to Full Text Index>>Properties -- In the middle you see see stop list and can use drop down)
Thanks so much for sharing this Ryan. I've wanted to learn more about how SQL Full Text search works so this is really helpful.
Google search makes sense to me, but SQL Full Text search is still a bit of a mystery to me.
Another thought, I wonder if Acumatica SaaS allows you to tweak these options or if it's "what you see is what you get".
Interested in joining a Local Acumatica User Group? Click here for more info