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
George Liang 3George Liang 3 

SOQL Query to Pull Field that has not been modified for the last 21 days

In Opportunity, I have a field called Status__c. I want to pull a list of records in which Status__c specifically has not been updated in the last 21 days. History tracking has been turned on for that field over a year ago. What's the SOQL query to do so?
Naveen IlaNaveen Ila
Hey George, 

Below is a proto type I haven't tested the same.  I hope below code works for your Business Case. 

Create a field called "Status_Last_Updated__c"

Execute below logic. 

  
List<Opportunity> conList = [SELECT Id, CreatedDate, (SELECT OldValue, NewValue, Field FROM Histories WHERE Field = 'Status__c' ORDER BY CreatedDate DESC LIMIT 01) 
FROM Opportunity];

for (Opportunity opp : oppList) {
	
	// defaulty assining the Status last changed date with created date 
	opp.opp.Status_Last_Updated__c = opp.CreatedDate;

	// If we have history record associated with Status__c field of opportunity 
	if (NULL != opp.Histories && opp.Histories.size() > 0) {
		//Loading latest histroy record of the Status field 
		OpportunityHistory oppHistoryRecord = opp.Histories.get(0);
	    opp.Status_Last_Updated__c = oppHistoryRecord.CreatedDate;
	}

}

update oppList;


 
In you organization if you have more than 10000 records. Try to update the record in chuncks by executing the above logic with below main query. 
  
[SELECT Id, CreatedDate, (SELECT OldValue, NewValue, Field FROM Histories WHERE Field = 'Status__c' ORDER BY CreatedDate DESC LIMIT 01) 
FROM Opportunity WHERE Status_Last_Updated__c != NULL  LIMIT 7000 ];


Once you are done updating the field Status_Last_Updated__c. 

Define process using process build : 
 
update the field Status_Last_Updated__c value "now" whenever the record created or The status field updated to new value. 


 
Ankit Sharma CCDEVAnkit Sharma CCDEV
Hello George,

Please try this:

Select Id, name from opportunity where id in (SELECT OpportunityId FROM OpportunityFieldHistory where field = 'Status__c' and createdDate <  LAST_N_DAYS:21)

Thanks,
Ankit