+ Start a Discussion
Evan DonovanEvan Donovan 

Implementing status- and time-based triggers for a timesheet system

I have two kinds of use cases for triggers in a timesheet system that I've developed which I don't currently know how to implement, at least in a scalable fashion that won't run into governor limits.

 

In general, I don't know what's the best way to trigger events based on the status of multiple records of the same type, all related to a master record.

 

Use Case 1:

 

I want to have a report that shows when an intern has submitted a timesheet two weeks in a row which has 0 hours recorded on it.

 

The need for this is that people can take vacations from their work, but we need to be have flagged when people are away for longer than 5 days, so we can follow up with them specifically about it. Most of the interns work off-site so without the timesheets we wouldn't know.

 

I'm presuming to do this I need some sort of trigger that sets a flag on a timesheet record when it is the second week in a row of 0 hours. The total hours on a timesheet is a formula field which totals up all the hours that they entered on the timesheet.

 

I have a custom controller extension on the Visualforce page, so potentially I could do this in the save method, by looking at the total for the previous week as well. Or would the formula not be calculated yet at that point?

 

Alternatively, I could use a trigger on insert & update, but I am concerned that I'd run into governor limits. I don't know how I'd structure the queries to run in bulk.

 

Use Case 2:

 

I want to send out emails when interns are 2 weeks behind on submitting timesheets, or their site directors are 4 weeks behind in approving them.

 

I have reports already on the Timesheet Master object which is the master for the Timesheet object, that look at a formula field that looks in turn at rollup summaries of the statuses on the timesheets.

 

However, I think formula fields are automatically calculated so I don't think I can have the triggers be on the Timesheet Master and be set to send out an email if Member Weeks Behind >= 2 or Site Director Weeks Behind >= 4.

 

I need to have these time-based, so would I have to create some sort of scheduled apex job that runs each day and looks at the Current Date vs. the Member Start Date (which is a field on their Contact record, which is linked via the Timesheet Master to the Timesheet), and sends out an email to the member or site director if the difference is too great?

 

Alternatively, is there some sort of time-based workflow I could use here? That would be simpler if it were possible.

I could give as much more detail about our setup as is necessary, if it would be helpful to you.

Best Answer chosen by Admin (Salesforce Developers) 
zachelrathzachelrath

Evan, here's my thoughts on your use cases:

 

Use Case 1:

-If you're looking for a report that could be scheduled to run every weekend that looks back over Timesheets submitted just in the past 2 weeks where the total hours for the Timesheets submitted during those 2 weeks is 0, then you could solve this purely through reporting. Do a report on the Timesheet object summarized by Person, with the Timesheet date range set to THIS_WEEK OR LAST_WEEK, or whatever makes sense depending on when your Timesheets have to be submitted by and when you want the report to be run.

-If, on the other hand, you want a report of ALL occurences of the rule "0 hours in past 2 weeks of Timesheets" over a much longer period of time (say over a month or semester), then I think yes, you'll have to set some sort of flag on the offending timesheets(s). I'd probably do the validation in the Save method of your Visualforce controller as long as Timesheets are being created strictly through the UI (i.e. none are created through the API), because it would be more straightforward to handle each Person's Timesheets individually.  Here's some sample code:

 

private Timesheet__c timesheet;

public PageReference save() {

	// Set a savepoint so that we can rollback
	// if anything goes wrong
	Savepoint sp = Database.setSavepoint();

	// Before we save this Timesheet,
	// check to see if 0 hours were recorded
	if (timesheet.Total_Hours_For_Week__c == 0) {
		// Get the most recent Timesheet for this person,
		// and see if 0 hours were recorded for it
		List<Timesheet__c> pastTimesheets
			= [SELECT Id, Total_Hours_For_Week__c, Date_Submitted__c
				FROM Timesheet__c
				WHERE Person__c = :timesheet.Person__c
				ORDER BY Date_Submitted__c DESC
				LIMIT 1];
		if (!pastTimesheets.isEmpty()) {
			Date lastSubmitDate = pastTimesheets[0].Date_Submitted__c;
			// If this was in fact the Timesheet for last week...
			if ((timesheet.Date_Submitted__c.daysBetween(lastSubmitDate) <= 10)
				&& pastTimesheets[0].Total_Hours_For_Week__c == 0) {
					// Flag the current Timesheet for later review,
					// but allow it to save
					timesheet.Flagged__c = TRUE;
				}
			}
		}
		
	}
	
	// Insert the Timesheet
	try {
		insert timesheet;
	} catch (Exception ex) {
		// Roll-back the database 
		Database.rollback(sp);
		// Let the user know what went wrong
		ApexPages.addMessages(ex);
	}
	
	// If our operation was a success...
	if (timesheet.Id != null) {
		// Send the User to the Detail page for our Timesheet
		// (or wherever else they should be sent)
		return (new ApexPages.StandardController(timesheet)).view();
	} else return null;
}

 

 

However, you could also do this through Triggers and future methods without using more than 2 SOQL queries, and with no danger of exceeding Governor Limits:

 

trigger Timesheets on Timesheet__c (after insert) {
	
	// We use an After Insert trigger because this way we know that the record
	// has passed all validations and has indeed already been inserted 
	if (Trigger.isAfter && Trigger.isInsert) {	
	
		Set<Id> personIds = new Set<Id>();
		
		for (Timesheet__c ts : Trigger.new) {
			if (ts.Total_Hours_For_Week__c == 0) {
				// We need to retrieve the 2 most recent Timesheets
				// for this person to see if they both had only 0 hours recorded
				personIds.add(ts.Person__c);
			}
		}
		
		if (!personIds.isEmpty()) {
			TimesheetUtils.CheckForSubsequentZeroHourTimesheets(personIds);
		}
	}
	
}

public class TimesheetUtils {

	// We run this as a future method
	// because it's not essential that it take place immediately.
	// If we ran it synchronously,
	// 		then the User might have to sit and wait longer
	// 		after pressing the save button on a Timesheet record.
	// Plus, we can't directly edit a record in an after Trigger
	//		--- i.e. we'd have to do DML in the Trigger,
	//		which wastes the User's time and eats at governor limits
	@future
	public static void CheckForSubsequentZeroHourTimesheets(Set<Id> peopleIds) {
		// Timesheets that need to be flagged
		List<Timesheet__c> timesheetsToFlag = new List<Timesheet__c>();
		
		// Retrieve the most 2 recent Timesheets for these People
		for (List<Contact> lstPeople : 
				[SELECT Id,
					(select Id, Total_Hours_For_Week__c, Date_Submitted__c
					from Timesheets__r
					where Total_Hours_For_Week__c = 0
					order by Date_Submitted__c DESC
					limit 2)
				FROM Contact
				WHERE Id in :personIds]) {
			for (Contact c : lstPeople) {
				// Verify that the two timesheets we retrieved
				// are indeed for subsequent weeks
				if ((c.Timesheets__r.size() == 2)
				&& (c.Timesheets__r[0].Date_Submitted__c.daysBetween(c.Timesheets__r[1].Date_Submitted__c) <= 10)) {
					// The most recent timesheet needs to be flagged
					timesheetsToFlag.add(c.Timesheets__r[0].Id);
				}
			}
		}
		
		if (!timesheetsToFlag.isEmpty()) {
			for (Timesheet__c ts : timesheetsToFlag) {
				ts.Flagged__c = true;
			}
			update timesheetsToFlag;
		}
	}	

}

 

 

 

 

All Answers

zachelrathzachelrath

Evan, here's my thoughts on your use cases:

 

Use Case 1:

-If you're looking for a report that could be scheduled to run every weekend that looks back over Timesheets submitted just in the past 2 weeks where the total hours for the Timesheets submitted during those 2 weeks is 0, then you could solve this purely through reporting. Do a report on the Timesheet object summarized by Person, with the Timesheet date range set to THIS_WEEK OR LAST_WEEK, or whatever makes sense depending on when your Timesheets have to be submitted by and when you want the report to be run.

-If, on the other hand, you want a report of ALL occurences of the rule "0 hours in past 2 weeks of Timesheets" over a much longer period of time (say over a month or semester), then I think yes, you'll have to set some sort of flag on the offending timesheets(s). I'd probably do the validation in the Save method of your Visualforce controller as long as Timesheets are being created strictly through the UI (i.e. none are created through the API), because it would be more straightforward to handle each Person's Timesheets individually.  Here's some sample code:

 

private Timesheet__c timesheet;

public PageReference save() {

	// Set a savepoint so that we can rollback
	// if anything goes wrong
	Savepoint sp = Database.setSavepoint();

	// Before we save this Timesheet,
	// check to see if 0 hours were recorded
	if (timesheet.Total_Hours_For_Week__c == 0) {
		// Get the most recent Timesheet for this person,
		// and see if 0 hours were recorded for it
		List<Timesheet__c> pastTimesheets
			= [SELECT Id, Total_Hours_For_Week__c, Date_Submitted__c
				FROM Timesheet__c
				WHERE Person__c = :timesheet.Person__c
				ORDER BY Date_Submitted__c DESC
				LIMIT 1];
		if (!pastTimesheets.isEmpty()) {
			Date lastSubmitDate = pastTimesheets[0].Date_Submitted__c;
			// If this was in fact the Timesheet for last week...
			if ((timesheet.Date_Submitted__c.daysBetween(lastSubmitDate) <= 10)
				&& pastTimesheets[0].Total_Hours_For_Week__c == 0) {
					// Flag the current Timesheet for later review,
					// but allow it to save
					timesheet.Flagged__c = TRUE;
				}
			}
		}
		
	}
	
	// Insert the Timesheet
	try {
		insert timesheet;
	} catch (Exception ex) {
		// Roll-back the database 
		Database.rollback(sp);
		// Let the user know what went wrong
		ApexPages.addMessages(ex);
	}
	
	// If our operation was a success...
	if (timesheet.Id != null) {
		// Send the User to the Detail page for our Timesheet
		// (or wherever else they should be sent)
		return (new ApexPages.StandardController(timesheet)).view();
	} else return null;
}

 

 

However, you could also do this through Triggers and future methods without using more than 2 SOQL queries, and with no danger of exceeding Governor Limits:

 

trigger Timesheets on Timesheet__c (after insert) {
	
	// We use an After Insert trigger because this way we know that the record
	// has passed all validations and has indeed already been inserted 
	if (Trigger.isAfter && Trigger.isInsert) {	
	
		Set<Id> personIds = new Set<Id>();
		
		for (Timesheet__c ts : Trigger.new) {
			if (ts.Total_Hours_For_Week__c == 0) {
				// We need to retrieve the 2 most recent Timesheets
				// for this person to see if they both had only 0 hours recorded
				personIds.add(ts.Person__c);
			}
		}
		
		if (!personIds.isEmpty()) {
			TimesheetUtils.CheckForSubsequentZeroHourTimesheets(personIds);
		}
	}
	
}

public class TimesheetUtils {

	// We run this as a future method
	// because it's not essential that it take place immediately.
	// If we ran it synchronously,
	// 		then the User might have to sit and wait longer
	// 		after pressing the save button on a Timesheet record.
	// Plus, we can't directly edit a record in an after Trigger
	//		--- i.e. we'd have to do DML in the Trigger,
	//		which wastes the User's time and eats at governor limits
	@future
	public static void CheckForSubsequentZeroHourTimesheets(Set<Id> peopleIds) {
		// Timesheets that need to be flagged
		List<Timesheet__c> timesheetsToFlag = new List<Timesheet__c>();
		
		// Retrieve the most 2 recent Timesheets for these People
		for (List<Contact> lstPeople : 
				[SELECT Id,
					(select Id, Total_Hours_For_Week__c, Date_Submitted__c
					from Timesheets__r
					where Total_Hours_For_Week__c = 0
					order by Date_Submitted__c DESC
					limit 2)
				FROM Contact
				WHERE Id in :personIds]) {
			for (Contact c : lstPeople) {
				// Verify that the two timesheets we retrieved
				// are indeed for subsequent weeks
				if ((c.Timesheets__r.size() == 2)
				&& (c.Timesheets__r[0].Date_Submitted__c.daysBetween(c.Timesheets__r[1].Date_Submitted__c) <= 10)) {
					// The most recent timesheet needs to be flagged
					timesheetsToFlag.add(c.Timesheets__r[0].Id);
				}
			}
		}
		
		if (!timesheetsToFlag.isEmpty()) {
			for (Timesheet__c ts : timesheetsToFlag) {
				ts.Flagged__c = true;
			}
			update timesheetsToFlag;
		}
	}	

}

 

 

 

 

This was selected as the best answer
zachelrathzachelrath

Use Case 2:

 

I'm not exactly sure how your data model is set up with the whole Timesheet / Timesheet Master business, but I'd set up a Workflow Rule on the Timesheet object with a Time-Dependent Email Alert action set to execute 4 weeks after each Timesheet is submitted. This would be an excellent use case for Time-Dependent workflow. If you really have to rely on the Timesheet_Master__c record's formula fields, then yes, I think you'll have to do Scheduled Apex. But I'd avoid that if possible considering how much of a quick win it would be to use the workflow route.

Evan DonovanEvan Donovan

Thanks so much for the suggestions!

 

In re: Use Case 1 -

 

I like the idea of the trigger code – I think I’ll go that route. Our architecture is slightly different (mainly in that the key field on the timesheet is a Week #, since people have different start dates, and then the date of the timesheet is calculated from that on the save method), but I think I can adapt it to make it work. It was mainly the thought of the @future method being called from the trigger that was helpful. I haven’t used them yet.

 

In re: Use Case 2 - 

 

I'll see whether I can do time-based workflow, but the main issue I foresee with that is it would have to *only* send the email if there hadn't been any approvals subsequently to the one 4 weeks prior to the email. In other words, if there's another approval after the first approval, that email shouldn't go since the site director has actually been keeping up with the timesheets.

 

The situation would be analogous for members.

 

Anyway, this definitely gives me a direction to go in. I'll post back with any updates/followup questions.

 

If anyone else has suggestions of how to approach this, though, I'd love to hear them.

 

Evan DonovanEvan Donovan

As an update, I have a version of the trigger code you proposed working to handle my Use Case #1.

 

I'm going to have to look further into Use Case #2 to see what's the best way to go about it.

 

Probably I shouldn't have combined them into a single forum post, since they're so different.

 

If anyone is interested in the code that I ended up using, or the test class that I wrote for it, I would be happy to share privately.

 

I could share the test class publicly on here; I just didn't want to put too much code up at once.