function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Nethra RaghupathyNethra Raghupathy 

How to write subquery on opportunity field history table with new value and old value field

I would like to count number of unique opportunities which has increased in amount value in its latest transactions, from opportunity field history table. 
SELECT opportunityId, newValue,oldValue  FROM OpportunityFieldHistory where createdDate IN ( select max(createdDate) FROM OpportunityFieldHistory where field ='amount' group by opportunityId)

This isn't working. I couldn't use new value and old value too in where clause, to check whether amount is increased. Is there any way to do this?  
Fabio PalladinoFabio Palladino
Hi Nethra,
unfortunately SOQL doesn't support field to field comparison :-(
An Idea is in roadmap https://success.salesforce.com/ideaView?id=08730000000BrHAAA0. (#)
If you want to use Apex and SOQL you must query on entire field history table and after you have to traverse all results recordset and select the correct Opty Id.
This is really boring and not elegant !!!!
My suggest is:
  • create custom currency field on Opty Object (old_Amount)
  • set this field with old value of Amout when Amount is changed (through process builder or workflow rule or trigger)
  • create a custom formula field (checkbox or text) on Opty object with an expression like this (Please check this expression before):
IF( Amount >= old_Amount__c, TRUE, FALSE)

With this configuration you can query through SOQL directly on Opty object with a "where" condition on formula field equals 'TRUE'.

Let me know

Fabio
Nethra RaghupathyNethra Raghupathy
Thanks Fabio.
I'm just wondering why that query didn't work even though it doesn't use newValue and oldValue in where clause. It works fine when executed individually in query editor but I couldn't execute it separately in apex, there is some problem in date format.   
Fabio PalladinoFabio Palladino
Ok, 
I understund now :-)
Probabily the issue related to the date format of the filed. But in my environment your query doesn't work both in query editor and in apex.
Another simple solution is to split your query into two different SOQL statement first the inner query and after the outer one using the results of hte first one.

Regards