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
Robbert Bos2Robbert Bos2 

WHERE clause on a date in a for loop

I'm trying to find the fee which is active on the date of the worked hours. Goal is to schedule this every week to see our revenues.

Does anybody know how I can use the where clause in de for loop so the start date of the Fee is smaller (or equal) to the date of the time entry?
  
global class RevenueTimeEntry implements Schedulable {
    global void execute(SchedulableContext sc) {

        List<timeEntry__c> TimeEntry = new List<timeEntry__c>(
                [SELECT Id, date__c
                 FROM timeEntry__c 
                 WHERE Fee1__c = null
                 LIMIT 10]);

        System.debug(TimeEntry); 

        for(timeEntry__c te : TimeEntry){
            Decimal fee = [ SELECT Fee2__c
                            FROM AssignmentFee__c 
                            //WHERE DateStart__c <= :TimeEntry.date__c
                            LIMIT 1].Fee2__c;

            te.Fee1__c = fee;
        }
        update TimeEntry;
    }
}

 
Best Answer chosen by Robbert Bos2
Robbert Bos2Robbert Bos2
This is the solution!
  
global class OmzetTimeEntry implements Schedulable {
    global void execute(SchedulableContext sc) {
        List<timeEntry__c> TimeEntry = new List<timeEntry__c>(
            [SELECT Id, date__c, timeSheet__r.Assignment__c
            FROM timeEntry__c 
            WHERE FeeHour__c = null]);

        List<timeEntry__c> updateTimeEntry = new List<timeEntry__c>();
        List<AssignmentFee__c> assign = [SELECT Fee__c, DateStart__c, Assignment__c FROM AssignmentFee__c ];

        for(AssignmentFee__c a : assign){
            for(timeEntry__c te : TimeEntry) {

                if(a.DateStart__c <= te.date__c &&
                    a.Assignment__c == te.timeSheet__r.Assignment__c) {
                    te.FeeHour__c = a.Fee__c;
                updateTimeEntry.add(te);
                }   
            }
        }
        update updateTimeEntry;
    }
}

 

All Answers

Robbert Bos2Robbert Bos2
This is the solution!
  
global class OmzetTimeEntry implements Schedulable {
    global void execute(SchedulableContext sc) {
        List<timeEntry__c> TimeEntry = new List<timeEntry__c>(
            [SELECT Id, date__c, timeSheet__r.Assignment__c
            FROM timeEntry__c 
            WHERE FeeHour__c = null]);

        List<timeEntry__c> updateTimeEntry = new List<timeEntry__c>();
        List<AssignmentFee__c> assign = [SELECT Fee__c, DateStart__c, Assignment__c FROM AssignmentFee__c ];

        for(AssignmentFee__c a : assign){
            for(timeEntry__c te : TimeEntry) {

                if(a.DateStart__c <= te.date__c &&
                    a.Assignment__c == te.timeSheet__r.Assignment__c) {
                    te.FeeHour__c = a.Fee__c;
                updateTimeEntry.add(te);
                }   
            }
        }
        update updateTimeEntry;
    }
}

 
This was selected as the best answer
NagendraNagendra (Salesforce Developers) 
Hi Robert,

It is a best practice to avoid soql query inside the for loop.

Please check the below code. 
global class RevenueTimeEntry implements Schedulable {

global void execute(SchedulableContext sc) {

    List<timeEntry__c> TimeEntry = new List<timeEntry__c>(
            [SELECT Id, date__c
             FROM timeEntry__c 
             WHERE Fee1__c = null
             LIMIT 10]);

    System.debug(TimeEntry);
     List<timeEntry__c> updateTimeEntry = new List<timeEntry__c>(
    List<AssignmentFee__c> assign = [SELECT Fee2__c, DateStart__c FROM AssignmentFee__c ];

   for(AssignmentFee__c a : assign){
        for(timeEntry__c te : TimeEntry)
        {
        if(a.DateStart__c <= te.date__c)
            {
            te.Fee1__c = a.Fee2__c;
            updateTimeEntry.add(te);
            }
        }
    }
    update updateTimeEntry;
}
}
Thanks,
Nagendra