I'm trying to get a list of Opportunities that have now "Cancelled" (isClosed=true and isWon=false) stageName and the previous (Penultimate) value was "Active" by SOQL .
For example, the Opp X had these history
- Created Active
- Updated Active
- Updated Paused
- Updated Active
- Updated Cancelled
This record should be return by the soql, but it shoudn't be returned if the step 4 doesn't exists.
In addition those opps should be and specific RecordType XX.
So far I've tried this:
Opportunity[] o =[select id from opportunity where isclosed = true and isWon = false recordtype.name = 'XX'];
OpportunityHistory[] oh = [SELECT OpportunityId FROM OpportunityHistory WHERE StageName = 'Active' and OpportunityId in:o ];
But I cant figure out how to know that the second query is getting only the last previous Stage
Challenges
- I have more than 10k Opps that should match
Best Answer
The
OpportunityHistory
does hold a record for each change of an opportunity. So if it went to stage Active, there will be a record for that. So it should be possible to execute the following query:It selects all Opportunities, which are not won and closed from a certain record type, if there is a history entry indicating that the Opoortunity has been Active once.
It is still debatable, if it will perform adequately with thousands of Opportunities and millions of OpportunityHistory entries. I would give it a try, since the query can be performed from the Developer Console without much effort.