5 Replies Latest reply on Oct 30, 2012 10:10 AM by old_cedar

    Please help me...Relationship Structure

    jackypeile

      Hi all,

       

      Trying to complete the relationships in my new project.

      I think I have been a bit ambitious with my limited skills in FileMaker and Database design in general.

      Any help would be great!

       

      I have the following planned out and mostly working so far.

      Basically the database is for use within my rowing club to create, distribute and record program/session information.

      So far everything works, except linking:

      • Sessions to Weekly Program

       

      • Weekly Program to Squads
      • Actual Program to Squads

       

      It is most likely that the Program/Session/Exercises will be created for a Squad(Multiple rower/multiple coach) and the Actual Program/Scores/Feedback will be recorded by Squad(single rower/single coach)

       

      Thanks in advance!!

      Jacky

      Database query.bmp

        • 1. Re: Please help me...Relationship Structure
          oli.olafsson

          Hi Jacky,

          It looks like you have the relationship you need to add a session to a weekly program.  To add a new session:

          New Record in the Session table with Session::Program Planner_id = $program planner id of the program you want to add

           

          To add a Squad to a Program, let's make a new table called Squad_Attend.  This table will be a record of which Squads will attend which Programs.  This table should have a relationship to the Squad table (similar to Rower_Squad) where:

          Squad::SQUAD ID = Squad_Attend::Squad id.

           

          This table can be used for Squad Attendance to both a Weekly Program and Actual Programs.  To create these relationships, add an Attend_Actual Program and Attend_Weekly Program table instances and relate to the Squad_Attend table where:
          Squad_Attend::Program Actual id = Attend_Actual Program::PROGRAM ACTUAL ID

          Squad_Attend::Program Weekly id = Attend_Weekly Program::PROGRAM PLANNER ID

           

          Now all the relationships are setup.  To make a new attendance for a squad, add a record in Squad_Attend and:

          Squad_Attend::Program Actual id = $actual program id you want to add

          Squad_Attend::Program Weekly id = $weekly program id you want to add

           

          Hope this helps,

          - Oli

          • 2. Re: Please help me...Relationship Structure
            old_cedar

            Jacky,

            This is a typical scheduling technique.  Let me suggest you start your research there.  Another help is the use of dynamic value list. Those that change based on hierarchal protocols.  This will require a significant amount of pre set up of the value list or you can allow it ot build based on entries.  It is in my experience easier to think of what things are independent of change and those that are dependent on which independent item is chosen.  For example if the sessions do not vary per week.  Session 1 is always at 1 PM and session 2 is always at 2... etc. Then sessions are independent of which week.  If the sessions are dependent on which day of the week then the combination of day of the week and Session are independent.  The Squad identity [i.e. Squad 1] is independent of who is on it.  But the positions on the squad are not.  The Names of the persons are dependent on which position they hold on the squad.

             

            So if you can combine a string that make a dependent and a Independent into a independent for the next step in the hierarchy then you can create another part of a string that becomes independent.  For example.  Squad 1 [and all other squads] are made up of positions. IN the case of rowers that may require a number. [i.e. rower 1, rower 2. etc.]  The number of Rowers may also depend on the type of rowing and therefore define the Squad further.  IN any case the squads are defined [therefore independent] by Squad Number, Positions and persons assigned to positions.  Similarly Sessions have Days of the week and hours. [I am assuming.]

             

            How to relate them?  3 Tables;  File Name [Rowing Sessions needs only on record], Sessions and Squads are all you need to start. 

             

            IN Squads Text fields for Persons Name, Squad, SquadCalc and Position.  Define SquadCalc as text and under "Auto Enter - Calculated value" = [ Squad & "." & Position & "¶" & Squad & "."  ]. 

             

            In Rowing Sessions Define global fields gSquad, gPosition and gSquadCalc as a global calculation the result is text = gSquad & "." & gPosition. [This is for relational finds of Squads data.] AND Define global fields gDate, gSession and gSessionCalc as a global calculation the result is text = gDate & "." & gSession. [This is for relational finds of Sessions data.]

             

            Relate Field Squad to Squad. [Table Squad 2]  Relate Rowing Sessions::gSquadCalc to Squads::SquadCalc [Table Squad 3]::

             

            Create Value lists of the field Squad. Create Value lists of the field Squad 2::Position from Table Squads.

             

            AS you add records persons and assign squad and position to Table Squads the Squad list and Position List per Squad will develop.

             

            Sessions is more cut and dry.  Create a fixed list of days of the week, a dynamic list available hours per day [relating day to day] and a list of dates from the field dates.  Create Session records.

             

            Fields in Sessions are: Day, Date, Hour, Session, and Squad.  Relate Sessions::Squad to Squads::Squad

             

            To display results:  On a Rowing Sessions Table LO[layout] Display the globals gDate, gSession, and gSquads with 2 portals. 

             

            One for Sessions [Rowing Sessions::gDate to Sessions::Date ], include the Sessions fields Date, Hour, Session and squad.  Make squad a button that sets the gSquad field to the related Sessions value [Attach the list of dates from Sessions to gDate.]; and

             

            The other portal for Squads [Rowing Sessions::gSquad to Squads::Squad], include Squads fields Squad, position, Name.

             

            This is primitive in nature but it should avail you with numerous options to make reports from either Sessions or Squads, email members of the found related records, etc.  Hint "one to many" relationships, where one is a global, does not work if the global is in the same table as the many.

            • 3. Re: Please help me...Relationship Structure
              jackypeile

              Thanks for the reply.

              I will try your suggestions and also do some more research on scheduling solutions!!

               

              • 4. Re: Please help me...Relationship Structure
                jackypeile

                Thanks,

                Most of it works now... but I cannot link the sub-sessions/feedback to the actual program table

                 

                I've been thinking a bit more about the functionality.

                I actually want to:

                • Add only ONE squad to any ONE program (ie. Senior Women)
                • MANY squads to a session (ie. crew 1 and crew 2, etc. which in the case of my database crews = squad assigned a coach)
                • MANY feedback/scores to session (ie. rower 1, rower 2, etc. which in the case of my database rowers = squad assigned a coach)
                • Tally feedback/scores in a Actual Program Table assigned to each Squad

                 

                That way make more sense in my head and I think is more practical in the real world...

                 

                Any ideas?

                Thanks!!

                • 5. Re: Please help me...Relationship Structure
                  old_cedar

                  Jackie,

                   

                  Let me apologize for not reading your entire post.  When I could not read the image posted I started from scratch. However your followup to another post may give the former discussion more substance if you will review my comments here.

                   

                  Let me take them one by one.

                   

                  If Programs are independent of sessions then use a separate table just for them. In other words if all the elements of a program are independent of sessions and squads then let it stand alone.  Members of the Program are dependent upon selection.  So if you are in a LO that is based on the Programs then a portal to Squads will show you all squads and thus all members of those squads. You would simply need to add a field to the Squads for the Programs and relate the Programs::Program field to the Squads::Programs field.  Remember these portals can be 2 way.  In a Program LO one can see the Squads selected and in the Squads LO one can see which programs they belong  [If Programs are dependent on sessions, then just make them another field in sessions.  But I suspect programs are not dependent.]

                   

                  If Coaching is dependent on session then add it as a field in Sessions, then everyone in the session has the same coach.  So when a Squad member is selecting a session, the assigned coach for that session is relationally selected as well.

                   

                  Again Feed back and score are dependent on sessions. Therefore from the Squad LO you can create a portal to Sessions Squads::Session to Sessions::Session and display any and all related data.

                   

                  Similarly if Programs need to have the same information displayed, then it can relate to Sessions and or Squads and be summarized per each.

                   

                  I hope that helps your understanding.

                   

                  Real world?  Is a function of one's reality, relatively speaking of course…;-}