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 ]);

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.

|improve this answer|||||
  • 2
    It also assumes the user has turned on Field History tracking, but the solution is great. – Sebastian Kessel Apr 5 '17 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 '17 at 15:33
  • @aditya That's what the sub-query (Left Inner Join) is for (Id IN (...)). – Adrian Larson Apr 5 '17 at 15:38
  • 1
    @SebastianKessel Good point. I hadn't really thought of that consideration. – Adrian Larson Apr 5 '17 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 '17 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.