How do I query New Value field on custom object history in SOQL?

Question

I'm trying to query the date from the history object where the last status (field) is 'Request Approved' I tried the following query but unable to query on NewValue field on the history object.

error: NewValue' can not be filtered in query call".

SELECT CreatedById,CreatedDate,Field,Id,IsDeleted,
       NewValue,OldValue,ParentId FROM custobj__History 
WHERE ParentId = 'a1228000000fagQ' 
  AND Field = 'Status__c' 
  AND NewValue='Request Approved'

Can Someone Help On this?

Answer 1

You cannot use the NewValue field in a query call, no way around it.

https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_entityhistory.htm

The field is not filterable.

You will have to do a full query and pull out the data in a loop i.e.

for( custobj__History hx : [SELECT CreatedById,CreatedDate,Field,Id,IsDeleted,
       NewValue,OldValue,ParentId FROM custobj__History 
WHERE ParentId = 'a1228000000fagQ' ]{

    if(hx.NewValue == 'Request Approved'){
        //Add to list, do something etc..
    }

}