[SalesForce] Field History Tracking – Finding value on a particular date

I am querying the Newvalue/Createddate from the History object in Field tracking.

   SELECT ID, (SELECT Id, Field, OldValue, NewValue 
              FROM Histories where field ='Status' 
               AND Createddate > LAST_QUARTER
               AND Field = 'CustomField__c'
               AND NewValue = 'SOMETHING')  
FROM CustomObject__c

Requirement : Need to find out what was the value on a particular date.
e.g What was the value on 30th June 2015 or check whather value!='XYZ' on 30th June 2015.

I tried with createddated , but it didn't help the requirement.

Is this feasible?

Update :
Here Role is a related object to Account . Hence , multiple records will be there . So fetching all the latest values from each record. And need to check if any values is NOT Lapsed , then add to a List . Please help me out in the synatx/code.

List<Role__c> pc = [SELECT Status__c , ( SELECT NewValue , createddate FROM Histories WHERE Field ='Status' ORDER BY CreatedDate DESC LIMIT 1) FROM Role__c where Accountid= :AccountList];

    List<String> ActiveRole = new List<String>();
            for (Role__c pl : pc) {
                if(pc.Histories.isEmpty())
                {
                 if(pl.status__!='Lapsed')
                 {
                      ActiveRole.add(pl.status__c);
                 }
                 else 
                 {
                 if(pl.Histories[0].NewValue!='Lapsed')
                 {


                 ActiveRole.add(pl.Histories[0].NewValue);
              }
            }
          }

Best Answer

You need the most recent history before or on the day you wish to examine. If none are found, you should fall back to the current value. You will need Apex post-processing something like:

public static String getStatusOn(Date day, Id recordId)
{
    Datetime endOfDay = Datetime.newInstanceGmt(day, Time.newInstance(23, 59, 59, 999));
    MyObject__c record = [
        SELECT Status, ( 
            SELECT NewValue FROM Histories
            WHERE Field ='Status' AND CreatedDate <= :endOfDay
            ORDER BY CreatedDate DESC LIMIT 1)
        FROM MyObject__c WHERE Id = :recordId
    ];
    if (record.Histories.isEmpty()) return record.Status;
    return (String)record.Histories[0].NewValue;
}

Bulkification is left as an exercise for the reader. :)

Related Topic