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.