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
}else{
/// 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?
