Is there a viable workaround to allow reporting on the history of a child in a master-detail relationship?

Question

I am already using a workaround to allow reporting on approval process steps (field updates to the record, track the history of the field), except that now I can't report on the field history that is being captured. Does anyone know of a workaround that would allow me to report on this tracked data?

Thanks!

EDIT: One thought I had was to create date fields for each step in the process and have the approval step update the relevant date field with NOW().

Answer 1

I think the best way for now would be to create a custom object which has the following fields:

Object Lookup -> Child__c
Object Lookup -> Parent__c
Field (same type as field to be tracked) -> NewValue__c
Field (same type as field to be tracked) -> OldValue__c

Then use a trigger on the child object to create a new record each time the field in question is updated (pseudocode):

trigger ChildObject_AfterUpdate on ChildObject(after update)
{
  list<FieldHistory__c> changes = new List<FieldHistory__c>(); 

  for(ChildObject o : trigger.new)
  {
    if(o.TheField != trigger.oldMap(o.Id).TheField)
    {
      FieldHistory__c  fh = new FieldHistory__c();
      fh.Parent__c = o.ParentId;
      fh.Child__c = o.Id;
      fh.OldValue__c = trigger.oldMap(o.Id).TheField;
      fh.NewValue__c = trigger.TheField;
      changes.add(fh);
    }
  }
  update changes;
}

Note that I didn't include a date field because we've already got that thanks to the system field CreatedDate.

Answer 2

The method I ended up using, which is working well so far, was to:

• Create date/time fields for each step in the process
• Have a field update on each step of the approval process update the date/time field with the formula NOW()
• Then have a formula field take the difference between the date/time field for step 2 and the date/time field for step 1 to see how long the step took to complete

This was relatively easy to set up (no coding) and easy to report on, so I would recommend it as a viable workaround for anyone stuck in a similar situation.