1

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);
              }
            }
          }  
2
  • 1
    an alternative is daily analytic snapshots or historical trend reporting
    – cropredy
    Jun 2, 2016 at 0:29
  • @cropredy -Can you please elaborate on that ? Are you referring to custom reports? Jun 3, 2016 at 12:30

3 Answers 3

4

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

5
  • Thanks Adrian . A Question here - If the picklist value has never been changed , it will not reflect in the query results , right ? I could see blank columns for such results. Jun 1, 2016 at 20:23
  • 1
    That's the reason for the isEmpty check on the penultimate line. @Vanilla
    – Adrian Larson
    Jun 1, 2016 at 20:36
  • I have updated the question with a modified code according to your answer . Can you please take a look and provide input? Jun 2, 2016 at 13:38
  • @Vanilla_Sky Your addition is very difficult to understand, and also doesn't really look very much like this solution at all.
    – Adrian Larson
    Jun 2, 2016 at 15:34
  • In the list , I have collected the last value of the Status field of Role object . Then I would like to iterate through that list and check if any value in 'pc' list is not equal to lapsed . if yes , then add to another list . I was not sure about the syntax. Jun 3, 2016 at 5:40
1

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

0

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.

2
  • But CreatedDate >= 2016-06-01T00:00:00Z may give give a whole lot of query results . Isn't it? My requirement is to find out what was the value on a particular date . That date may not be the day a value was changed. Specifically i would need to know the value on last day of every quarter /month. Jun 1, 2016 at 19:53
  • There are lot of action that you need to consider. What if the same field is updated multiple times in a single day? What if the field is not updated in that day? It all depends on your business case.
    – Bugude
    Jun 1, 2016 at 20:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .