I have 3 tables- Organizations, Clubs, and Events. Each Club is connected to a 'parent' Org record in the Organizations table, in a many Clubs per Org, only one Org per Club arrangement. The Event object is also connected to both the Organizations and Clubs objects, so that individual Clubs and/or Organizations can be part of an Event record.
I also have 2 user account types, Organization Staff and Club staff, also with connections to the Org and Club objects, set up so that Club staff can see their own Club information and events, and Organization staff can see details on all Clubs connected to their organization. When the user is Club staff, one Club is entered in the Club field, and when they're Org staff, one Organization is linked in the Org connection field.
I need to create a table that contains all the event records of Clubs connected to an organization staff's Org, so that Org staff can sort through them all in one place rather than having to look up each Club individually. It would look like "Events connected to Clubs of the Logged-in User's Organization". However, the only options I see are to add "Events connected to the Logged-in User's Club", which references the blank Club field in an Org Staff's account record or "Events connected to the Logged-in User's Organization", which will only show events connected to the Org itself, not any of the Clubs in the Org.
Sorry if that's long winded, but I've really hit a stump here. I don't know how else to do it, other than adding in every Club in the Org manually to the Org Staff's account record in a new "Org's Clubs" connection field, which defeats having Organization records in the first place. Am I completely missing something here? Thanks in advance for any help.