I'm trying to fetch opportunity records whose field(StageName) has been changed in the past 24 hours and whose child record(Say="Some X OBject", it's a masterdetail relationship) has field called 'status'=approved?

How can I fetch these records in the start method of a batch class?

I've tried:

List<Id> OppIds= new List<Id>(); 
for (Invoice__C Inv:[select id,name from Invoice__C where status__C='Approved']) {

DateTime Past24 = system.now().AddDays(-1); 
return Database.getQueryLocator([select id from Opportunity where LastModifiedDate > :Past24 and Sent_to_ShareDo__c=false and Id IN :OppIds ]);

1 Answer 1


You need to first create a rollup summary field to count how many detail records have the requisite status. There is ample documentation out there about how to set that up, so you should be able to figure out that step.

Let's assume you call this rollup summary field Approved_Child_Count__c. Then you should be able to do something like:

public Database.QueryLocator start(Database.BatchableContext context)
    return [
        SELECT ... FROM Opportunity
        WHERE Approved_Child_Count__c > 0
        AND Id IN (
            SELECT OpportunityId FROM OpportunityFieldHistory
            WHERE Field = 'Stage' AND CreatedDate >= YESTERDAY

It's not exactly the last 24 hours, but you can tweak the date literal in your Left Inner Join as needed.

  • 2
    It also assumes the user has turned on Field History tracking, but the solution is great. Apr 5, 2017 at 15:28
  • Okay. How can i fetch opps, for which, stage name changed in the last X hours. I enabled History Tracking for Stagename
    – aditya
    Apr 5, 2017 at 15:33
  • @aditya That's what the sub-query (Left Inner Join) is for (Id IN (...)).
    – Adrian Larson
    Apr 5, 2017 at 15:38
  • 1
    @SebastianKessel Good point. I hadn't really thought of that consideration.
    – Adrian Larson
    Apr 5, 2017 at 15:38
  • Please suggest me how to query opportunities for which child object 'invoice' has been created in the last 24 hours with status=approved. Thanks.
    – aditya
    Apr 7, 2017 at 12:16

You must log in to answer this question.

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