I am trying to use Knack to track how many members RSVP to and then attend organization events, and also to track which events members RSVP to and attend. I have two groups of objects, Members and Events. Each event is connected, one-to-many, to Members twice–once in an RSVP field and once in an Attendance field. Each Member is also connected to an Office: each Office has multiple Members, but each Member only has one Office.
I’m struggling to do a couple of things with this. One, I would like the page for the Offices to show which events were attended by any of it Members, and which Members those were. I can get the page to display which events were attended by a Member of that Office, but the view displays everyone who attended, not just the ones who are Members of the Office. Can I filter out the Members who belong to other offices?
Second, I would like to generate some reports based on this information. I would like to be able to count how many Members RSVP’d and attended each event, total. I would also like to be able to further specify how each Office did with attendance. I’m having a hard time converting the names of the Members to numbers–I can get it to list how who attended, but I can’t get it to tell me, e.g., 5 people attended.
My apologies if these questions are very basic or if the way I’m describing it is not helpful. I appreciate any advice!
Hey @caitlin, welcome to Knack!
When able, it is best to avoid many-to-many relationships.
Below is a class diagram of how I would set up your scenario, along with a workflow:
- Create an Event
- Invite members one by one with a form that adds a new EventMember, and fetches the Member’s connected Office
- Members indicate whether they can attend of not via another form, which updates the EventMember RSVP Status
- A count field on the Event table indicated the number of connected EventMember records where RSVP Status is ‘Attending’.
- A count field on the Office table indicates the number of connected EventMember records where RSVP Status is ‘Attending’.
Feel free to reach out to me if you’d like some support in setting this up!
1 Like