1

Using this Soql : SELECT count(case.subject) FROM CaseHistory where field ='subject' i was able to retrieve number of time Subject field has changed

Since we can not filter by OldValue / NewValue , Is there a way to retrieve number of time lookup/LongText/RecordType field has changed using soql?

enter image description here

1 Answer 1

1

To retrieve the number of times a long text field has changed, first ensure that Your_Long_Text_Field__c is history-tracked in Setup menu, or was at some point, and has seen data changes. Then run this:

SELECT Count(Id) from CaseHistory where field = 'Your_Long_Text_Field__c'

You can do something similar for RecordTypeId, or Your_Custom_Lookup__c

Note about counting changes to lookup fields:

You can avoid double counting lookup field changes by further filtering the query by DataType. For example, ContactId is a lookup field, found by field='contact' on the CaseHistory sobject, and we apply DataType filters to avoid double-counting as it seems when a lookup field value changes, the history table tracks the text/label change separately from its referenceable id : SELECT Count(Id) from CaseHistory where field = 'Contact' AND DataType='Text' vs SELECT Count(Id) from CaseHistory where field = 'Contact' AND DataType='EntityId'

8
  • It doesn't work with Lookup field or RecordType , SELECT Count(Id) from CaseHistory where field = 'ContactId' . ContactId is history-tacked but the soql is returning 0 row
    – Farkous
    Jan 7, 2021 at 19:00
  • Hmm, for whatever reason, the conventions different: you have to filter like this SELECT Count(Id) from CaseHistory where field = 'Contact' notice for some reason field='contactid' wont work Jan 7, 2021 at 19:46
  • Thanks but it return wrong result - Example : if i change contact name on case 1 time the soql return 2 , if i change the contact name 2 times it return 4 - it count the old and new value
    – Farkous
    Jan 7, 2021 at 21:01
  • 1
    Same, i suspect thats a design of lookup fields because when in testing, i see when a lookup field changes the history table increases twice. Once for DataType=Text, once for DataType=EntityId. In other words try one of these when counting lookup field changes (either): SELECT Count(Id) from CaseHistory where field = 'Contact' AND DataType='Text' or SELECT Count(Id) from CaseHistory where field = 'Contact' AND DataType='EntityId' Jan 7, 2021 at 21:07
  • Or just devide the number by 2 lol , sorry but what do you mean by DataType = 'Text' , EntityId? it's a lookup ..
    – Farkous
    Jan 7, 2021 at 21:28

You must log in to answer this question.

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