2 Replies Latest reply on May 24, 2016 8:21 AM by wacole1213

    Assigning Persons to a Trip Itinerary: Two Files, Many-to-Many, Select One or More From Each


      The problem is how to assign one or more people to one or more stops in an itinerary. My question to the community: is the following design feasible and, if so, a pointer to a similar example would be wonderful. I'm having trouble working out how to best implement the two parts – Itinerary and Persons – on the layout.

      File one: list of trips

      File two: list of stops on a trip – its itinerary

      File three: people who may be selected to join one or more of the itinerary's stops.

      I have this idea – which may or may not be feasible – of a layout which has two parts; (1) a part which lists a trip's itinerary [File two] and (2) a part which lists people [File three]. A button on the layout would execute a script which would create a record in a join-table for each stop-person record pair. There would also be an “undo” option somewhere in the process.

      The two parts would have a checkbox next to each record which the user can use to select (1) which stops in the itinerary that the (2) person(s) selected are to be assigned. [If there may be better methods for selecting multiple records for a file, please let me know.] Here's a rough sketch of what I'm thinking of:


      Thank you very much for your advice.

        • 1. Re: Assigning Persons to a Trip Itinerary: Two Files, Many-to-Many, Select One or More From Each

          I would use multiple tables to accomplish this, and also ditch the checkboxes as they can't display what you want.:






          Stops (itinerary)




          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 ]

          New Record

          Set Field [ StopAttendees::a_kf_StopID ; $stop ]

          Set Field [ StopAttendees::a_kf_AttendeeID ; $attendee ]

          Commit Record [ no dialog ]

          Go To Layout [ original layout ]

          Refresh Window



          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 ]

          Refresh Window

          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.

          1 of 1 people found this helpful
          • 2. Re: Assigning Persons to a Trip Itinerary: Two Files, Many-to-Many, Select One or More From Each

            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.

            Bill Cole