Time Sheets and Time Sheet Exceptions Table

[rdskn023ET]

Hi,

Has anyone ran into an issue where time sheets are setup to be automatically generated on a weekly basis and the time sheet exception table is displaying incorrect results when those time sheets are deleted at a later time? For instance, in our case, we generate time sheets for all active employees who are part of a specific group within in the organization. If someone leaves the organization without notifying our group, which happens often within a large organization, we then have to go back and delete the time sheets for those users. However, for those using the time sheet exception dashboard reports, it seems that those time sheets remain as unsubmitted in the reporting data, even though they no longer exist since being deleted. Has anyone experienced something like this or written something to automatically delete the record in the time sheet exception table once the actual time sheet has been deleted? Any insight would be greatly appreciated! Thanks!

[Chaitanya Redd1]

Hi,

To address this issue, one possible solution is to create a business rule that automatically deletes the record in the time sheet exception table once the actual time sheet has been deleted. Here are the high-level steps to create such a business rule:

1. Navigate to System Definition > Business Rules.
2. Click "New" to create a new business rule.
3. Enter a name for the business rule, such as "Delete Time Sheet Exceptions."
4. In the "When to run" section, select the "Before" option and choose the "Delete" operation.
5. In the "Advanced" section, add a script that will delete the corresponding record(s) from the time sheet exception table. Here is an example script:

This script will run before the time sheet record is deleted and will search for any associated records in the time sheet exception table. It will then delete those records to ensure that the reporting data is accurate and up-to-date.

Once the business rule is created, it will automatically delete any corresponding records in the time sheet exception table when a time sheet is deleted. This will ensure that the reporting data is accurate and up-to-date, even if time sheets are deleted at a later time.

(function executeRule(current, previous /*null when async*/) {

  var timeSheetID = current.getValue("time_sheet");
  
  var exceptionTable = new GlideRecord("time_sheet_exception");
  exceptionTable.addQuery("time_sheet", timeSheetID);
  exceptionTable.deleteMultiple();

})(current, previous);

[rdskn023ET]

Thanks! This is what I had intended to do, but wasn't sure about the code to delete the time sheet exception entries. When I tested this out, it removed all the time sheet exceptions currently out there. I am only looking to delete those that have no actual time sheet entry associated with it. We may go back in and remove all the time sheet exceptions in our production environment to get a fresh start, but then moving forward we only want the time sheet exception item associated with the time sheet being deleted to be deleted along with it. This will ensure that all remaining entries in the time sheet exception table remain in place so the report and dashboard work as intended for our resource managers. Hope that makes sense. 

[rdskn023ET]

Any other suggestions on the code to only remove the time sheet exception related to the time sheet item being deleted? Any help would be greatly appreciated!

[Chaitanya Redd1]

Hi,

(function executeRule(current, previous /*null when async*/) {
  var timeSheetItemID = current.getValue("sys_id"); // Assuming the sys_id of the deleted time sheet item is used as the condition

  var exceptionTable = new GlideRecord("time_sheet_exception");
  exceptionTable.addQuery("time_sheet_item", timeSheetItemID); // Add a query condition to filter by the time sheet item
  exceptionTable.deleteMultiple();

})(current, previous);

To remove only the time sheet exception related to the deleted time sheet item, you can modify the code as follows: