SOQL Field History Query

Understanding SOQL Field History Query

Salesforce Object Query Language (SOQL) is used to query data from Salesforce. An important use case is tracking changes in field values over time. Field History Tracking in Salesforce allows you to understand how field values change, such as tracking changes in the Opportunity Amount.

This post will guide you in crafting SOQL queries to access field history data, focusing on tracking changes in Opportunity Amount.

Enabling Field History Tracking

Before you can query field history, you must enable Field History Tracking for the fields you're interested in. For Opportunities, navigate to the Setup menu, select Object Manager, choose Opportunity, and then go to Fields & Relationships. Click on Set Field History Tracking and select the fields you wish to track, such as Amount.

Querying Opportunity Field History

Field history data is stored in a special history table automatically created by Salesforce for each object with history tracking enabled. For Opportunities, this table is called OpportunityFieldHistory. Below is a basic query to retrieve changes in the Opportunity Amount field.

SELECT OldValue, NewValue, CreatedBy.Name, CreatedDate FROM OpportunityFieldHistory WHERE Field = 'Amount' AND OpportunityId = 'INSERT_OPPORTUNITY_ID_HERE'

Understanding the Query Results

The above query selects the OldValue and NewValue of the Amount field, along with the CreatedBy (the user who made the change) and CreatedDate (when the change was made). By filtering with a specific OpportunityId, you can focus on the history of a single opportunity.

Filtering Results by Date

To narrow down history records to a particular time frame, you can add date filters. This is useful for tracking changes over specific periods.

SELECT OldValue, NewValue, CreatedBy.Name, CreatedDate FROM OpportunityFieldHistory WHERE Field = 'Amount' AND OpportunityId = 'INSERT_OPPORTUNITY_ID_HERE' AND CreatedDate = LAST_N_DAYS:30

Advanced Filtering with SOQL

Beyond simple date filtering, SOQL supports a range of operators and functions to refine your queries. For example, you can use logical operators to combine conditions or aggregate functions to summarize data.

SELECT COUNT(Id) FROM OpportunityFieldHistory WHERE Field = 'Amount' AND CreatedDate >= LAST_N_DAYS:90

This query counts the number of changes made to the Amount field in the last 90 days, providing a quick overview of activity frequency.