Welcome to the community, @Yaine_Tobias! :grinning_face_with_big_eyes: 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 oMeetings] 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 mMeetings] 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?
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!
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!
@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.
