How to create a table that reflects changes over time?


I am managing access to a building. Each access card has a number. Each student is given a card when they start working here and they return it when they leave. The access cards have to be activated for each person so I need to keep track of who has each card and for how long so that I can know when a card is available to be transferred to another user.

I already have a student table. My problem is figuring out how to do the access card table. At first I started with the following columns in the Access Card table:
Card #
Student Name (linked-allowing multiples)
Date Checked Out
Date Returned

However, I don’t think this will work once I get multiple students for the same card. How will I keep the dates tied with the right student?

Then I decided to keep the dates with the student instead of the card. So as of right now, I have added “Access Checked Out” and “Access Returned” columns to the student table. So the Access Card table looks like this:
Card #
Student Name (linked-allowing multiples)
Date Checked Out (lookup field)
Date Returned (lookup field)

I still don’t know if this will hold up once I start having multiple people on the same access card. I just started this position so the Access Card table is being built as students turn in their access cards. It was not well organized before so I don’t have a master list of cards.

Basically I need a table to track the life cycle of these access cards. I need to know when they come and go over time. Is there an easy way to track this?


You are so close. What you need is a separate log table:

Date checked out
Date Returned
Student Name (single link)
Card # (single link)

This should be the only table that you have to make frequent edits. And both ‘cards’ and ‘students’ can have rollups so that you can check if a student has a card out or that a specific card is still out.

If you want to get a little more advanced:

I’d suggest adding a formula for the key value instead of using the date checked out so that each one is unique try something like:

{student name} & ' : ' & {date_checked_out} & ' - ' {date_returned}

This would look like ‘Billy : 01/10/2019 - 01/12/2019’


Instead of doing this manually you can do it with a rollup. A basic a way is:

  1. In the [Students] table create a rollup field ‘cards_out’

  2. Select [Log] and then ‘Date Returned’

  3. Enter a formula will count the return dates that are blank:


You can do the same for the [Cards] table as well. Also if you want to get fancy:

  1. Create a formula field ‘Access Status’

  2. Set the formula to:


I suggest not having an ‘access returned’ field because this will always be the opposite of ‘Access Checked Out’ (after the first time them check out the card)


Thanks for your help, I completely forgot about bridge tables. However, when I try to set up the formula for the primary key, it displays the full date and time even though the column I’m referencing doesn’t collect the time. How do I make the formula display just the calendar date. Here is my formula:

{Student Name}&" checked out card on "&{Date Checked Out}" and returned it on "&{Date Returned}

Edit: It also returns the date in European format. Here’s an example of the one of the primary keys:

Damin Xia checked out card on 2019-01-07T00:00:00.000Z and returned it on

I want the date to just say 1/7/2019.


See the documentation on DATETIME_FORMAT(), especially the format specifiers it supports. DATETIME_FORMAT(), as you might guess, takes a datetime value and returns it as a string formatted in a specific way as determined by the format specifier provided. For instance, the syntax you want is

DATETIME_FORMAT({Date Checked Out},'l')

(that’s a lower-case L) or

DATETIME_FORMAT({Date Checked Out},'M/D/YYYY')

Either will give you the date as you desire.


I tried that. I used this:

{Student Name}&" checked out card on "& DATETIME_FORMAT({Date Checked Out},'M/D/YYYY') & " and returned it on " & DATETIME_FORMAT({Date Returned},'M/D/YYYY')

But I get an error message.


That looks good to me. Double-check both {Date Checked Out} and {Date Returned} to make certain Airtable sees them as dates. (Easiest way is to configure the field and make sure it allows you to format it as a date. Under some circumstances, formula, lookup, and rollup fields may not always be interpreted as a date value.)


I can’t figure out what I’m doing wrong. This is a shot of my table showing the date fields.


Oh – got it.

What you’re doing wrong is logging {Date Checked Out} and {Date Returned} in separate tables, rather than as separate fields in the same table. For one thing, it will be very difficult to correlate the checked-out date with the returned date (performing multi-record calculations is far from intuitive in Airtable); for another, attempting to execute your formula with either date field missing will generate the error message you are seeing.

If you want to inhibit the error message — for instance, after a card is checked out but before it’s returned — you can wrap it in an IF() statement. The simplest version would be something like

        {Date Checked Out},
        {Date Returned}
    {Student Name}&
        " checked out card on "&
        DATETIME_FORMAT({Date Checked Out},'M/D/YYYY')&
        " and returned it on "&
        DATETIME_FORMAT({Date Returned},'M/D/YYYY')

That would simply leave the field blank unless both dates were filled.

Perhaps more useful would be

    {Date Checked Out},
    {Student Name}&
        " checked out card on "&
        DATETIME_FORMAT({Date Checked Out},'M/D/YYYY')&
            {Date Returned},
            " and returned it on "&
                DATETIME_FORMAT({Date Returned},'M/D/YYYY')

This latter formula would show both returned and not-yet-returned cards and the responsible students.