[SalesForce] SOQL – Query for Last Name of Contacts

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

Best Answer

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);
}
Related Topic