8 Replies Latest reply on Dec 3, 2012 8:29 AM by philmodjunk

    (updated) Multiple "lookups" between two tables / multiple relationships

    wilz

      Title

      (updated) Multiple "lookups" between two tables / multiple relationships

      Post

           Im new to DB and FM.  Currently using 30 day trial version to see if this software is right for me.  My knowledge of FM is limited only to the walkthrough file. (which ive completed).  My problem is I dont know how to setup multiple relationships for two fields.  (and apoloiges as im to noob to really know how to write the question properly)

           (Update: since posting i ve learnt about "lookups" and i guess im looking for a way to set up two lookups between two tables) 

           Situation is this: I have two table. 

      Flight Table                    Crew Table
           Flight Number                 Pilot Name
           Pilot                                 Pilot WAge
           Pilot Wage
           Origins

           I currently know how to set up a relationship, so when "creating" a flight record, I can enter the pilots name, and it will give me the Pilot Wage

      So DATA in my CREW TABLE is
           Record #        1                   2
           Pilot Name   George          Mary
           Pay               $100             $150

      Create a Flight:
           Flight Number  : CX103
           Pilot: George
           Pilot Wage: 100 (automatically filled in)
           Origin: Hong Kong

           I RUN into problems when I add in a "co pilot"

      Flight Table                    Crew Table
           Flight Number                 Pilot Name
           Pilot                                 Pilot WAge
           Pilot Wage
           Co-Pilot Name
           Co-Pilot Wage
           Origins


      THIS is what I WANT to see :
           Create a Flight:
           Flight Number  : CX103
           Pilot: George
           Pilot Wage: 100  <--autimatically entered
           Co-Pilot: Mary
           Co-Pilot Wage: 150 <--automatically entered
           Origin: Hong Kong

      THIS is what I END UP seeing :
           Create a Flight:
           Flight Number  : CX103
           Pilot: George
           Pilot Wage: 100
           Co-Pilot: Mary
           Co-Pilot Wage: 100   (THIS SHOULD BE 150, as its looking doenst know how to look for Mary;s wage only Georges)
           Origin: Hong Kong

           SOLUTIONS I dont like:
           I DO NOT want to create TWO Staff tables (A pilot table AND co-pilot table) becuase there may be times Mary will be PILOT and George CO-pilot.  AND for what Im doing I want to see what their respective wages when I create the flight record.

           I'm guessing to do this I have to write a customised script? Or is there a fourmla I can put in? (like a Vlookup in Excel?)

           Any advice (specific or general) would be great.

           Cheers!

        • 1. Re: (updated) Multiple "lookups" between two tables / multiple relationships
          philmodjunk

               You have a many to many relationship. A flight can list many crew members and any given crew member participates in many flights. (And in other similar cases, it has come out that the "role" for a given crew member may not be the same on every flight, a pilot, for example can be a pilot on one flight and a co-pilot on another....)

               You need a join table between flights and crew:

               Flights-----<Flight_Crew>------Staff

               Flights::__pkFlightID = Flight_Crew::_fkFlightID
               Staff::__pkStaffID = Flight_Crew::_fkStaffID

               If this notation is unfamiliar, see this thread: Common Forum Relationship and Field Notations Explained

               The pay and role--assuming that it can change from flight to flight would be recorded in fields in the Flight_Crew join table. If roles never change, you can keep that field in Staff.

               With this design, you can put a portal to Flight_Crew on the Flight layout and format _fkStaffID as a drop down list of StaffID and Staff Name so that you can use it to assign crew to a particular flight. Fields from Staff--such as the person's name, can be included in this portal. If you include the pay and role fields, you can specify that in the same portal.

               In similar fashion, a portal to FIight_Crew on the Staff layout can list all flights that crew member has taken, their pay and their role on that flight.

          • 2. Re: (updated) Multiple "lookups" between two tables / multiple relationships
            wilz

                 Thank for that.  Problem is im still really confused, coz the solution i created still seems wrong. 

            • 3. Re: (updated) Multiple "lookups" between two tables / multiple relationships
              wilz

                   or is this what you meant? I think it looks wrong too.. sorry im really noob with all this.... 

              • 4. Re: (updated) Multiple "lookups" between two tables / multiple relationships
                wilz

                     OR did u mean this? My "issue" I had with this solution was that i actually had to enter in the post (i was hoping that by default there was a slot for pilot and a slot for co-pilot and i could just enter in the name)?  

                      

                • 5. Re: (updated) Multiple "lookups" between two tables / multiple relationships
                  wilz

                       Ok solved!  

                       Step 1: Created Tables
                       FLight Details (Table)
                       _PKFlightID
                       Pilot
                       CoPilot

                       Staffing (Table) 
                       _PKStaffID
                       Wage

                       Step 2
                       Went to "Relationshios view, highlighed staffing table and created a copy "Staffing 2"
                       BUT Staffing 2 does NOT show up when i go back to the "table" tab. 

                       Step 3 (input data)
                       went to Staffing and inputed three staff (s1,s2,s3) and wages
                       went back to Flight Details.. inputed staff names and it gave me the wages

                       But im still not sure why this works, maybe u can explain what I did? and if this is a workable soultion? (assuming im gonna have alot of flights and staff. 

                       THX !


                        

                  • 6. Re: (updated) Multiple "lookups" between two tables / multiple relationships
                    philmodjunk

                         Your final set up is not what I suggested. What I suggested is shown in the screen shot you uploaded immediately before this last screen shot.

                         You would select crew for the filght in fields in the portal to Pilot Roster. A drop down list format set up on the _fkStaffID field is what you would use to select a pilot or copilot.

                    • 7. Re: (updated) Multiple "lookups" between two tables / multiple relationships
                      wilz

                           Ok thanks for your help... and again and apologies too as i think im at fault for not defining my problem clear enough).  


                           So just to clarify, your soultion is better if there are multiple roles on the plane and for multiple people who can take it.  where as my final one is better if the roles are fixed (i.e. there MUST be 1 pilot and 1 copilot each flight).  Is that correct? 

                           Thanks again for your help so far... its been a vry steep learing curve for me as I;m learning to develop a database for my business. 

                           Cheers!

                           Wilie 

                      • 8. Re: (updated) Multiple "lookups" between two tables / multiple relationships
                        philmodjunk

                             I still think my suggestion is the better option. I'm not an expert in your business, but it's my impression from other sources that a person might be a pilot on one flight and a co-pilot on another. Is that a possibility?

                             If so, you'll be able to use that join table to generate reports with much greater flexibility than you will from separate fields for pilot and co-pilot on each flight record.