Linked Records Completed Based on Lookup Field from another Table

[KEVIN_BERGER]

I have a table for meetings that is linked to a Personnel table and an attendance confirmation table that is also linked to the Personnel table.

When a meeting is submitted, an email is sent to each linked personnel record with a form. The form is prefilled with the meeting ID and the Personnel's name. When they submit, it shows that they attended the meeting.

I'm trying to find a way - within the meeting table - to show what personnel (linked in the meeting table) have not completed an attendance form using a lookup into the attendance table to find the personnel name.

Any ideas are greatly appreciated!

[TheTimeSavingCo]

Hmm, I feel like you might need a script to do this I'm afraid, and the script would compare the rollup of attendees vs the linked field of people who are supposed to attend

What if we changed the workflow around?  So you'd add Personnel records to the linked field in Meetings to say who is supposed to attend, and based on those attendees you'd create one record per Personnel <> Meeting mix in "Attendance" like so:

Then your form would be in a new table called "Form Submissions" or something with a linked field to the "Attendance" table.  The form would have a prefilled form URL to the person's record in "Attendance" and a checkbox to mark their attendance and would look like this:


This setup would give you the ability to create a lookup field in your Meetings table that shows the people who haven't attended:

Link to base

[pressGO_design]

It sounds like your form submissions currently populating your Attendance table, which means that you’re looking for records that aren’t there. If you want to track people who don’t submit the form, you need a separate table that takes in the form submissions so you can compare that to your Attendance table. But you don’t need that table to be linked to anything - you can use automations to update the Attendance table when a form comes in (and then you can periodically trash all the records in the form submission table without losing the attendance data.

I would set this up so that there is a form to add a meeting where people can choose linked Personnel records to add invitees to a meeting. I would set up an automation that is triggered by that form submit (which goes into the Meetings table) and uses the Repeating Loop condition to

(1) create a record in the Attendance table with
   (a) a linked field for the invitee
   (b) a linked field for the meeting
   (c) a formula field that creates a link to a personalized attendance form that, when submitted, will go to your newly created Form Submissions table
   (d) a checkbox that is blank
   (e) a formula field that creates an attendance ID of some sort
(2) send an email to the invitee with the link to the form that includes their name, the meeting, and the attendance ID

When the invitee submits the form, that submission triggers an automation that finds the record in the Attendance table by a condition (where attendance ID = attendance ID) and updates that record by checking the checkbox.

Then, in the Meetings table, you can conditionally look up invitees whose linked attendance records are not checked. Or, if you have the new dynamic filtering of linked records option available to you - it hasn’t trickled down to me yet ☹️ - you might be able to use that? Although, since I don’t have access to that yet I’m not sure if it will work. 

Base link to come bc I’m on mobile rn. 

Edit: base link

Meeting form (this kicks everything off)

[KEVIN_BERGER]

I apologize for the delayed response, I implemented this and was needed for an emergency project that took some time to resolve.

I created a table that I called "Attendance Seats". To record a meeting, initial training, or follow-up training ("Meetings" table, "Initial Training" table, and "Follow-Up Training" table respectively), a form for each table is used; in which, personnel attending (a linked field to the "Active Personnel" table) are selected.

Upon submission, a record is created, triggering an automation (one for each table, each with the same process) that finds the active personnel tied to the Event (which will be how I refer to meetings, initial trainings, and follow-up trainings from now on). A "repeating group" then creates a record in the "Attendance Seats" table, and is followed by an email with a URL to confirm or deny attendance at the event.

The URL is a form for the "Attendance Confirmations" table; it is prefilled with the Attendance Seat RECORD_ID and the Attendance Seat linked record field is hidden from the submitters view. This leaves them only two fields: a single-select field with the option to decide "Attended" or "Denied", and a long text field for comments.

Upon submission of the "Attendance Confirmation" form, an email is sent to the Event's host. Additionally, there are 3 roll-up fields in each of the 3 events tables. One of these finds the personnel name for all attendance seats where the selected confirmation of attendance is "Attended", another finds the personnel names for all attendance seats where the selected confirmation of attendance is "Denied", and the last finds the personnel names for all of the attendance seats with no attendance confirmation.

The "Attendance Seats" field in the "Attendance Confirmation" table does not allow for multiple records to be linked, and filters the selection (using the new feature mentioned by pressGO_design) to attendance seats where an attendance confirmation does not already exist; this ensures that the unique form sent to the attending personnel's email cannot be resubmitted (at least not without altering the URL, which I don't have to worry about at my place of business).

Concerning follow-up trainings, when an initial training is created, the "Follow-Up Due Date" [formulated field using

DATEADD({Created Time},7,'days')

) is updated. I've created an automation that finds the initial training records "where the {Follow-Up Due Date} is today", sends an email to the host of the initial training with a URL for the "Follow-Up" form that prefills the "Initial Training" linked record field with the applicable initial training's RECORD_ID (hidden to prevent alteration) and the "Personnel Attending" linked record field with "Attending Personnel" from the initial training (not hidden to permit the alteration as necessary), and then sends an email to the personnel attending to notify them of a scheduled follow-up training for the day.

I owe a big thank you to TheTimeSavingCo and pressGO_design for their great assistance! I hope someone out there with a similar use-case finds this thread as helpful as I have!