2

I'm trying to SOSL search a custom object we have, by a phone number field but the results are not working as I'd expect. The issue seems to be around missing characters at the start of the search string; but I expect SOSL to search "within" field values to get around this.

The search is simply

FIND {criteria} IN ALL FIELDS RETURNING Object_Name__c(Id, Name, etc, etc)

I can't share a real example because the phone number is a genuine one, but say I have a phone number stored as:

01 395 24 24 68

searching for {1 395 24 24 68} (where the opening number is removed) returns no results, but if I search for {395 24 24 68} (opening two numbers are removed) then the record IS retrieved by the query.

This seems to be something to do with the formatting of the number so to work around this I created a new field and using regex in a trigger, I populate this field with ONLY numbers from the phone number field. In this example, the value would be 01395242468. However this makes NO difference to the SOSL results. I've tried making the new field a Phone, Text and Text (External Id) field. It is on the page layout and is editable by everybody. Obviously if I use SOQL, wrap the search in wildcard symbols and perform a LIKE search, the record is retrieved but I'm confused, why will SOSL not find that record?

NOTE: I've also found out that using Salesforce's own Global Search yields exactly the same problem!

  • Does the field you're trying to search contain a long string not broken up by whitespace (i.e. something like JSON)? If so, then I had a similar question several months ago that might give you some insight: salesforce.stackexchange.com/questions/264916/… – Derek F Sep 23 '19 at 12:43
2

The search indexes require the first character of each "word" (in your case, numbers) in order to make a match. This is a limitation of the search engine's indexes, and you can't work around it aside from making sure you have all permutations you want to have searchable. See FIND {SearchQuery} for more details on wild cards and what partial searches are allowed.

| improve this answer | |
  • I observed many times that SOSL is inconsistent - for example, the exact same scenario (of question) works fine in my dev org - either with full number, removing 0 or 01, having/not having spaces in search. – salesforce-sas Sep 23 '19 at 16:12
  • @salesforce-sas No, it's generally consistent per its documented behavior. If not, there's a bug that should be reported. It's almost always a misunderstanding of how it works. Every time I've come across a question of this nature, SOSL is actually working as documented. – sfdcfox Sep 23 '19 at 18:31
  • 1
    Thanks for the reply @sfdcfox. What you're saying makes sense but I don't think that article is particularly explicit in explaining this (but then, I find Salesforce documentation to be generally lacking in most situations!). Aside from "Searches are evaluated from left to right" it isn't really mentioned that I can see, and even that doesn't really explain itself as meaning that the opening characters have to exactly match. Guess I'll have to resort to doing a SOSL followed by a SOQL search! Thanks – ministe2003 Sep 24 '19 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.