Field History Tracking - Finding value on a particular date?

Question

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);
              }
            }
          }  

Answer 1

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. :)

Answer 2

An alternative is to use either Historical Trend Reporting (which is available for custom objects) or to use Analytic Snapshots

Historical Trend Reporting (HTR)

You have to enable this on the custom object and selected fields are trackable. What HTR does is exploit field history and allow you to construct reports to compare the values of a field at present versus its values at up to five (5) other dates using the report builder..

There are limits to HTR, notably that it only goes back in time 3 months

Analytic Snapshots (Doc)

Here, you run a snapshot of your custom object (you choose the fields) every day with the results saved to a custom object (e.g. Snapshot_Custom_Object__c). From this custom object, you can use Report Builder to see the value of a field on any given day. Even formula fields can be "snapped"

Analytic Snapshots, of course, don't exist until you turn them on so there is no way to get past data without using Data Loader from some known source of truth.

The biggest limitation of Analytic Snapshots is each snapshot run can extract only 2000 records. So, if you have a big org, it may take several snapshot runs to populate a daily snapshot (multiple snapshot executions can populate the same Snapshot_Custom_Object__c). But, if you have tens of thousands of records to snap each day, this approach will fail due to administrative complexity and inability to schedule that many snaps. (As an aside, analytic snapshots frequently are used to snap summary (instead of detail, record-by-record) data each day, thus making them more useful to large orgs).

Answer 3

You can use CreatedDate in the History object of the main object to find out the specific old and new value.

Precondition: The field history should be enabled for that specific field on the object.

Example:

Select Id, Field, OldValue, NewValue, CreatedDate from Candicate__History where Field = 'Prospect_Rating__c' and CreatedDate = TODAY

You can also use as below.

Select Id, Field, OldValue, NewValue, CreatedDate from Candicate__History where Field = 'Prospect_Rating__c' and CreatedDate >= 2016-06-01T00:00:00Z

The date should be formatted in the Salesforce acceptable format.