function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Miguel Nuñez Díaz-MontesMiguel 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.

filter by standard field

filter by custom field

Any idea why this is happening?

Kind Regards




 
Best Answer chosen by Miguel Nuñez Díaz-Montes
monsterloomismonsterloomis
Hi there,

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.  
Date d = Date.today();
for (Contact con: [
        SELECT Id, Name
        FROM Contact
        WHERE CALENDAR_MONTH(Birthdate__c) = :d.month()
        ORDER BY Name
        LIMIT 10
        ]) {
    System.debug(con);
}
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

monsterloomismonsterloomis
Hi there,

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.  
Date d = Date.today();
for (Contact con: [
        SELECT Id, Name
        FROM Contact
        WHERE CALENDAR_MONTH(Birthdate__c) = :d.month()
        ORDER BY Name
        LIMIT 10
        ]) {
    System.debug(con);
}
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
This was selected as the best answer
Miguel Nuñez Díaz-MontesMiguel Nuñez Díaz-Montes
Hi!

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!