Re: How to create a table that reflects changes over time?

Chrystal_Fizer · ‎Jan 02, 2019

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?

bdelanghe · ‎Jan 03, 2019

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’

bdelanghe · ‎Jan 03, 2019

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

In the [Students] table create a rollup field ‘cards_out’
Select [Log] and then ‘Date Returned’
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:

Create a formula field ‘Access Status’

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)

Chrystal_Fizer · ‎Jan 07, 2019

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.

W_Vann_Hall · ‎Jan 07, 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.

Chrystal_Fizer · ‎Jan 10, 2019

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.

W_Vann_Hall · ‎Jan 10, 2019

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.)

Chrystal_Fizer · ‎Jan 10, 2019

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

W_Vann_Hall · ‎Jan 10, 2019

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.