1

Someone asked me why a SOQL query for a product value on the Case object returned all the records with the product name queried while a SOSL search on the same returned only 1 record.

Example using SOSL:

FIND {GC3060} IN ALL FIELDS RETURNING Case

enter image description here

Example using SOQL:

SELECT ID from CASE where product__c='GC3060'

enter image description here

Normally (without having invested much time in searching and reading documentation), they would have expected the same amount of records from both queries or maybe even more from the SOSL if there had been other fields referencing the product name elsewhere. However, this was not the case.

the SOSL returned 1 record, and the SOQL 10 (for Ex.)

I went through the documentation and other SFSE posts in order to find why:

However, I did not find a complete concrete guide on what is and isn't supported by SOSL (in one place at least). I am also thinking this could benefit the community. Feel free to provide an answer on what limitations there are to SOSL queries on objects and fields which would result in this behavior.

Product__c is a picklist (usually included in a fresh Dev org on the CASE Object) (FYI: the above is easily reproduce-able in a Dev Org using the developer console, you can copy paste the queries separately.)

  • 1
    For completeness, can you please provide a table of data which would make it "easily reproducible in a dev org"? – Adrian Larson Aug 2 '17 at 19:42
  • My first impression is that SOSL is only finding matches on the Case.Subject field and isn't looking in the custom Product__c field at all. Somehow that field isn't being indexed. – Daniel Ballinger Aug 2 '17 at 20:31
  • @Adrian Larson didn't think of including a table of data since these are cases pre populated in a dev org. Not sure if the screenshots suffice. – glls Aug 2 '17 at 21:04
2

It would appear the picklist fields are not searchable with SOSL.

You can confirm this by checking the DescribeFieldResult for the field in question.

Schema.DescribeFieldResult dfr = Schema.SObjectType.Case.fields.Product__c;
System.debug(dfr.isSearchPrefilterable());

What isn't 100% clear is why a field that isn't "prefilterable" in the SOSL where clause would also be excluded from any SOSL indexes.

There is also the associated Idea : Include pick list values in search, which confirms other users finding that picklists are excluded from SOSL results.

See also:

  • Indeed, and I did find that info. However the documentation is a bit scattered and there is no one place where all the limits of sosl are written, I also found some doc on what field types and objects support sosl, could you reference it in your answer. Or I can also add them in another answer post. – glls Aug 2 '17 at 22:53
  • @glls Which document did you find that indicates the supported field types for SOSL? I can link to it. – Daniel Ballinger Aug 2 '17 at 23:08
  • Or is your goal to build up a more definitive resource on what is and isn't supported? Rather than addressing the specific issue you are having with picklists. – Daniel Ballinger Aug 2 '17 at 23:09
  • Searchable Objects and Fields I found this to be most useful – glls Aug 3 '17 at 12:55
  • ^^ there you go @Daniel Ballinger – glls Aug 3 '17 at 17:21

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.