Friday, 15 April 2011

Tricks : Comparing two fields in a SOQL query

Sorry for being Inactive from a long :).
Actually these days I am looking for that what is not possible in salesforce and what can be the work around for that.

So be ready I am going to post some interesting  things in coming days :).

Are you ready to get the first one, so let's start

I have an object named "Filed Comparision" with fields "Start Date" and "End Date".
Now picture starts from here :
My requirement is to get all the records from the "Field Comparison" having End Date greater then Start Date.

So my query should be something like :

List<Field_Comparison__c> listResults = [Select Name from Field_Comparison__c where End_date__c > Start_Date__c];

but unfortunately, due to some salesforce restriction we can not do field comparison in where clause.This throws an exception "Compile Error: line 1, column 98: unexpected token: 'Start_Date__c'".

Then what should be done? do we need to fetch all the records and do the coding stuffs for getting the results?
Absolutely not.

We can create a formula field with criteria :
IF(End_Date__c > Start_Date__c , 'true', 'false') 

Guess, Formula field name is "Is Active". After introducing this field, records will be looks like :

I hope , now you got the idea what I am going to do to get the valid records only :D.

So, to get all the valid records SOQL query can be :

 List<Field_Comparison__c> listResults = [Select Name from Field_Comparison__c where Is_Active__c = 'true'];

Ohh..........It's completed. forgive me for getting you bored :P.

Please comments for making it for meaningful and more helpful.


  1. I'm still shocked SOQL doesn't support field-to-field comparisons. Thanks for the good hack.

  2. your trick helped me. Genius !

  3. Replies
    1. we have a scenario where we are going to compare two profiles nad give error message. Eg: If role1 and role2 is present in two different fields (like role and delegate role) then error message display. Please help on this asap.

  4. Thanks a lots sir, really helpful...

  5. This comment has been removed by the author.

  6. This blog is really remarkable. Thanks for sharing this great stuff. Keep sharing more useful and conspicuous stuff like this. Thank you so much.
    compare currency

  7. This is really helpful. Thanks sir. You are rock...