I am trying to create an app to track member participation for a community group. Each member belongs to a specific sub-group, and there is a weekly schedule of recurring meetings, where specific meetings are open to specific sub-group.
The data is structured as follows:
Table: Members (MemberID, Name, Group, other fields…)
- M1, John Smith, Group A
- M2, Jane Smith, Group A
- M3, Bob Jones, Group B
- M4, Sally Brown, Group C
Table: Schedule (MeetingID, Meeting Name, Group, Day, Start Time, End Time, other fields…) This data rarely changes and is more of a control table providing values for other views.
- S1, Planning Meeting, Group A, Monday, 2 PM, 3 PM
- S2, Design Meeting, Group B, Monday, 3 PM, 4 PM
- S3, Membership Committee, Group C, Wednesday, 5 PM , 6 PM
Table: Attendance (ID, Date, MeetingID, MemberID)
- 1, 8/31/2020, S1, M1
- 2, 8/31/2020, S1, M2
- 3, 8/31/2020 S2, M3
- 4, 9/2/2020, S3, M4
I have the view and form for Members no problem - very straightforward, including a text field (not a ref field) using Valid_If to control the possible values for Members[Group]. What I can’t figure out is how to build the form for Attendance. Ideally, I’d like to be able to select a Meeting Name, a Date, and then a multiselect from a list of members who are elligible to attend the meeting. When I built the Attendance table and added it as a Form view, I got this (after a bit of tweaking):
I have two issues:
- How do I create a multiselect for Attendees that:
- DISPLAYS the Schedule[Meeting Name] values
- STORES the Schedule[MeetingID] in the Attendance Table AND
- FILTERS the list of Members based on the Schedule[Group] value for the selected Meeting
In the Attendance Table I need to store the MeetingID, not the Group. And I’m confused between the use of an EnumList with a Base of Ref versus a Ref field…
- Ideally, I would like for the Attendance Form to create multiple rows in the Attendance Table when the Form is saved - one row for each Attendee. This way we can report on attendance for each Member individually.