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
DJPDJP 

Calculate hours of the current week

Hello
I have requirement to calculate today hours for the current week. 
We have custom object Tracker__C and field Hours__C. Hours__C is user entered field. Next to the user entered field we wanted to show the users total hours he has entered thus far this week. 
For example  (Day of the week - Hours entered - Total hours this week)
Monday - 4 - 4
Tuesday - 6 - 10
Wednesday - 2 - 12
Thursday - 8 - 20
Friday - 8 - 28

All help appreciated
Thanks
Dinesh
Best Answer chosen by DJP
Abdul KhatriAbdul Khatri
Can you try the below code
  
trigger UpdateTotalWeekHours on Tracker__c (before insert, before update) {
    
    Map<Id, Tracker__c> idOwnerTrackerMap = new Map<Id, Tracker__c>();
    List<Id> idTrackerList = new List<Id>();
    for(Tracker__c tracker : Trigger.new) {
        
        idOwnerTrackerMap.put(tracker.OwnerId, tracker);
        
        if(Trigger.isUpdate)
            idTrackerList.add(tracker.Id);
    }
    
    String sSOQL;
    String sWhere = '';
    
    if(!idTrackerlist.isEmpty())
        sWhere = ' AND Id NOT IN :idTrackerlist';
    
    List<Id> tempIdOwnerList = new List<Id>(idOwnerTrackerMap.keySet()); 
	sSOQL = 'SELECT SUM(Hours__c) totalHours, OwnerId FROM Tracker__c WHERE OwnerId = :tempIdOwnerList ' + sWhere + ' GROUP BY OwnerId';
	List<AggregateResult> sumHoursTrackerResults = Database.query(sSOQL);
    Map<Id, Integer> totalHoursMap = new Map<Id, Integer>();
    
    if(!sumHoursTrackerResults.isEmpty()) {
        
        for(AggregateResult ar : sumHoursTrackerResults) {
            totalHoursMap.put((Id)ar.get('OwnerId'), Integer.valueOf(ar.get('totalHours')));
        }
    }
    
    for(Tracker__c tracker : Trigger.new) {

        tracker.totalhoursweek__c = tracker.Hours__c;
        if(!totalHoursMap.isEmpty() && totalHoursMap.containsKey(tracker.OwnerId))	            
        	tracker.totalhoursweek__c = totalHoursMap.get(tracker.OwnerId) + tracker.Hours__c;
            
    }

}

 

All Answers

Abdul KhatriAbdul Khatri
Can you share the Custom Object Details so we understand what attributes needed in order to fulfill the requirements?
Raj VakatiRaj Vakati
If you want to show this type page .. i guess you have to use visualforce or lightning .. 

If you create a formula field that will appear only after saving the data .. 

 
Abdul KhatriAbdul Khatri
Hi Raj,

How formula field will work in this case? Can you give some perspective?
Abdul KhatriAbdul Khatri
Hey DJP, Any update on that?
DJPDJP
@Abdul Khatri
Other fields other than system fields on the custom object Tracker__C are, Account__C (has lookup relationship with Account), Date_Visited__c, Comments__c and the one I mentioned in the original question Hours__C. We have a request to add two more fields 1 is the OpportunityID__C which will have look up with Opportunity object and other is the totalhoursweek__C which is the one I have challenge with. 
For Monday it will always show the hours entered for Monday, For tuesday it will show hours entered on Monday + Hours entered on Tuesday so on and so forth. 

Thanks
Dinesh
Abdul KhatriAbdul Khatri
Can you try the below code
  
trigger UpdateTotalWeekHours on Tracker__c (before insert, before update) {
    
    Map<Id, Tracker__c> idOwnerTrackerMap = new Map<Id, Tracker__c>();
    List<Id> idTrackerList = new List<Id>();
    for(Tracker__c tracker : Trigger.new) {
        
        idOwnerTrackerMap.put(tracker.OwnerId, tracker);
        
        if(Trigger.isUpdate)
            idTrackerList.add(tracker.Id);
    }
    
    String sSOQL;
    String sWhere = '';
    
    if(!idTrackerlist.isEmpty())
        sWhere = ' AND Id NOT IN :idTrackerlist';
    
    List<Id> tempIdOwnerList = new List<Id>(idOwnerTrackerMap.keySet()); 
	sSOQL = 'SELECT SUM(Hours__c) totalHours, OwnerId FROM Tracker__c WHERE OwnerId = :tempIdOwnerList ' + sWhere + ' GROUP BY OwnerId';
	List<AggregateResult> sumHoursTrackerResults = Database.query(sSOQL);
    Map<Id, Integer> totalHoursMap = new Map<Id, Integer>();
    
    if(!sumHoursTrackerResults.isEmpty()) {
        
        for(AggregateResult ar : sumHoursTrackerResults) {
            totalHoursMap.put((Id)ar.get('OwnerId'), Integer.valueOf(ar.get('totalHours')));
        }
    }
    
    for(Tracker__c tracker : Trigger.new) {

        tracker.totalhoursweek__c = tracker.Hours__c;
        if(!totalHoursMap.isEmpty() && totalHoursMap.containsKey(tracker.OwnerId))	            
        	tracker.totalhoursweek__c = totalHoursMap.get(tracker.OwnerId) + tracker.Hours__c;
            
    }

}

 
This was selected as the best answer
DJPDJP
@Abdul Khatri
The trigger works to the most part however one issue is it adds all hours for this object on total hours field.
It should only total the hours for current week. 
If I have hours for two weeks and it calculates total hours for both weeks.

Thanks for your response
Dinesh
Abdul KhatriAbdul Khatri
If I understood correctly you mean it is not totalling the hours of the current week but accumulating for the previous weeks also.

If I misunderstood, sorry for that and if you explain with example what is doing that would help.
DJPDJP
Sorry for the confusion. 
The work week is from Monday to Friday. User will enter hours at the end of the day. So on Monday totalhoursweek__c will show the hours he entered since its the first day of the week. On tuesday the field totalhoursweek__c will show hours entered on Monday + Hours entered on Tuesday. On Wednesday totalhoursweek__c field will show hours entered on Monday + Tuesday + Wednesday so on and so forth. This will repeat for each week. From next Monday the value is reset and starts all over again for new entries. The table below might help. 
Thanks Again
sampledata
 
Abdul KhatriAbdul Khatri
Please change the following line in the above code  
sSOQL = 'SELECT SUM(Hours__c) totalHours, OwnerId FROM Tracker__c WHERE OwnerId = :tempIdOwnerList ' + sWhere + ' GROUP BY OwnerId';
To this 
sSOQL = 'SELECT SUM(Hours__c) totalHours, OwnerId FROM Tracker__c WHERE OwnerId = :tempIdOwnerList AND CreatedDate > THIS_WEEK  ' + sWhere + ' GROUP BY OwnerId';

Salesforce consider Sunday the first day of the week, but if there is no entry on that Day, the code must work as per expectation. 

Let me know if that work