Returning record data
  • 04 Oct 2022
  • 6 Minutes to read
  • Dark
    Light

Returning record data

  • Dark
    Light

There are several formula functions that can be used to return data about individual records. This article will cover the basics of how they work, while the next article will dive in a bit deeper into different ways to leverage them.

This article is part of a guided course which you can view here.

Created time: a field and formula

“Created time” is a field type that will automatically show the date (and optionally, time) that a record was created—and it's also a formula!

1500000448442rrd-1.png

When used as a formula, created time can simply be entered as CREATED_TIME(). This may be one of the most straightforward formulas we have, as there are no other functions to add or adjust.

1500000456841rrd-2.jpg

As with the date field, you can customize the date format, choose to include a time stamp, and choose whether to use the same time zone for all collaborators. Note that since the created time for a record will always be the same, there is no way to directly edit the contents of the cells in the created time field (just like other computed fields!).

If you want to use created time in conjunction with other text, you'll need to wrap the function with some date formatting in order for it to display properly. You can find a lot more information on working with dates in other articles, but let’s take a look at a short example to familiarize you with the process.

Let's say you wanted to display a message along with the created time of a record, something like "This record was created at CREATED_TIME()." To output that, you would write this formula: "This record was created on " & CREATED_TIME(). However, when you combine an output of plain text along with a computed date function, you'll get the result below.

See the flashcard below for the result of this formula and a solution to fix it.

1500000448462rrd-3a.jpg
"This record was created on " & CREATED_TIME()

Last modified time: another field and formula combo

Similar to created time, the last modified time function exists both as a field type and as a formula function.

When used as a field, the Last modified time provides a visual selector to choose whether to display the time either the record or a specific field was modified.

360101547173rrd-4.jpg

When used as a formula function, LAST_MODIFIED_TIME() can be customized to make the same selection within the formula itself. To output the last modified time of the entire record, just write LAST_MODIFIED_TIME() in the formula field.

360101547113rrd-5.jpg

To designate a specific field, reference the field name within the parentheses like this: LAST_MODIFIED_TIME({Status}). You can comma separate multiple field names as well to output the last modified time of any of the fields, like this: LAST_MODIFIED_TIME({Status}, {Name})

Limitations of last modified time

“Last modified time” may be a powerhouse but it still has its limitations in specific circumstances. In this section, we’ll go through each one to learn about that limitation and how you can work with it.

Referencing other computed fields

The last modified time field type and the LAST_MODIFIED_TIME() function both reflect modifications made by a user to editable fields. They do not reflect changes that occur automatically in computed fields, i.e., any fields where the user doesn't directly edit the cell values and the values are instead computed by Airtable—such as formula fields or rollup fields.

Some computed fields can change values as a result of a user making a modification elsewhere. If the value of a computed field changes when the user makes a modification, then you can use formula fields and different combinations of formula functions to find out when changes were last made that affected that computed field.

Example: if you're trying to figure out when the value in a formula field changed most recently, you can often just include the fields used in the other formula as parameters in the
LAST_MODIFIED_TIME().

For example, one formula field may be DATEADD({Start Date}, 30, 'days'). If you wanted to check the last modified date of that formula field (and really, the {Start Date} field), you should instead just reference {Start Date} itself:

LAST_MODIFIED_TIME  ({Start Date})

Referencing linked records

The value of the last modified time will change if you add or remove a record from a linked record field, but it will not change if the fields associated with the linked record(s) have been changed—for example, changing the primary field of the linked record or any other fields that live in another table will not update the value of last modified time. This is because you have not changed which record you have linked to, even if its values in the other table have been modified.

If you would like to know when the value of a linked record field type has changed, you will need to create a new field in the linked table first—either a last modified time field or a formula field using LAST_MODIFIED_TIME(). You can then use either a rollup with a MAX(values)or a lookup field to pull the last modified time for the linked record(s) into your table.

When used with undo

If you modify a record and then undo that modification (using Cmd+Z ), the value for the last modified time will be the time that the undo action was performed—not for the value of the last modified time that existed before performing the action that was undone.

Another way to think about this is that the value for the last modified time never goes backward in time.

Blank values

When no cells in a record have a last modified time, the value of the last modified time field or LAST_MODIFIED_TIME() will be blank. This can occur for a few reasons.

1. No users have yet made any modifications

When a new blank record is created that has no user-created values, none of the fields will have any last modified times, because no users have yet made any modifications.

2. The record predates the last modified time feature

Last modified time starts tracking from when the last modified time feature was enabled. (For longtime users, this will be sometime in May 2019, when we officially launched this feature.) Any old records that were in your bases before last modified time was enabled will return blank values in a last modified time field or for LAST_MODIFIED_TIME().

In either case, any new modifications made to the records will overwrite the blank value with the date and time of the most recent modification.

3. Blank values causing formula errors

If LAST_MODIFIED_TIME()returns a blank value, and you're using LAST_MODIFIED_TIME()in a formula function that requires a date value (e.g. DATETIME_FORMAT()or TIMESTR()) the formula function will not evaluate properly, which can cause error messages.

You can account for errors caused by blank last modified time values by wrapping your function in an IF statement that checks for errors. For example, instead of TIMESTR(LAST_MODIFIED_TIME()), you could try:

IF(ISERROR (TIMESTR (LAST_MODIFIED_TIME ())),BLANK(), TIMESTR(LAST_MODIFIED_TIME ()))

Knowledge check

Now that you know more than you ever thought you wanted to know about last modified time, why not test your newfound knowledge with some flash cards? You’ll find a question on each card below. When you think you know the answer, click the card to reveal it.

The last modified time function will always output the date (and time) of every record in a table.
😁 Nope! There are a several conditions that won't result in a last modified time being displayed for a record.
Last modified time can reference any other field type and display the time it was last changed.
😮 Nope! Last modified time only reflects modifications made to user-editable fields.
If a record was created prior to our release of last modified time, Airtable won't display anything when that function is used. However, you can manually go back and enter a date if needed.
😎 Nope! No computed field can be manually overwritten.

RECORD_ID()

This function is even more straightforward than the last two, and can only be entered in this format: RECORD_ID(). This function will return the unique identifier of every record in the table (which cannot be altered in any way).

360101547133rrd-6.gif

Record IDs are formatted to start with rec and then a unique string of numbers and letters. You'll notice that when you expand a record, the record ID is part of the structured URL we display in your browser address bar.

4410757532183ExpandedrecordURL.jpg

Formula Foundations

This article is part of a guided course that helps you learn how to use Airtable formulas.

View Course



Was this article helpful?