2

I am running a SOSL with a query similar to the one below:

FIND {joh* OR smith*} IN ALL FIELDS RETURNING 
Account(Firstname,Lastname WHERE Firstname LIKE 'joh%' AND Lastname LIKE 'smith%'),
Contact(Firstname,Lastname WHERE Firstname LIKE 'joh%' AND Lastname LIKE 'smith%')

I have records in Account:

Account Firstname: John Lastname: Smithers

Contact Firstname: John Lastname: Smithers

I expect to find both records using the wildcard query as entered.

However, when I run the above query, the Contact result is returned, but not the Account record?

However, if I run the SAME query with a complete lastname string value matching:

Case 1:

FIND {joh* OR smithers*} IN ALL FIELDS RETURNING 
Account(Firstname,Lastname WHERE Firstname LIKE 'joh%' AND Lastname LIKE 'smith%'),
Contact(Firstname,Lastname WHERE Firstname LIKE 'joh%' AND Lastname LIKE 'smith%')

I get both expected records

Case 2: Likewise, if I run it with one or the other

FIND {smithers*} IN ALL FIELDS RETURNING 
Account(Firstname,Lastname WHERE Firstname LIKE 'joh%' AND Lastname LIKE 'smith%'),
Contact(Firstname,Lastname WHERE Firstname LIKE 'joh%' AND Lastname LIKE 'smith%')

I get both expected records

Case 3: And if I search just account with two wildcart partials,

FIND {joh* OR smith*} IN ALL FIELDS RETURNING 
Account(Firstname,Lastname WHERE Firstname LIKE 'joh%' AND Lastname LIKE 'smith%')

I get the expected account

Am I misunderstanding something about wildcard search here?

  • I was able to get the correct results for your first mentioned SOSL in a scratch org (both thru Developer Console 'Execute Anonymous' window & 'Query Editor' tab). I mean the following SOSL: FIND {joh* OR smith*} IN ALL FIELDS RETURNING Account(Firstname,Lastname WHERE Firstname LIKE 'joh%' AND Lastname LIKE 'smith%'), Contact(Firstname,Lastname WHERE Firstname LIKE 'joh%' AND Lastname LIKE 'smith%'). You mentioned that you are running a query similar to this one? – arut May 7 at 19:59
  • I have to ask the question as to why you are using SOSL like this? Are you doing it this way just to make it so you use a single query to query against two types of object? Remember that SOSL indexes take time to update, so it is just possible you got the wrong results purely because the updates to the Account instance had not propagated into the SOSL index. What happens if you try this again now? – Phil W May 7 at 20:56
  • How many Accounts and Contacts meet the individual criteria of FirstName:'joh%' or LastName:'smith%' in the entire org? My understanding is that the SOQL Find first occurs over all the Accounts and Contacts that contain joh or smith IN ALL FIELDS. If there are more than 2000 results then the subsequent RETURNING criteria won't see all the possible records. – Daniel Ballinger May 8 at 2:47
  • @PhilW it's an indexing issue with the Sandbox - I was not specifically using joh* or smith* in the real example it was a real person whose name is unique amongst a few million records. A number of weeks have passed since the sandbox was refreshed or the record updated. @Daniel Ballinger - there are fewer then 2000 results, and my real clause had limit 20 on it. – jordan.baucke May 8 at 20:19

Your Answer

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

Browse other questions tagged or ask your own question.