Skip to main content

I’ve set up a base that tracks a specific team’s incoming trouble Tickets. The main Tickets table has a single-select field for Status, which can be ‘Submitted’, ‘Needs further details’, ‘Queued’, ‘In Progress’, or ‘Done’. I’m now working on reporting and management would like to know the full time it takes for ticket resolution, but also how long a ticket remains in each status before progressing.

I can’t decide which of these approaches would be preferable: 

  • Have an automation create a new linked record in a Reporting table with a time stamp when a ticket is created, and then create an additional new linked record on the Reporting table for each subsequent Status update. Then I’d need to do a little more work to compile all of the records on the Reporting table that link to each Ticket (I’m assuming via a bunch of rollup fields). I’m hoping that stamp can also include the name of the Status that’s being recorded
  • Have an automation create a new linked record in a Reporting table with a time stamp when a ticket is created, and have each subsequent Status update appended to that same record via lookup. (Can you have an automation create a new field? If not, I could just create 20 fields to receive date stamps. But how to know which step of the process they represent?)

The workflow for Tickets might skip over one of the Statuses or potentially move backwards and double-up on one of the steps.

I can’t think of a compelling reason to use one method listed above versus the other. Does anyone have any arguments for or against either of them? Or maybe a third option that I’m not thinking of? I’m still fairly new to Airtable & appreciate any assistance. Thanks!

Hi ​@CaitlenBerg.

 

I hope you are doing well. I would definitely suggest to go with options 1. It is not scalable to create  more and more fields of the same type for the timestamps. It will be harder to utilize the data later on if you are trying to create charts or reports. 

 

Your first option is solid and maybe it could use a little more polish. This is what I would suggest:

 

  1. Create an automation that will run whenever the status updates. Depending how your system is set up, you might need to also have this run if you create the record from a form or API where it creates the record with the status already there. (Potentially 2 automations)
  2. Your reporting table should be linked to your ticket table and also it should have a self link to itself, where you would be using as a dependancy. 
  3. The first action in your automation should actually be to find if there is any other reporting records already linked to that ticket record. The idea here is to find the previous reporting status record. You can use the find block, have it filter by the ticket linked record and that the self link its empty. 
  4. Then as a second step, create a new record in your reporting table and link it to the triggering record in your tickets table. 
  5. Then as a 3rd action, you can update the reporting record you found in step 3 and link the newly created record from step 4 into this record’s self linked field. Then using a lookup you can get that records created date to use as the end date.

Essentially every time a status changes, you will create a new record which will have a start date (created date field) and the end date will be empty as its currently in progress, but at the same time your previous reporting record will have this new record linked to it and you will be able to look up its created date field to use as the end date.

 

This approach will allow you to have an unlimited amount of changes of status tracking. It will keep all your status changes and duration neatly in a table that you can then use on charts or reports. I hope that all made sense, if any questions just let me know. Hope it helps!

Yeap echoing airvues, option 1’s the way to go.  The only problem I’ve had using that system is eventually running into the record limits and then having to either delete or migrate the data, but eh, that’s a general issue with Airtable anyway

If you don’t need to know when the statuses were updated and only need to know how much time was spent in each Status it’s possible to do this within a single table and a few formula helper fields too I think

​@airvues  - thanks a lot, that’s very helpful. I think the only piece I’m not sure about is how to include the name of the Status step in the time stamp record. 

As the status is currently a single-select field on the Tickets board, I think I’ll need to have another field that copies the current Status name as plain text. Then use that field in the automation when creating time stamps on the Tracking board whenever the Status is changed. But since the record on the Tickets board will be linked to the time stamp record on the Tracking board, wouldn’t the name of the Status be updated whenever it changes, even on the linked record?

Hi ​@CaitlenBerg ,

 

Sure thing! To keep it simple, you can definitely add a plain text field on your timestamp record that just copies the Status name at the moment the automation runs. That way it won’t change later on. You don’t want to use a lookup in this scenario as it will change with each status change. 

 

If you want to keep it visually consistent with your single-select formatting, you could actually use a single-select field on the timestamp table too, and just have the automation set that value. That keeps everything neat and color-coded.

 

Hope that helps clarify! Let me know if you need anything else.

Terms & Conditions