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?
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!
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!
Wonderful, thanks! Not yet sure if this solves my overall problem, but I will see.
Wonderful, thanks! Not yet sure if this solves my overall problem, but I will see.
Imperfect solution; however, it works entirely within Airtable and doesn’t suffer from lag time or delays possible with third-party middleware:
Create a new table called (for instance) eTimestamp].
Create a formula field in eTimestamp] called {StampedTime}. Configure the formula to be simply CREATED_TIME().
In your main table, define a linked records field that links to eTimestamp]. Name it whatever you would name your checkbox field: {Completed}, {Done}, {Checked}, whatever. Disable ‘Link to multiple records,’
Also in your main table, define a rollup field linking to 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 table and opens a list of 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 nTimestamp] 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 rTimestamp] 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 aTimestamp] table will display only a check mark.
Again, not a perfect solution — but not that bad of one, either.
Imperfect solution; however, it works entirely within Airtable and doesn’t suffer from lag time or delays possible with third-party middleware:
Create a new table called (for instance) eTimestamp].
Create a formula field in eTimestamp] called {StampedTime}. Configure the formula to be simply CREATED_TIME().
In your main table, define a linked records field that links to eTimestamp]. Name it whatever you would name your checkbox field: {Completed}, {Done}, {Checked}, whatever. Disable ‘Link to multiple records,’
Also in your main table, define a rollup field linking to 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 table and opens a list of 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 nTimestamp] 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 rTimestamp] 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 aTimestamp] 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!
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:
“W Vann Hall: Bending Airtable to His Will for a Year (and a Day)”
Recently I discovered that the “Last modified time” field was rolled out.
I used this to my advantage by creating a “Completed” checkbox, and then using a formula to link to when that checkbox was checked.
The formula for my “Completed Date/Time” column is as follows:
I also made sure to turn off the “Use the same time zone (GMT) for all collaborators” feature so that the time reflected correctly for us, as shown below:
I hope this helps!
Recently I discovered that the “Last modified time” field was rolled out.
I used this to my advantage by creating a “Completed” checkbox, and then using a formula to link to when that checkbox was checked.
The formula for my “Completed Date/Time” column is as follows:
I also made sure to turn off the “Use the same time zone (GMT) for all collaborators” feature so that the time reflected correctly for us, as shown below:
I hope this helps!
Thank you so much, this is exactly what I was looking for! Simple and 100% inside Airtable.
Thank again.
Recently I discovered that the “Last modified time” field was rolled out.
I used this to my advantage by creating a “Completed” checkbox, and then using a formula to link to when that checkbox was checked.
The formula for my “Completed Date/Time” column is as follows:
I also made sure to turn off the “Use the same time zone (GMT) for all collaborators” feature so that the time reflected correctly for us, as shown below:
I hope this helps!
To build on this a bit as I think new functionality makes this really easy now. Create a new field using Last Modified Time. You can have that field only look at when one field was updated (in my case Done for my Task List). Now, whenever you update the record by changing the status of Done, you have the Date / Time stamp.