How to create a table that reflects changes over time?

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:

   SUM(IF(values='',0,1))
   

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:

   if('cards_out'>0,'checked-out','returned')
   

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

IF(
    AND(
        {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

IF(
    {Date Checked Out},
    {Student Name}&
        " checked out card on "&
        DATETIME_FORMAT({Date Checked Out},'M/D/YYYY')&
        IF(
            {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.