4 Replies Latest reply on May 15, 2014 9:42 AM by Annette

    Students / Workshops Relationship and Layout Advice /Help

    Annette

      Title

      Students / Workshops Relationship and Layout Advice /Help

      Post

           Hi,

           I have the following tables: 

           1. Students:  Student ID, Name, Etc

           2. Workshops:  Workshop ID, Name,

           3. Student_Workshops:  Student ID, Workshop ID, Date, Location, Etc.

           Relationships are as follows: 

           1. Students::Student ID = Student_Workshops::Student ID

           2. Workshops::Workshop ID = Student_Workshops::Workshop ID

           Each student can attend multiple workshops, each multiple will have multiple students at it.

           I am trying to create a layout where I can enter workshop name (by dropdown referencing workshop table), date, location, other details then add multiple students to attend (using dropdown referencing active student names, unless there is a better way) all at once rather than adding the workshop one at a time for each student.  Is there an easy/better way to do this?  Have I dont the tables correctly to allow this? 

           Thank you in advance!! 

        • 1. Re: Students / Workshops Relationship and Layout Advice /Help
          philmodjunk

               You should be able to do this from a layout based on Workshops and a portal to Student_Workshops used to select each student.

               But what exactly are you doing in this part of the process?

               

                    I can enter workshop name (by dropdown referencing workshop table)

               Are you creating a new workshop with the same name as an existing record or finding a record for that workshop that was already set up?

               If you are creating a new workshop with the same exact name, this could create issues with your records in Workshops due to having multiple records with the same name. You may want to consider adding another table of Workshop Sessions where you have one record for each workshop in the Workshops table and different records for that workshop in the sessions table--each for a different time and/or location for the same Workshop.

               Leaving that possible design change aside for the moment, unless you write some scripts to work with groups of records in either students or workshops, you either select students one at a time for the current workshops record or workshops one at a time for the current students record.

          • 2. Re: Students / Workshops Relationship and Layout Advice /Help
            Annette

                 OK, I will add another table for workshop sessions because yes, the same workshop may be run at various dates/times throughout the year.  Thanks for the suggestion. 

                 As for the layout, I'm ok with going to one layout and selecting each of the names which attend after having entered the workshop detials once.  I was trying to avoide having to either put a portal for student_workshops on each student page and going to each student to repetitively enter the workshop details or alternatively going to the student_Workshops and having to enter the info repeatedly on there for student and workshop. 

                 So am I correct to say that the additional table, workshop sessions would have the workshop ID, date, student ID and location on it and then relate the ID's to the appropriate workshop and student table?  Then what would my layouts/portals be based on?

            • 3. Re: Students / Workshops Relationship and Layout Advice /Help
              philmodjunk

                   Your original relationships were:

                   Students-----<Student_Workshop>-------Workshops

                   Adding a "sessions" table makes it:

                   Students-----<Student_Workshop>-----Sessions>-------WorkShops

                   Sessions::__pkSessionsID = Student_WorkShop::_fkSessionID

                   Note that when you use _fkSessionID to select a Session, you are also selecting the workshop that is linked to that Session record.

                   A portal to Student_Workshop, placed on a Students layout can include fields from Student_Workshop, Sessions and WorkShops to show the needed details for each workshop session in which that student has been enrolled.

                   To assign students to a workshop session, you might use the following scenario:

                   a) select a workshop to get a list of all upcoming sessions for that workshop. This can be as simple as selecting form a value list on a global field and then a script trigger performs a find on the Sessions layout. Scripted Find Examples

                   b) select a session for that work shop to get a form view layout with a portal to Student_Workshop.

                   c) use _fkStudentID to select students for that work shop.

                   This is just one possible approach. I'm suggesting it as it's fairly simple to describe in a forum and for a "newbie" to implement. But many other options are also possible.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              • 4. Re: Students / Workshops Relationship and Layout Advice /Help
                Annette

                     That's great, sounds like exactly what I'm looking for.  Thank you SO much for your help. 

                      

                     Just when I think I've learned a great deal I come up with a new scenario I'm not sure how to tackle.  .......sigh.