4 Replies Latest reply on Sep 19, 2012 9:11 AM by philmodjunk

    Simple Relationship



      Simple Relationship


           Hi Everyone,


           I have been working with this for a while now, but can't seem to figure out what I'm not doing right...


           I have a personnel database for a college within a university.  The college operates on multiple campuses.  I would like to link the faculty member to which campus he or she works.  Simple.

           I have a separate personnel and company database, and created a one-to-many relationship between the two.

           On my personnel layout, I have a drop-down box where users select on which campus each faculty member works.


           For some reason, however, this relationship does not work.  I have two companies currently set up, but when I select for one faculty to be on a certain campus, it overwrites the other company record.  They just don't seem to play well together.  I've attached a screen shot of my relathipships.



        • 1. Re: Simple Relationship

                    I have a separate personnel and company database, and created a one-to-many relationship between the two.

               Actually, you have a one to one relationship between the two (Note the lack of "crows feet") and the names used for the match fields suggest that the wrong fields are linked in this relationship. Since more than one Personnel Record can be linked to the same Company Info record, I'd add a new Match field, company ID defined as a number field (no auto-entered serial number for this new field) and link it to the Company ID Match Field in Company Info.

               From the field names and the apparent one to one relationship link, I'd guess that both the Company ID match Field and the Personnel ID Match Field are both auto-entered serial number fields and this will not work for your relationship. One of the two match fields would be an auto-entered serial number and the other would be just a plain number field.


          • 2. Re: Simple Relationship

                 Thank you so much for your response.  There is just something I'm not grasping, and it is really frustrating me that I cannot figure out the problem/solution.

                 Here is what I did.  I created a COMPANY ID field in my Personnel database and made it a number field - no calculations or anything - just as you suggested.   I related this to the COMPANY ID MATCH FIELD in the Company database which is a numbered, indexed/auto-enter serial field.

                 In layout mode, I then created a drop-down field on the Personnel page.
            Display data from: "Personnel Records::COMPANY ID," and the Control Style is a drop-down list.  I then had to create a Value List, which I named Company Name. I specified it to pull values from "Company Info::Company Name".  When I show the "Printable View" The company name, logo, and related information does not appear.  It's just blank.  See screen shots.

                 I'm just using one of the Starter Solutions.

            • 3. Re: Simple Relationship

                   Here's another screen show with the "CAMPUS" field (lower, left-hand corner), being where I want the drop-down menu for the end-user to select on which campus the faculty member works.

                   The idea being that, when you click "Printable View," the header portion pulls the logo, address, etc, from the record coinciding with his or her specific campus.


                   Thank you again for your help.  I am waiting on a "lightbulb moment".  I never thought relationships could be this difficult.  But maybe I am making it more difficult than it really is...?

              • 4. Re: Simple Relationship

                     Your relationship matches by ID numbers not names.

                     Select Company info::Company ID Match Field

                     as the first field for your value list. Then select the also use values from check box and select the Company Name field for the secondary field.

                     Now you can select a company by name, but the value list will enter the ID number into the field and link your Personnel record to the selected Company record. Fields from the company info table can now be added to your personnel layout and when you select a company, they will then display data from the selected company.