We’re using AirTable to track actions and filtering the todo list using a checkbox field titled “Completed”.
I’d like to setup a new view that sorts the tasks with most recently completed at the top.
Manually adding the “Completed date” isn’t an option as people will naturally forget to fill it in.
I’ve looked down the list of available formulas but nothing jumps out as being suitable.
Thanks in advance for any help.
There are requests for a “last updated” thing that can be 3xposed using a formula. But it’s not here yet.
Here’s a workaround:
Instead of a check box for “done” create another table called “done”.
On the done table have two fields:
On the task table you can now link to a record on “done” with the auto date.
Hi @Phil_Sheard
There is another way if you use Zapier…
This would only work once for any given record so that any changes to the completion date would need to be done manually. Here’s the zap:
Julian
Hi @Julian_Kirkness,
That’s a great workaround, thanks. I’ve been trying to work this out myself, but I can’t seem to figure out how to update the Date field in Zapier. I see you’re referencing a different field in a record (I’m guessing) in order to add the current date; do you just have another field that updates the current date/time constantly, and, when the zap is triggered, it just pulls that data from the field and adds it to the “Completion Date” field?
Thanks for your help!
Ray
Zapier has a way of entering the current date and time which is to type in:
{{zap_meta_human_now}}
It then looks like this in the Zap:
There’s a Zapier Article here:
Hope this helps!
Imperfect solution; however, it works entirely within Airtable and doesn’t suffer from lag time or delays possible with third-party middleware:
[Timestamp].[Timestamp] called {StampedTime}. Configure the formula to be simply CREATED_TIME().[Timestamp]. Name it whatever you would name your checkbox field: {Completed}, {Done}, {Checked}, whatever. Disable ‘Link to multiple records,’[Timestamp] that rolls up {StampedTime} using an aggregation function of MAX(values). Call it something like {Completed Time}.Now when your users want to flag task completion, rather than checking the {Completed} checkbox, they click on the plus sign in the {Completed} field. This drills through to the [Timestamp] table and opens a list of [Timestamp] records. The user then selects ‘+ Add new record’ at the bottom of the list, creating a new {Timestamp} record. The record opens in a pop-up window, which the user then closes. {Completed Time} rolls up the time at which the new [Timestamp] record was created, providing an accurate — and persistent — indication of the time at which the pseudo checkbox was filled.
Downside: Slightly clunkier than a real checkbox, requiring three mouseclicks (instead of two). As it is based upon calculated dates, some sort of mechanism is needed should manual override be required (for instance, a task is completed, but the user forgets to mark it until the next day). Mistakenly linking to an existing record results in invalid data.
Upside: Purely Airtable solution. Not constrained by third-party middleware limits, polling schedules, or prices. Fast, accurate, and easy to implement.
Bonus: Unlike the Zapier solution, if ‘link to multiple records’ is enabled, the box may be checked multiple times, and {Timestamp} will display the date and time of the most recent check. (See record #5 in the demo base for an example.)
Trick: Configure the primary field of [Timestamp] to be a formula field with a formula along the lines of
'✅ '&{Link2Main}&'-'&{StampedTime}
using the Non-Breaking Space character for the spaces following the check mark. Narrow the width of the linked record field. Now, all records with a link to the [Timestamp] table will display only a check mark.
Again, not a perfect solution — but not that bad of one, either.
So clever. Even though this is not a perfect solution and won’t apply to us, I must solute your creativity.
Brilliant!
Thanks for the very kind words.
Maybe it’s time to start using this as my sig file: