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

| improve this answer | |
  • 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. – Vanilla_Sky Jun 1 '16 at 20:23
  • 1
    That's the reason for the isEmpty check on the penultimate line. @Vanilla – Adrian Larson Jun 1 '16 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? – Vanilla_Sky Jun 2 '16 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 '16 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. – Vanilla_Sky Jun 3 '16 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).

| improve this answer | |
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.

| improve this answer | |
  • 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. – Vanilla_Sky Jun 1 '16 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 '16 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.