Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: default value date field

Solved
Jump to Solution
3923 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Alex_Ma
6 - Interface Innovator
6 - Interface Innovator

I know this has been raised before as a feature request but is there a way to add a default value to a date field through automations or some other mechanism other than pleading airtable to implement or a third party add-on? Thanks in advance

2 Solutions

Accepted Solutions
Alex_Ma
6 - Interface Innovator
6 - Interface Innovator

@Ben_Young1 Makes sense; how do I set the date field to today? I can only choose static or dynamic and neither option gives me a choice for a formula like always set to today. Thanks

See Solution in Thread

So, there are three ways I think of doing this.

All of them are incredibly straight forward.

To help run through these methods, I've gone ahead and created a simple table with just a date field.

Ben_Young1_0-1675262341863.png

Method 1: The Trojan Field 

I don't feel strongly for or against this method. It kinda meets the best of both worlds.
For this method, simply create a Created time computed field in your table.
From there, just reference it in your automation when you need to pull today's date.
It somewhat satisfies the desire to not have a bloated schema with a supplementary field (I'll touch on this in method two), as it can serve other purposes and can be useful to users to be able to reference for each record.

Method 2: The Formula Route (Not Recommended)

For this method, I'll create a single formula field with only the TODAY() function inside of it.
This field can now be referenced in the automation to provide your default value to be written to the Date field.

Ben_Young1_1-1675263869964.png

I'm not a fan of this method. If you're curious to know why, feel free to keep reading, otherwise you can skip straight to Method 3.
At the core of best practices, a record should only store data that is relevant to that individual instance of a record.
What does that mean?
In a database, you want each table (object) to contain records about a single type of thing.

For example:
A table for your team members will contain a single record for each unique team.
Your table should contain fields that only store information about your team member.
Have a group of projects that your team member is working on? That project information belongs in a separate table that then relates to your team members.

Why am I going on about the fundamentals of database planning?
Because having a formula field that returns the current date is not relevant to the record, even if it's there to facilitate a helpful automation.

When you create supplementary fields like this, you incur technical debt and create an avoidable dependency in your schema. You should always avoid as many non-functional fields as possible, as they make your records easily readable and force users to learn to disregard many of the fields present in a table.

Naturally, the question now shifts to: "If we don't want to create this formula field, then what should we do?"
With that, we arrive at...

Method 3: The Scripting Route (Recommended)

Leaning on scripting here is highly recommended because it removes a dependency on a supplemental field and creates a really nice abstraction layer to handling the creation of the default date value.

It's also quite literally the shortest script I've ever written in Airtable.

First, configure your automation with your desired trigger.
I've done as I previously recommended and set my trigger to fire on record creation.
Next, I created a conditional action group that only runs if the date field has been left empty.
Within that conditional action group, I created a script that contains only the following code:

output.set("dateToday", new Date().toLocaleDateString());

When tested, the following will be returned:

Ben_Young1_2-1675264278338.png

This is now a value that will be available to you within the scope of the automation.
That's all you need to do for the script. You won't need to configure any input variables, so if you're confused about what the input variables do, there's no need to worry.

Next, I created an Update Record action that will update the triggering record and set the action to update the date field with a dynamic value and fed the output value from the script to the date field.

Ben_Young1_4-1675264852317.png

To test, I'll simply create a new record and leave the date field blank.

Ben_Young1_5-1675264911242.png

Ben_Young1_6-1675264942527.png

 

As expected, the date is automatically filled in to the current date if the date field is left blank.

 

See Solution in Thread

3 Replies 3

Hey @Alex_Ma!

This should be a pretty straightforward automation to create.
You'll want to create a single automation that is triggered on a new record creation.

From there, create a conditional action group that will only run when the created record's date field is left empty.
If the date field is blank, then configure a single record update action that fills in the field to the default value that you'd like to fill in.

Alex_Ma
6 - Interface Innovator
6 - Interface Innovator

@Ben_Young1 Makes sense; how do I set the date field to today? I can only choose static or dynamic and neither option gives me a choice for a formula like always set to today. Thanks

So, there are three ways I think of doing this.

All of them are incredibly straight forward.

To help run through these methods, I've gone ahead and created a simple table with just a date field.

Ben_Young1_0-1675262341863.png

Method 1: The Trojan Field 

I don't feel strongly for or against this method. It kinda meets the best of both worlds.
For this method, simply create a Created time computed field in your table.
From there, just reference it in your automation when you need to pull today's date.
It somewhat satisfies the desire to not have a bloated schema with a supplementary field (I'll touch on this in method two), as it can serve other purposes and can be useful to users to be able to reference for each record.

Method 2: The Formula Route (Not Recommended)

For this method, I'll create a single formula field with only the TODAY() function inside of it.
This field can now be referenced in the automation to provide your default value to be written to the Date field.

Ben_Young1_1-1675263869964.png

I'm not a fan of this method. If you're curious to know why, feel free to keep reading, otherwise you can skip straight to Method 3.
At the core of best practices, a record should only store data that is relevant to that individual instance of a record.
What does that mean?
In a database, you want each table (object) to contain records about a single type of thing.

For example:
A table for your team members will contain a single record for each unique team.
Your table should contain fields that only store information about your team member.
Have a group of projects that your team member is working on? That project information belongs in a separate table that then relates to your team members.

Why am I going on about the fundamentals of database planning?
Because having a formula field that returns the current date is not relevant to the record, even if it's there to facilitate a helpful automation.

When you create supplementary fields like this, you incur technical debt and create an avoidable dependency in your schema. You should always avoid as many non-functional fields as possible, as they make your records easily readable and force users to learn to disregard many of the fields present in a table.

Naturally, the question now shifts to: "If we don't want to create this formula field, then what should we do?"
With that, we arrive at...

Method 3: The Scripting Route (Recommended)

Leaning on scripting here is highly recommended because it removes a dependency on a supplemental field and creates a really nice abstraction layer to handling the creation of the default date value.

It's also quite literally the shortest script I've ever written in Airtable.

First, configure your automation with your desired trigger.
I've done as I previously recommended and set my trigger to fire on record creation.
Next, I created a conditional action group that only runs if the date field has been left empty.
Within that conditional action group, I created a script that contains only the following code:

output.set("dateToday", new Date().toLocaleDateString());

When tested, the following will be returned:

Ben_Young1_2-1675264278338.png

This is now a value that will be available to you within the scope of the automation.
That's all you need to do for the script. You won't need to configure any input variables, so if you're confused about what the input variables do, there's no need to worry.

Next, I created an Update Record action that will update the triggering record and set the action to update the date field with a dynamic value and fed the output value from the script to the date field.

Ben_Young1_4-1675264852317.png

To test, I'll simply create a new record and leave the date field blank.

Ben_Young1_5-1675264911242.png

Ben_Young1_6-1675264942527.png

 

As expected, the date is automatically filled in to the current date if the date field is left blank.