Creating an AirTable Script to Record the Duration of a Single Select Field

[timlines]

I'm intending to create a simple AirTable script to record duration of a single status field. In my use case I'm tracking a large number of issues where the status frequently goes back and forth between the multiple status',

For example there are multiple status':  open, in-progress, and resolved status. A normal workflow would be non-linear, so a record could go from open to in-progress, back to open, then to resolved then back to open, and then finally to resolved again.

open > in-progress > open > resolved > open > resolved.

I'd like to track the duration that each record is in each status as well as the cumulative time each record is in each status. This way I could say this record was open for a period of 3 days, in progress for 1 day and it was  in a resolved status two days. 

In the first table I have several records with a status field and a time modified field as well as other important data fields to help identify the issue, like name and the primary key. As well as a field for each cumulative status duration. 

My second table is a log recording the status and the time it was modified. This table can be used for historical data. It record the time that the status has started on each status and when it has ended each status, as well as the duration in days. 

I'd like to do this whole project with AirTable Scripting. And I have a good start so far, thanks to Ben from the Airtable community. But I still have a little ways to go. 

// read the current status of a record

const table = base.getTable("Item Status");
const records = await table.selectRecordsAsync({
    fields: ["Current Status"]
})
    .then(query => query.records);

let record = records[0];

console.log(record.getCellValueAsString('Current Status'));



// record the last modified time of a record

//const table = base.getTable("Item Status");
const records2 = await table.selectRecordsAsync({
    fields: ["Last Modified Time"]
})
    .then(query => query.records);

let record2 = records2[0];

console.log(record2.getCellValueAsString('Last Modified Time'));

 At the moment I can get both the status and the date modified but now I need to "write" that data for record into my log table. 

Also, a major issue I'm finding is that I'm not able to have the single select option be the trigger for the automation. I'd imagine when I can the status of the field "current status" from Open to In-Progress, this would trigger the automation to run the script. (At the moment I have this setup through an automation, and it works but I'd like to do this via scripting.)

[timlines]

Okay I'm making some progress I can now (kind of) get the values updated in my log table, I have an automation setup so when my single select option is "updated" it will then run this script. 

Problem is, it is only giving me wrong data, instead of giving me the bug flick It's giving me Francis.

// GOAL: If the status button is updated, record the last modified time and the status that it is in in a new table. 

// read the current status and last modified time of a record
const table = base.getTable("Item Status");
const records = await table.selectRecordsAsync({
    fields: ["Issue Key", "Item Name", "Current Status", "Last Modified Time"]
})
    .then(query => query.records);

let record = records[0];

let recordIssueKey = record.getCellValue('Issue Key');
let recordItemName = record.getCellValue('Item Name');
let recordStatus = record.getCellValue('Current Status');
let recordTime = record.getCellValue('Last Modified Time');

console.log(record.getCellValueAsString('Current Status'));
console.log(record.getCellValueAsString('Last Modified Time'));

// add these to a new table
const logTable = base.getTable("Status Log");
let recordId = await logTable.createRecordAsync({
    "Issue Key": recordIssueKey,
    "Item Name": recordItemName,
    "Status": {'name': recordStatus.name},
    "Status Start Datetime": recordTime
});

console.log("Record created!");

[Alexey_Gusev]

Hi,

Your script is a bit incorrect. It uses only first record from query, reading whole table (btw order of records might be random if query unsorted). 
I tried to answer on your previous post, but couldn't understand your goal that time. Now I see.
When you run script from automation, some record supposed to be a trigger (except time scheduled etc.) , on the left from code editor you should define input parameters.
Usually it's record ID, sometimes record name (value of primary field), sometimes array of links, you can even hardcode any static value here.
Then you might use object 'input.config()' to get input data and assign them to variables. Previously it was a wall of useless code like

let config=input.config()
let recordId=config.recordId
let userName=config.userName
let valueCost=config.valueCost

now it looks better:

So, to take correct record from your query, you should pass ID and then find yours by ID:
let record=records.find(r=>r.id==ID)

the better way is to query single record, instead of

const records = await table.selectRecordsAsync({

use

const record=await table.selectRecordAsync(ID)

But indeed, you don't need querying at all, just take your value:

In such cases, I usually use minimum scripting, and create Automations by GUI, if possible. Can't be 100% sure, maybe I missed smth, but it looks like you can use Update action and avoid scripting at all.

[timlines]

• Thanks for your help Alexey. I think this is a good first step but not what I'm looking for. When I ran this, I got the following:
  • Input Variables: 
  • Name: Current Status
  • Value: Current Status
  • Display: Name
    
    
    

const {status_name} = input.config()
console.log(status_name)

When I run this code I get "undefined".  However, if I change the code, removing the curly brackets, I get 

const status_name = input.config()
console.log(status_name)

In my initial question, I'm not trying to only get the status but log how long the status drop down has been in each state. I'd like to answer the following question: How many days was this record in a open state? 

[timlines]

Update: Getting it working. 

Thanks to Alexey I was able to understand working with Input Variables. Here is my current code and it seems to be working to point as I'll explain:

// OBJECTIVE: If STATUS field is updated, record the last modified time and status
// NEW OBJECTIVE: Record the last status' end time in order to calculate duration. Ex. Open for 1 day

const {status, time, key, name} = input.config();
console.log(status, time, key, name);

// add these to a new table
const logTable = base.getTable("Status Log");
let recordId = await logTable.createRecordAsync({
    "Issue Key": key,
    "Item Name": name,
    "Status": {name: status},
    "Status Start Datetime": time
});

console.log("Record created!");

As it works, the status field can be changed and it will now appropriately add the proper information to the second log table. 

The log table is looking great, correctly updating the name, key, status and time for whatever field is changed. 

But now for the tricky part. I need to go back into the log and add in the end time for the previous status log when the new status is updated. So if the status is "Open", there should be a start time and an end time. 

For example:

RECORD	STATUS	START	END	Duration
Bug-Flick	Open	March 1	March 2	1 Day
Bug-Flick	Needs more info	March 2	March 4	2 Days
Bug-Flick	Open	March 4	March 5	1 Day

After I can caluate the duration in each status I can add those together, so in this example this record "Bug-flick" was Open for 2 days, from March 1 to March 2, and reopened again on March 4 to March 5th. 

RECORD	STATUS	TIME IN STATUS
Bug-Flick	Open	2 Days
Bug-Flick	Needs more info	1 Day

[Alexey_Gusev]

Hi,

I see that you were able to find out that input variable names (being the 'properties' of input.config() object)  which you defined in left panel, should match their names in code.

Now, about your question - how to 'close' previous opened record by setting 'end date'. You can do it by code or in GUI, without code, using 'Find records' and 'Update'. By way, with 'Create record' you can do exactly the same action that written in your code. But since you decided to perform it by scripting, I can give an advice.

I think, before creating new record, you can query existing set sorted by 'start date' descending. So, the newest record will be first  in query results, and you can retrieve it as query.records[0]. Then you close it by updating 'end date' with value of 'time', the same 'time' which you then put as starting time in new created record.
Very Important to follow order (sort & find newest record) - (update it with end time) - (create new record in log) otherwise newest record found by sort will be your new created.

that piece of code, inserted in your script, should work. or you have to debug if something wrong

const logTable = base.getTable("Status log");

// insert these 5 lines below
const query=await logTable.selectRecordsAsync({fields:[],
    sorts:[{field:'Status Start Datetime', direction:"desc"}]
  })
const previous=query.records[0]
await logTable.updateRecordAsync(previous.id,{'Status End Datetime':time})
//end of insertion

let recordId = await logTable.createRecordAsync({
    "Issue Key": key, ...

[timlines]

Got it, since I'm dealing with multiple records, in this case a number of bug tickets, I'll need to filter the query first to make sure that I'm only looking at the log files for a single bug ticket. Looks like I found a filter right here that might help. I'll test it out an post any questions.

[timlines]

Okay making more progress, this is what I have so far, I'm sure there's a way to clean this up, but right now it will check the current logs and update the last recorded bug log for that specific issue recording the end time for that status and then it will create a new log recording the new start time for the next opened status. 

// OBJECTIVE: If STATUS field is updated, record the last modified time and status
// Record the last status' end time in order to calculate duration. Ex. Open for 1 day

const {status, time, key, name} = input.config();

// add these to a new table
const logTable = base.getTable("Status Log");

// query records in the log table and sort


const query = await logTable.selectRecordsAsync({fields: ['Issue Name', 'Issue Key', 'Status Start Datetime', 'Status End Datetime'],
    sorts:[{field: 'Status Start Datetime', direction:"desc"}]
});

let filteredRecords = query.records.filter(bug => {
    return bug.getCellValueAsString('Issue Key').includes(key)
});

const previous = filteredRecords[0];

await logTable.updateRecordAsync(previous.id, {'Status End Datetime': time});


let recordId = await logTable.createRecordAsync({
    "Issue Key": key,
    "Item Name": name,
    "Status": {name: status},
    "Status Start Datetime": time
});

console.log("Record created!");

[timlines]

Okay update on this, my duration formula seems to be working so the next step is to add up the duration of each record in the log that meets the given criteria. For example, I want to know how many days total my record had the status of 'open', in this case I'm using seconds (because why not, and testing) so I'll just add up each time bug-12345 was in open status and get the total time it was in open. Note, Airtable already does this when I group things together but for whatever reason I'm not able to actually get the number out and store it in column, so I'll great a column for each status and say "Time in Open" 

I'll log this information in the first 'status' table.