SOQL with FieldHistoryArchive | Field Audit Trail Implementation Guide

The allowed subset of SOQL commands lets you retrieve archived data for finer-grained processing. You can use the WHERE clause to filter the query by specifying comparison expressions for the FieldHistoryType, ParentId, and CreatedDate fields. You must specify the FieldHistoryType first, followed by either ParentId or CreatedDate. You can use =, <, >, <=, or >=, or IN. on the last field in your query. Any prior fields in your query can only use the = operator. The !=, LIKE, NOT IN, EXCLUDES, and INCLUDES operators are not valid in any query.

You can use the LIMIT clause to limit the number of returned results. If you don’t use the LIMIT clause, a maximum of 2,000 results are returned. You can retrieve more batches of results by using queryMore().

Examples: Allowed Queries

1SELECT fieldList 
2FROM FieldHistoryArchive
3[WHERE FieldHistoryType expression [AND ParentId expression[AND CreatedDate expression]] ]
4[LIMIT rows]

Unfiltered

1SELECT ParentId, FieldHistoryType, Field, Id, NewValue, OldValue 
2FROM FieldHistoryArchive

Filtered on FieldHistoryType

1SELECT ParentId, FieldHistoryType, Field, Id, NewValue, OldValue 
2FROM FieldHistoryArchive 
3WHERE FieldHistoryType = ‘Account’

Filtered on FieldHistoryType and ParentId

1SELECT ParentId, FieldHistoryType, Field, Id, NewValue, OldValue 
2FROM FieldHistoryArchive 
3WHERE FieldHistoryType = ‘Account’ AND ParentId=’906F00000008unAIAQ’

Filtered on FieldHistoryType, ParentId, and CreatedDate

1SELECT ParentId, FieldHistoryType, Field, Id, NewValue, OldValue 
2FROM FieldHistoryArchive 
3WHERE FieldHistoryType = ‘Account” AND ParentId=’906F00000008unAIAQ’ AND CreatedDate > LAST_MONTH

Filtered on FieldHistoryType and CreatedDate

1SELECT ParentId, FieldHistoryType, Field, Id, NewValue, OldValue 
2FROM FieldHistoryArchive 
3WHERE FieldHistoryType = ‘Account” AND CreatedDate >= LAST_MONTH

The following table describes the SOQL functions that are available for querying archived fields.

All number fields that are returned from a SOQL query of archived objects are in standard notation, not scientific notation as in the number fields in the entity history of standard objects.

Note

Table 1. SOQL Functions Available for Archived Fields
Functionality	Details
DATE LITERALS	`yesterday`, `last_week`, and so on
LIMIT
WHERE	Filtering only on FieldHistoryType, ParentId, and CreatedDate