You need to sign in to do that
Don't have an account?
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.
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:
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:
All Answers
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:
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:
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.
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.
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.