0

We have Name field on account which is encrypted. (PersonAccount)

Now as this field is encrypted we cannot fire a Like query as a partial match. So we created a formula field and copy the FirstName and LastNameand fire a query on this formula field.

When I fire a SOQL query SELECT Id, Name_Formula__c FROM Account Where Name_Formula__c LIKE '%test%' it throws an error Name_Formula__c can not be filtered in a query call.

I tried in my local org where Name is not encrypted, there the same filter query on Name_Formula__c works fine.

Is there any limitation from shield that we cannot keep a formula field on where clause as LIKE query when the formula field derives data from an encrypted field?

Improve this question
3
  • developer.salesforce.com/docs/… is what you want. Commented Jun 28, 2023 at 6:23
  • No even deterministric encryption does not work with a partial match. Already tried it. Commented Jun 28, 2023 at 6:35
  • Correct, LIKE or formula won't work. If exact match isn't an option, you'll need to consider building an index based on an algorithm that is your own implementation of "LIKE", then using exact match against that index. A very basic example of such an algorithm is Soundex. Commented Jun 28, 2023 at 6:48

1 Answer 1

Reset to default
1

A shield encrypted field cannot be used in a WHERE clause unless you make it deterministic (as mentioned by identigral). Regardless, they certainly cannot be used with LIKE however they are encrypted.

A formula field does not exist separately in the database, in general terms, with the expression being evaluated instead when used in SOQL to get the value or use it in a WHERE clause. As such your attempt to use a formula in a WHERE clause that itself uses an encrypted field violates the constraints that Shield adds for SOQL and other usages.

The alternative is to switch your query to use SOSL, which will generally function unless your encryption is configured to apply here too. We went through this pain several years back when enabling support for shield encryption on PII data in Contacts for our app exchange package.

You do not get the same behaviour - SOSL will match against not just name but also other fields (e.g. for contact, this matches contact account names too) and you cannot have leading wildcards with SOSL - but it does at least allow something vaguely equivalent.

The alternative is to duplicate the fields in some way, for storage in the DB, but then you are effectively removing the purpose of encrypting in the first place.

Improve this answer
2
  • Ok got it. Its actually mentioned in document. developer.salesforce.com/docs/… Formulas : Fields encrypted with the deterministic encryption scheme can’t be referenced in SOQL WHERE queries. Commented Jun 28, 2023 at 8:47
  • 1
    N.B. at least in V57 (haven't checked V58), Flow Get Records elements can't filter on encrypted fields, even if deterministic Commented Jun 28, 2023 at 23:30

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.