3rd Object Connections

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.

Okay - perhaps there's a need for another object that links Events and Clubs - Attendance.

The Attendance object is connected to Events (one-many), and to clubs (one-many), and to Organisation (one-many).

Each Event then can list clubs attending via the Attendance records, and the Organisation can do the same. You choose what's set automatically within the forms (the Organisation for example).

Likely to be other ways to achieve this of course Stephen!

Same thing happens when I tried to add a connected "Event List" field to the Organization object. Knack can set that field to whatever it wants initially, but has trouble adding additional events to what's already there. The only option is to set the value, not add to what's already there.

Thanks Brad, that's very close but not quite what I need. While your answer works great for assigning 1 organization to the event in the new Org field, I need to be able to assign multiple organizations in case Clubs from different Organizations attend the same event. And changing the one-many connection to many-many removes the option in the record rule to add a connected field. The options just drop to "a custom value" and "a form value".

Hi Stephen,

I think this is what you're after... an organisation view with a table or list view that lists all the Events connected to that Org via the Club connection?

I suggest:

  1. Add an Organisation connection to the Event object (one-many)
  2. In the 'New Event' form, add a record rule that updates the Organisation field to the connected field of Club > Organisation. And remove that field from the form so users don't need to fill it in manually.
  3. Now add your Events table to an Organisation view.

Sing out if that's not what you're after.