Attendance checklist (is there a way to automate a checkbox from multiple linked records?)

[Yaine_Tobias]

So my first table is like this:

The “Attendees” column was just pasted from a Zoom CSV report. It’s linked to another main “Client Directory.” That’s the easy part. Now I want to see the attendees’ individual track record via checklist like this:

It makes more sense visually, but I have to manually check each box and each record. Is there a way to automate these checkboxes? Like if Orla shows up as an Attendee from the first table, it would automatically check off the Oct. 27 column on my attendance checklist?

Or is there a better way to do this? I’m dealing with at least 60 attendes for 4 meetings each month. I want like a dashboard of sorts where I can easily see who has been attending or not.

Would appreciate any help. Thanks

[Justin_Barrett]

Welcome to the community, @Yaine_Tobias! Only scripts or automations can change the state of a checkbox “behind the scenes,” but I wouldn’t recommend setting up either one for something like this. I’d actually lean toward a slight design change.

First off, you’ll need a field in your [Meetings] table (guessing the name) to create a formatted version of the date (I’ll explain why in a bit):

IF(Date, DATETIME_FORMAT(Date, "MM/DD/YYYY"))

Over in your [People] table (again guessing the name), I suggest changing the checkbox fields to rollup fields, rolling up this formatted date based on the links coming from the [Meetings] table. Using the {Oct. 27} field as an example, you would use the following aggregation formula:

IF(FIND("10/27/2021", ARRAYJOIN(values)), "✅")

As you link to employees in meeting records, the formula will automatically add checkbox emojis where needed on each employee’s record.

For the other date fields, you can copy an existing rollup field and change the date string that the FIND() function is seeking.

(Here’s why we need formatted dates. When rolling up actual date fields, Airtable automatically converts the dates to strings, but not nice-looking strings. Also, those strings are based on the raw datetime data, which is based on GMT. Formatting the dates as text before rolling them up works around those issues.)

With all of that said, I’m curious about setting up your table with date-specific fields. Do you plan to keep these fields around for a long time, or do you periodically delete them as new date fields are added?

[Yaine_Tobias]

Hi Justin, I’m sorry for the late response, but I am so thrilled to see your reply! To answer your question, I plan to keep these fields around for a long time.

The rollup fields for the checkbox make so much sense. I appreciate you writing the formula!

Just to update you, I have 3 main tables right now. First, the main “Client Directory”

The second table is the “Meetings” table, the “Attendees” column links back to the “Client Directory”

Lastly, my “Attendance Checklist” – the formula with the checkbox emoji works great!

My one last question is, would it be possible to automate the “Meetings” column here so that when I update my 2nd table (Meetings), it automatically links to this 3rd table to the appropriate client/attendee?

I truly appreciate your help on all of this. Thank you so much!

[Justin_Barrett]

@Yaine_Tobias My gut feeling is that duplicating the clients as the primary field in two tables is overkill. What I think would be more effective is to put all of the meeting date rollup fields in the [Client Directory] table instead of a completely separate table. Create a new “Attendance Checklist” view that only shows those rollup fields.