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

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:

Screenshot 2021-10-07 005906

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

Welcome to the community, @Yaine_Tobias! :smiley: 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.

Screen Shot 2021-10-10 at 2.23.24 AM

(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?

1 Like