You need to sign in to do that
Don't have an account?
Miguel Nuñez Díaz-Montes
Filter by Date not working on Custom Field
Hi,
I'm currently implementing Shield and due to Birthdate is not supported for encryption I want to move that data into a Custom Field and encrypt that.
I was testing in a developer org but I'm having trouble because this field is used in filters in ListViews using Context Variables (f.e. Date = THIS_MONTH). And when I querry with the Standard Birthdate field I get results, but, if I try with the CustomField I cant get any result.
Any idea why this is happening?
Kind Regards
I'm currently implementing Shield and due to Birthdate is not supported for encryption I want to move that data into a Custom Field and encrypt that.
I was testing in a developer org but I'm having trouble because this field is used in filters in ListViews using Context Variables (f.e. Date = THIS_MONTH). And when I querry with the Standard Birthdate field I get results, but, if I try with the CustomField I cant get any result.
Any idea why this is happening?
Kind Regards
No idea exactly why that's happening, because I don't see any references to date literals not being supported with custom date fields. I did confirm the results of your test, and then looked for a workaround. I tried doing various >, >=, and other operators on both literals and constructed dates, and it stubbornly refused to filter on the custom date field as you would expect. I did find one thing that worked, though you'll have to evaluate whether it will work for you. If you make a Birthdate__c field of type datetime (instead of date), then using a date FUNCTION instead of a literal. The larger issue you're going to hit, though, is that even with deterministic encryption, you can't filter by date in a query. Once your new field has been encrypted, it will bark at you that it can't be used in a where clause. Strings can be used in query where clauses when encrypted with deterministic encryption, but that won't help you with dates/datetimes. You might be able to use something to store dates as a string in a way that would let you use them programmatically, but your end users using list views will still have problems with the solution. Another thing to consider would be a formula field that abstracts this complexity for your users, as they will be forced to update their list views to point to a new field either way. Performance might take a hit, because formula fields are evaluated at runtime, but you'll have to decide if the tradeoff is worth it based on the specifics of your org/use cases.
Wish I had a better answer for you, but it may boil down to educating your end users on the tradeoffs of encryption and the limitations of deterministic encryption:
https://developer.salesforce.com/docs/atlas.en-us.securityImplGuide.meta/securityImplGuide/security_pe_deterministic_considerations.htm
https://developer.salesforce.com/docs/atlas.en-us.securityImplGuide.meta/securityImplGuide/security_pe_considerations_general.htm
All Answers
No idea exactly why that's happening, because I don't see any references to date literals not being supported with custom date fields. I did confirm the results of your test, and then looked for a workaround. I tried doing various >, >=, and other operators on both literals and constructed dates, and it stubbornly refused to filter on the custom date field as you would expect. I did find one thing that worked, though you'll have to evaluate whether it will work for you. If you make a Birthdate__c field of type datetime (instead of date), then using a date FUNCTION instead of a literal. The larger issue you're going to hit, though, is that even with deterministic encryption, you can't filter by date in a query. Once your new field has been encrypted, it will bark at you that it can't be used in a where clause. Strings can be used in query where clauses when encrypted with deterministic encryption, but that won't help you with dates/datetimes. You might be able to use something to store dates as a string in a way that would let you use them programmatically, but your end users using list views will still have problems with the solution. Another thing to consider would be a formula field that abstracts this complexity for your users, as they will be forced to update their list views to point to a new field either way. Performance might take a hit, because formula fields are evaluated at runtime, but you'll have to decide if the tradeoff is worth it based on the specifics of your org/use cases.
Wish I had a better answer for you, but it may boil down to educating your end users on the tradeoffs of encryption and the limitations of deterministic encryption:
https://developer.salesforce.com/docs/atlas.en-us.securityImplGuide.meta/securityImplGuide/security_pe_deterministic_considerations.htm
https://developer.salesforce.com/docs/atlas.en-us.securityImplGuide.meta/securityImplGuide/security_pe_considerations_general.htm
A huge thank you for answering. Finnally I think that we will have to change that filter.
Even though, its strange that that filter does not work, I will use your solution as a workaround for future cases
Thank you again!