Hi everyone! I’m setting up a membership and events tracking database; I have records for individual Members, each of which belongs to an Office (each Office has several Members), as well as records for individual Events. Our members attend our events, and we need to use the database to track that: how many event has John Q attended? How many people attended x event compared to y event? Which offices have the best attendance records? That kind of thing. I’m struggling with a couple of problems relating to the way the records are connected.
With help from a Knack expert on here several months ago, I set it up as follows:
I have a form which creates “EventMember” records. Every EventMember has a one-many connection with an Event, and a one-many connection with a Member. To track member’s attendance at an event, I select the Event, Member, and Attendance status (Yes or No) and then submit the form. The idea is that I can then count how many Members attended each Event (how many connected EventMember records where Attendance= Yes) and how many Events each Member has attended (again, how many connected EventMember records where Attendance=Yes).
This is partially working. On each Event page I am able to add a view which shows the connected EventMember records, listing each member and their attendance status. I can do the same on the Member pages, as well as the Office pages. But I’m having a hard time counting them. I was able to add a Sum field on the Events records which sums up how many connected EventMember records there are, but for some reason I cannot get it to work for the Members (or their Offices). I have a Sum field which is supposed to count EventMember records connected to that Member where Attendance=Yes, but it doesn’t work; it says the sum is 0 for every Member, even though I know that those Members have connected EventMember records (I can see them displayed on the page when I view it!). What am I doing wrong?
My second question is about pulling dates. Every Event has a date field. The EventMember record also has a date field–that’s because I’d like to be able to order them by date when they display on Member’s pages. How do I get the EventMember record to automatically set the date field to the same date as the Event that’s connected to it?
Thank you for your help!