0

I am trying to query for Contacts by Last Name. I understand the error I am receiving, but I am unsure of the cause.

I think one of the following might be the potential root causes:

  • Name is of the data type "Name".
  • Platform encryption is on.

What are my options? As a workaround, currently I am using SOSL to search in Name Fields, then parse through that return of contacts to pull out the contacts where the LastName of that contact is == the search string, but this is taking too long to parse through all of the returns I get for common names because it is searching in ALL Name fields.

If this is not a bug or an oversight, I'm thinking of using a formula text field to get the LastName so I am able to SOQL directly for that field.

The following query returns the following error:

SELECT Id, LastName from Contact where LastName = 'Smith'

field 'LastName' can not be filtered in a query call

The following query does not return an error:

SELECT Id, LastName from Contact
1

If you have Shield Platform Encryption turned on in your org and have that field encrypted you will get an error message; not sure if it's exactly that one though. See the SOQL/SOSL section of General Shield Platform Encryption Considerations.

You would get that message for a custom long text area field.

PS

So it sounds like it is the encryption. First thing to read is this ISVPlatformEncryption document that includes sections like "Key limitations and their workarounds". The code example they supply for a similar situation to yours is this combination of dynamic SOQL and using a SOSL find:

List<Contact> conts;
If (Schema.sObjectType.Contact.fields.MailingCity.isFilterable() ){
      //Filterable, so use SOQL
      conts = Database.query('Select ID, Name, MailingCity from Contact WHERE MailingCity = \'PARIS\'');

} else {
      //Not filterable, so use SOSL
      List<List<SObject>> searchList = [FIND 'PARIS' IN ALL Fields RETURNING Contact (Id, Name, MailingCity)];
      conts = searchList.get(0);
}
  • "Encrypted fields that use the probabilistic encryption scheme can’t be used with the following SOQL and SOSL clauses and functions: --WHERE clause" Yep. So now we have a cause... are there any other solutions to make this search more efficient? – S.B. Jun 22 '18 at 16:04
  • Currently taking a look into deterministic encryption... a setting in Salesforce that allows queries on specific fields set on the on platform encryption policy page. Will post results... – S.B. Jun 22 '18 at 16:07
  • @S.B. See my PS. Not pretty but we've had to do it and it works. That was a couple of years ago though so there may be other options now. – Keith C Jun 22 '18 at 17:11

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.