function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
RiverChaserRiverChaser 

Accessing Field History for Contacts and Accounts

I've turned on field history for selected fields in the Account and Contact objects, as well as in a couple of custom objects.

Now I need to access the history from an external application. Custom objects are easy (and slick): just read and process the contents of the object's history table, such as Membership__History.

I can find no such history tables for Account and Contact. Some standard objects have tables with names like CaseHistory, but not Account and Contact. I also don't see where it might be hiding within the objects themselves, but it's certainly possible that I've not used the right tool or query.

How can I get access to Account and Contact history?

Thanks,
Don
Best Answer chosen by Admin (Salesforce Developers) 
KristinFKristinF
I see you're beyond this point, given your additional posts, but want to add that AccountHistory and ContactHistory are available starting with version 11.0 of the API.

All Answers

RiverChaserRiverChaser
Okay, I'm not there yet but I'm a lot further along. The History objects I need are right there in the latest enterprise WSDL files, and I can query them with no problem. I'm currently playing with the AccountHistory table.

Here is the schema:

Code:
<complexType name="AccountHistory">
 <complexContent>
     <extension base="ens:sObject">
  <sequence>
  <element name="Account" nillable="true" minOccurs="0" type="ens:Account"/>
  <element name="AccountId" nillable="true" minOccurs="0" type="tns:ID"/>
  <element name="CreatedBy" nillable="true" minOccurs="0" type="ens:Name"/>
  <element name="CreatedById" nillable="true" minOccurs="0" type="tns:ID"/>
  <element name="CreatedDate" nillable="true" minOccurs="0" type="xsd:dateTime"/>
  <element name="Field" nillable="true" minOccurs="0" type="xsd:string"/>
  <element name="IsDeleted" nillable="true" minOccurs="0" type="xsd:boolean"/>
  <element name="NewValue" nillable="true" minOccurs="0" type="xsd:anyType"/>
  <element name="OldValue" nillable="true" minOccurs="0" type="xsd:anyType"/>
  </sequence>
     </extension>
 </complexContent>
</complexType>

The problem I'm having now is that I'm writing  code like this (C# in a .NET application, not that that is relevant):

Code:
apex.QueryResult qr = null;
qr = binding.query("SELECT AccountId, CreatedBy, CreatedDate, Field, IsDeleted, NewValue, OldValue FROM AccountHistory");
if (qr.size > 0)
{
   for (int i = 0; i < qr.size; i++)
   {
      apex.AccountHistory ah = (apex.AccountHistory)qr.records[i];
 ...

If I include CreatedBy in the field list, I get an error that it doesn't exist in Account History. If I include CreatedBy.Name, I sort of get a result, but it is buried deep in a apex.Name object and I have to extract it from that.

I think I'm in a new area of SOQL here. Is there another syntax I should be using?
 
One thing I learned through this so far is not to trust the tools, since they aren't keeping up with the Apex API. The Apex Explorer doesn't expose the history objects, nor does Developer SideKick. Both tools helpfully tell me that they don't exist.

I'll keep inching along....

Don
RiverChaserRiverChaser
Continuing the conversation with myself (which is what I get for working on a weekend!)....

Below is the code that is working for this. Note this is rough draft code, and I'm not yet filtering for the start and end dates. What's weird about it is that I have to return Account.Name as well as CreatedBy.Name, but in the case of the former I read the Account.Name property of the ah object, but CreatedBy.Name1. I haven't figured out why that is yet.

Code:
public DataSet GetHistory(DateTime start, DateTime end)
{
 DataSet ds = new DataSet();
 DataTable dt = new DataTable("AccountHistory");
 dt.Columns.Add(new DataColumn("Account", System.Type.GetType("System.String")));
 dt.Columns.Add(new DataColumn("CreatedBy", System.Type.GetType("System.String")));
 dt.Columns.Add(new DataColumn("CreatedDate", System.Type.GetType("System.DateTime")));
 dt.Columns.Add(new DataColumn("Field", System.Type.GetType("System.String")));
 dt.Columns.Add(new DataColumn("IsDeleted", System.Type.GetType("System.Boolean")));
 dt.Columns.Add(new DataColumn("NewValue", System.Type.GetType("System.String")));
 dt.Columns.Add(new DataColumn("OldValue", System.Type.GetType("System.String")));
 ds.Tables.Add(dt);

 apex.QueryResult qr = null;
 qr = binding.query("SELECT Account.Name, CreatedBy.Name, CreatedDate, Field, IsDeleted, NewValue, OldValue FROM AccountHistory");
 if (qr.size > 0)
 {
  for (int i = 0; i < qr.size; i++)
  {
   apex.AccountHistory ah = (apex.AccountHistory)qr.records[i];
   DataRow dr = dt.NewRow();

   dr["Account"] = ah.Account.Name;
   dr["CreatedBy"] = ah.CreatedBy.Name1;

   DateTime createdDate = (DateTime)ah.CreatedDate;
   // NOTE! Next line adjusts for Alaska Standard Time. Is there a better way to do
   // this— There doesn't seem to be any timezone information that comes along with
   // dates from SalesForce.
   createdDate = createdDate.AddHours(-9);
   dr["CreatedDate"] = createdDate;
   dr["Field"] = ah.Field;
   dr["IsDeleted"] = ah.IsDeleted;
   dr["NewValue"] = ah.NewValue;
   dr["OldValue"] = ah.OldValue;
   dt.Rows.Add(dr);
  }
 }
 return ds;
}

Right now using a DataSet is just a convenience. I haven't figured out what I'm actually going to return from this function.

Progress, inch by inch!

Don
KristinFKristinF
I see you're beyond this point, given your additional posts, but want to add that AccountHistory and ContactHistory are available starting with version 11.0 of the API.
This was selected as the best answer
RiverChaserRiverChaser
Hi Kristin,

Yep, that's probably why I hadn't noticed them. I only update the WSDL files when I need to.

Thanks,
Don