Time tracking Automation with Scripts and Status

I’ve been working on a time tracker automation that allows my team to update a status field and kicks off a time tracker in the background so they don’t have to worry about tracking their hours.

The solution I came up with works well for me, but I have read through a few options from @Justin_Barrett and @kuovonne here Record timer script and here Time Tracker Block + Button to start?

The Setup
I have 6 fields

  • Current status
  • Current status change time
  • previous status
  • Previous status change time
  • total time
  • Current status = Previous status Check

A view is set up with a filter based on the field “Current status = Previous status Check” if true then filtered out, if false then they appear in the view.

When they enter the view it runs an automation that runs the following script (apologies not sure on correct formatting for the forum)

//This is to track the time on the AE work, it covers both the Online and Prep, it compares the current and previous status if a mismatch it checks if anything should be done then revises

//pull in the record details from automation
let inputConfig = input.config();
let RecordID = inputConfig[“RecordID”];

//I dislike pulling everything from the input variables so I pull them in a more normal scripting approach
let table = base.getTable(“Projects”);
let QueryResult = await table.selectRecordsAsync();
let Record = QueryResult.getRecord(RecordID);

// grab the things needed to check
let AEWorkOnlineStatus = Record.getCellValue(“AE Work Online Status”);
let AEWorkOnlinePreviousStatus = Record.getCellValue(“AE Work Online Previous Status”);
let AEWorkOnlineRecentchangeTime = Record.getCellValue(“AE Work Online Recent change time”);
let AEWorkOnlineWorkPreviousTime = Record.getCellValue(“AE Work Online Work Previous Time”);
let AEWorkOnlineTimeTrackTotal = Record.getCellValue(“AE Work Online Time Track Total”);

if (AEWorkOnlinePreviousStatus.name == AEWorkOnlineStatus.name){
//do nothing they match this is not the time your looking for
/// Check that the Previous status was a status you want time tracked, effectivly a filter for do you add time
if (AEWorkOnlinePreviousStatus.name!=“Finishing in Progress”){
// this does not need time tracking so just update the previous status to recent status
// and the previous time to current time
await table.updateRecordAsync(RecordID,{“AE Work Online Previous Status”:{“name”:AEWorkOnlineStatus.name}});
let RecentTime = new Date(AEWorkOnlineRecentchangeTime);
await table.updateRecordAsync(RecordID,{“AE Work Online Work Previous Time”:RecentTime});
} else {
// this does need time tracking done.
// clean up the Time for callculation
let RecentTime = new Date(AEWorkOnlineRecentchangeTime);
let RecentTimeUTCMilli = RecentTime.getTime();
let PreviousTime = new Date(AEWorkOnlineWorkPreviousTime);
let PreviousTimeUTCMilli = PreviousTime.getTime();

// find the diff in time between the 2 and convert to seconds (duration field takes seconds)
let MilliDif = (RecentTimeUTCMilli-PreviousTimeUTCMilli);
let SecDif = (MilliDif/1000);

//add the diff to the previous total time then push to total time
let NewTotalTime = AEWorkOnlineTimeTrackTotal+SecDif;
await table.updateRecordAsync(RecordID,{“AE Work Online Time Track Total”:NewTotalTime});

// update the previous status. this takes the item out of the automation view also update the previous time to recent time

await table.updateRecordAsync(RecordID,{“AE Work Online Previous Status”:{“name”:AEWorkOnlineStatus.name}});
await table.updateRecordAsync(RecordID,{“AE Work Online Work Previous Time”:RecentTime});

The useful thing is you can add as many Status as you want by just adding an OR function to the “Current status = Previous status Check” field and adding in additional current VS previous checks. The script will update each status taking it out of the view after updating the timing. You can then duplicate the script for each status you need to update. The reason I do this is at the moment Automations are limited to 25 and I need all the automations I can get for other things i’m doing.

Would appreciate if anyone had thoughts on making it faster or more efficient?

1 Like

It’s great that you were able to code your own solution. It’s also great that you include comments in your code.

I don’t have time to read over your code in detail. However, I did notice two things:

  • you dislike pulling in values from input variables, but the script could be made faster and more efficient by using input variables.
  • at the end that you are calling updateRecordAsync twice for the same record. You could tighten up your code by combining both updates into the same call.

If you would like me to look over your code in more detail or explain how to make these changes, feel free to book an appointment with me. The link is in my profile.

1 Like

Thank you for the feedback Kuovonne, much appreciated. I will get to updating and not being so lazy pulling in all the variables from automation, the autocomplete in the scripting module is great and makes so easy to just type. Cheers