9 Replies Latest reply on Feb 20, 2015 7:42 AM by mdphillips

    Mulligan on Relationships Help Question



      Mulligan on Relationships Help Question


      First off - many thanks to Phil for trying to help me out with this before.  Either I'm a complete idiot (very possible) or I didn't represent the project very well so I thought I would try it again since I think the relationships are a bit more complex that I originally anticipated.

      The project needs, goals or whatever they are called are as follows:

      The group has many members that need to be tracked for position certification
      The group has 19 different positions available
      Each member can become certified for more than 1 position
      Each position has a task book and a Job Action Sheet (JAS) associated with it
      Each member will have their task book(s) evaluated several times before becoming certified
      Each member also receives 1 JAS for each position(s) every time they perform it

      I added fields for every pk/fk combo I could think of even though I might not need them (newbie overkill) so don't laugh.  I also included a picture of what I would like to use as the main data entry layout.  Members could be added and then the related data could be added/seen via the portal.  Any insight would be greatly appreciated.




        • 1. Re: Mulligan on Relationships Help Question

          Sorry forgot the layout

          • 2. Re: Mulligan on Relationships Help Question

            And which table occurrence from your first screen shot is the basis for the layout in your second screen shot?

            This can be found by opening Layout Setup and checking "Show Records From".

            Am I correct that multiple team members can be certified for the same position?

            • 3. Re: Mulligan on Relationships Help Question

              Yes you are correct in that multiple members can be certified in the same position. 

              If I understand the question, the layout I would like to use is called Members_Data Entry.  I would like to have a single layout that users work from if possible.  I like the idea that Forgive my possible nomenclature abuse here - Each tab control at the bottom contains a portal to view and create that would be related to the current member record & I did use the "Show Records From" in the setup.  I had this partially working (or seemed to be) before using the original set of tables and tweaked the relationships a bit.  It let me add multiple task books to a member but I was getting hung up when I tried to add evaluations to the new the additional task books.

              To review - there is a TB & JAS for every position available.  To clarify each individual member will only get 1 task book per position that will be evaluated on multiple occasions until they are deemed certified.  However the member will get a position-centric JAS each time they perform that position.  For example I am working on TBs for the Resource Tracker and Ground Support.  I have been evaluated twice as a RT and have 2 JAS sheets for it and I have been evaluated once for the GS position and have 1 related JAS.  At the same time a friend of mine is working on TBs for Ground Support and Transportation.  He will have separate evaluations and JAS for each of those positions. 

              One thing that occurred to me is that I have the position name is used in 2 different tables because it refers to both a task book and a JAS and they are both position-centric.  So doesn't that break 3nf? If so, don't I need a join table(s) in there? 

              I'm thinking that the Positions entity (nomenclature ?) should be global (or at least that's what I think you call it) because there are only 19 possibilities and they can be used by 3 other entities (members, TB & JAS).  Am I even close?  The problem is, I currently have very little understanding of global relationships or how to accomplish them.  I'm more than willing to learn - just not sure where to start.

              Thanks for being so patient with me and your willingness to try to help!

              • 4. Re: Mulligan on Relationships Help Question

                Well, I didn't ask you what name you planned for the layout but on what table you based it on. As I stated in my last post, if you open layout setup, there's a table occurrence name selected in "show records from". Likewise, there's another table occurrence specified in "show records from" in portal setup.

                Let's tackle this one part at a time...

                The fact that TeamMember records can be linked to multiple records in the Position table and a Position record can be linked to multiple TeamMember records means that you have a many to many relationship here and that requires a join table.

                Start with these relationships:

                Team Members-----<Member_Position>-----Positions

                Team Members::__pkTeamMemberID = Member_Position::_fkTeamMemberID
                Positions::__pkPositionID = Member_Position::_fkPositionID

                You can place a portal to Member_Position on the Team Members layout to list and select  Positions records for each given Team Members record. Fields from Positions can be included in the Portal to show additional info about each selected Positions record and the _fkPositionID field can be set up with a value list for selecting Positions records by their ID field.

                • 5. Re: Mulligan on Relationships Help Question

                  Sorry I misunderstood you.  The layout I showed was based off the Team Members table.

                  That's what I was trying to do with the relationships but messed it up.  I followed your suggestion and so far so good - it looks and does exactly what I wanted so far.  You are the FM Yoda!

                  Now I'm guessing I have to something similar to the JAS and Evaluations to the Member Position table. I took a stab in the dark and came up with this new set of tables - am I even close?

                  • 6. Re: Mulligan on Relationships Help Question

                    This is where the "subject matter expert" (you) has access to info about how this is supposed to work that I don't. So I will feed back to you what your last screen shot tells me:

                    a) the relationship between the join table and Member Position Evaluations allows you to link multiple evaluation records to one team members qualification for one specific position. If there is never more than one evaluation for a given member_position record, you don't actually need a separate evaluation table, the fields in this table can instead be fields that are part of Member_Position. (But there is no great harm in using a related table as you have set up if you want to use it here.)

                    b) The Join table, Member Position JAS, means that you can set up a record in JAS that links to multiple Member_Position records and also that a given Member_Position record can be linked to multiple JAS records.

                    So it is up to you to determine whether you need that 1 to many relationship in a) and the many to many relationship set up for b).


                    • 7. Re: Mulligan on Relationships Help Question

                      OK I think I may have simplified my life if I've finally wrapped my head around the relationships.  Here is my new table diagram.  I think this allows me to have many evaluations related to a specific Member Position ID (which we we know works correctly).  Hopefully I'm not crazy.

                      Thanks so much!!

                      • 8. Re: Mulligan on Relationships Help Question

                        Your relationships are consistent with what you describe via text in your post.

                        • 9. Re: Mulligan on Relationships Help Question

                          Phil - you are the bomb!!!!!  I tried it out last night and it did work.  Your last post was my "wow I could've had a V8 moment".

                          I was so dead set that I had to keep JAS and Evaluations in separate tables but realized they didn't have to be because they are both evaluations - just different in what they track.  I also got messed up because because I incorrectly assumed that all fields from a table had to be in the same portal. By using the separate tab controls for JAS and Task Book I was able to split the fields up visually.

                          Again thank you so much for your patience and wisdom!