Automatically add a date stamp

I use Airtable to keep track of my service tasks at work. We are watchmakers and we repair watches.
So, I would like to record the date when a job is finished by us, and another when the customer picks up the watch. The intervals between the start, finish and pick up are very useful to us!
I have used Zapier to export a field to a new Airtable form, via a Google calendar event, to make a time stamp. This works well, but adding another time stamp to the same original listing has proven very difficult to accomplish.
Any help in much appreciated!

It’s hard to say without seeing your current base, but typically ticketing systems use transaction records associated with the ticket to track intervals. (For instance, if you use dedicated in, repaired, and out datetime fields, what happens if the customer comes to pick up the watch and says, ‘Hey — this isn’t fixed.’ If you overwrite the original fields, you lose visibility to the time the watch has already been in your shop; if you open a new ticket, you loose easy access to documentation of what work has already been performed and you lose sensitivity to the watch already having passed through one unsuccessful repair cycle.) (Of course, maybe this isn’t a common issue regarding watch repair — but I cut my teeth on telephone lines and data circuits, and I can assure you it’s not uncommon there!) By using a discrete record to track each movement (or status change) for the watch, you can easily tally how much time it’s spent in each status.

I know I used a similar mechanism in my Wardrobe Manager base in Airtable Universe — but, frankly, it’s been so long since I was in that base, I’m not sure if it’s coded as cleanly as I would today. Still, you should be able to get an idea of how I’d architect the solution — and the base is well (or is that ‘overly’?)-documented. Examine the base on Airtable Universe: You’ll want to look at how the [Out] table is implemented. The base also contains a [Documentation] table, which includes a detailed users guide stored as a PDF attachment: Look for the section on handling referrals or ‘outs’.

Thank you so much! I will dig into this in a while, and get back to you with the results!

One quick and Airtable-ish solution might be to leverage the ability of the new last modified time field and its matching formula to take its cue from or more specific columns. For instance, you could set up:

  • A “finished” checkbox column and a modified time column that only tracks changes to “finished”;
  • A “picked up” checkbox column and another modified time column that only tracks its changes.

That would give you time stamps for both; note this is not exactly a “resolved” time stamp but more like a “status change” time stamp, as it would also change when someone unclicks the checkbox again (depending on your workflow, this might be good or bad); also, modified times are not rolled back on undo, so there is a margin for user error. On the upside, this works entirely inside Airtable without base linking trickery, complicated formulas or recourse to external services.

2 Likes

Good points. It still doesn’t get around the Zapier limitation on not triggering on records when they re-enter a view – but for an Airtable-only solution that doesn’t need the ability to walk back a status — which, frankly, applies to most processes that don’t touch the customer directly — it’s an excellent solution.

—and it points out I need to update the Wardrobe Manager base (among others) to take advantage of the new last modified time tracking abilities. A lot of the baroque flavor of the [Out] table is the result of trying to fake a last-modified status; I suspect I can now streamline some of the processing. Thanks!

A slight improvement to my solution that ensures only checked boxes get a time stamp is to use a formula that checks the status of the checkbox instead of a last modified time column, i.e.

IF({checkbox field} = 1, LAST_MODIFIED_TIME({checkbox field}))

This will result in an empty timestamp value for unchecked boxes; it doesn’t actually correct wrong timestamps from editing errors, but it reduces the noise caused by them.

2 Likes