Filter Field History Reports by Old and New Values
Where: This change applies to Lightning Experience and Salesforce Classic in Essentials, Group, Professional, Enterprise, Performance, Unlimited, and Developer editions.
Who: To add or edit a filter to a field history report, you need one of these permissions: Create and Customize Reports, Report Builder, or Report Builder (Lightning Experience).
Why: Field history reports let you track changes in fields by adding an “Old Value” and a “New Value” column. With a field history report, each report row represents a change to a record. By filtering a field history report, you can answer questions like “Which opportunities have we closed so far this financial quarter?”
All filters on the Old Value and New Value fields are text filters, which sometimes means that special consideration is needed. Even if New Value captures a change to, say, a numeric field - like Amount - it captures the number as text. That means that filter operators like “greater than” and “less than” don’t work like you’d expect. If a field represents a currency - like USD 1,000.50 - and you filter the report to show New Value equals USD 1,000.50, then the filter doesn’t always return records for a colleague who works in a different locale. For example, for your colleague in Paris to get the same report results, the filter would probably need to read New Value equals USD 1 000,50 because number formats vary widely by locale.
How: To see which opportunities closed this financial quarter, create a report based on the report type Opportunity Field History, then add these filters:
- Ensure that the Edit Date standard filter is set to Current FQ.
- New Value contains Closed
- Old Value does not contain Closed
If you don’t see the Opportunity Field History report type, or if your Opportunity Field History report shows no records even though you know that opportunities have changed, then turn on field history tracking. For information about field tracking, including how to set it up, see Field History Tracking in Salesforce Help.