1 of 1 people found this helpful
I would use multiple tables to accomplish this, and also ditch the checkboxes as they can't display what you want.:
Trip --< TripAttendees >-- Attendees (many to many)
Trip --< Stops (one to many)
Stops --< StopAttendees >-- Attendees (many to many)
This would allow me to assign multiple people to a "trip", keep a list of "stops" for itinerary, and then assign multiple people to each "stop".
To add people to stops, I would create two relational value lists:
-List of stops related to Trip
-List of attendees related to Trip via TripAttendees
Each value list would use the primary ID as the first field, and the display name as the second field (check the box stating only display value from second field to hide the key).
I would have two global fields, that would allow me to select the IDs of the stop, and the attendee, then a simple script that added a record to the StopAttendees table:
Set Variable [ $stop ; table::globalStop ]
Set Variable [ $attendee ; table::globalAttendee ]
Go To Layout [ StopAttendees ]
Set Field [ StopAttendees::a_kf_StopID ; $stop ]
Set Field [ StopAttendees::a_kf_AttendeeID ; $attendee ]
Commit Record [ no dialog ]
Go To Layout [ original layout ]
To display this related data on a "Trip" layout, I would create a portal showing "Stops", I would then place a button on each row of the Stops portal to "Show Attendees", this would just tie to a script with a single step like:
Set Variable [ $$StopFilter ; Stops::a__kp_StopID ]
I would then have a second portal, showing records from StopAttendees, that had a portal filter of:
StopAttendees::a_kf_StopID = $$StopFilter
Essentially, this second portal only shows records from the filter you chose to display from the first portal. Since you have the relationship there, you can drop the name field of the attendee into the second portal.
I realize that's a lot to take in, but if you work through this from start to finish, it should get you what you need, with a proper relational structure.
Mike, really appreciate the time and thought you took to answer and the straight-talk on how to make this happen. I'll start working on this today. Many thanks, indeed.