0

I am trying to query Opportunity Field History to get the results for a custom lookup field that has history tracking. I am getting 2 records back for 1 single change, one of the record has oldvalue and newvalue as lookup id and another record has lookup name as old and new value. I was wondering why Field history is creating 2 records for same change. I am trying to query it and set the resulting list to a front end table, but since it is returning 2 values for single change there is a duplicate value displayed. how can i restrict that?

my query looks like this:

SELECT id, OldValue, NewValue
FROM OpportunityFieldHistory
WHERE OpportunityId='oppID'
2
  • Try adding the Field column into your query, it should tell you the tracked field of each row. I assume they are not the same field. Jun 29, 2020 at 0:54
  • I added Field column to the query query it still shows me 2 records for the same tracked lookup field one with id for old value and new value for lookup field and one with name value of the lookup field in old value and new value
    – apc
    Jun 29, 2020 at 1:07

2 Answers 2

1

This is "working-as-designed"

For example, field history on Opportunity.OwnerId will return two records

OldValue      NewValue
----------     --------
oldId          newId
oldOwner.Name  newOwner.Name  // facilitates display in the Related List

Since you can't filter on OldValue or NewValue in the query; you'll need to post-process in Apex, testing OldValue / NewValue to see if they cast to ID type

try {
   Id oldValAsId = (Id) fieldHistoryItem.OldValue;
}
 catch (Exception e) { // ignore item  }
0

The wording of your question is a bit odd, and assuming you are not confusing the fields OldValue and 'NewValue` for records, it seems to me you would want to select the latest change to get one entry, being the last change made to the Opportunity record.

SELECT Id, NewValue
FROM OpportunityFieldHistory
WHERE OpportunityId='OppId'
AND Field = 'CustomFieldName__c'
ORDER BY CreatedDate DESC LIMIT 1

This will give you the new value for the changed field for the latest change to the specific Opportunity record.

That being said, if you are seeing two changes in the history tracker - one that set to the new value, and another that set back to the old value, for what the same save, then perhaps there is some process, maybe an async method, that is changing the value back after the original save occurred.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .