14 Replies Latest reply on May 17, 2017 2:56 PM by Jaymo

    Help with relationships

    sandygjohnston

      I'm sort of a novice, and I'm trying to set up a simple Company/Contact rolodex database. 

       

      I have 3 tables:

      1) Company with address & phone number fields.  It also has a portal which relates properly to the Contact table (see screen shots below), but companies don't have to have employees.

      2) Companies/Contacts table, which is a join table.

      3) Contacts with contact fields.  Contacts don't have to be an employee of a company.

       

      I think I've set up the primary keys and child keys properly (see the attached screen shot), and the Company layout works fine.  I can create contacts in the portal that are related to ABC Company.

       

      But on the Contact layout, the drop-down list doesn't populate the Company fields.  When I click on the arrow to show the list of companies (those are created by calculation), I can see all the companies, but it won't populate the list.

       

      And my button "Go to this Company" doesn't work either.

      @

      I'm sure it has something to do with how the tables are (or aren't) related.  Help?  I've been stuck on this for a while.  Help!

       

       

        • 1. Re: Help with relationships
          Jaymo

          Can a Contact belong to more than one Company? It's possible but not common. If the answer is "no" then you don't need a join table. Just want to make sure your relational design is correct before going any further.

          • 2. Re: Help with relationships
            sandygjohnston

            Okay, thanks for your help!  You're correct, one contact doesn't belong to more than one company.  So I removed the join table, but the company info still doesn't populate on the contact page.  Do I just have one table occurrence of company and contact, related only through pk_COID & fk_COID?  Because when I add another table occurrence and relate pk_CONTACTID & fk_CONTACTID, the fields still don't populate.

            • 3. Re: Help with relationships
              philmodjunk

              You need to match records by company ID.

               

              You might need to explain in more detail what you mean by "populate". You can set up fields in one table that auto-enter or look up (copy) data from a related table, or you can place fields from the related table right on the layout based on the first table. There are different causes of "Failure to populate" depending on how you set this up. (And I wouldn't set fields in contacts to copy data from companies if that's what you have done here.)

               

              Edit note, use the wrong field name at the top.

              • 4. Re: Help with relationships
                Jaymo

                Yes, you should relate the primary key from companies to the foreign key in contacts. The easiest way to populate the foreign key in contacts is to use a popup menu. In companies, a simple portal will work if you have the relationship option to create related records turned on.

                • 5. Re: Help with relationships
                  philmodjunk

                  If you are reading this via email, please note that I entered the wrong field name in my last post. I've edited it here in the forum to correct, but you won't see the correction via email.

                  • 6. Re: Help with relationships
                    sandygjohnston

                    Sorry, more questions.  Do I ONLY relate the tables through the Company pk_COID to the fk_COID in the Contact table?  And I do NOT add another relationship between CONTACT IDs between the two tables?

                     

                    So see my screen shot of the Contact layout below, you can see that when I click on the drop down arrow, I can see all the companies.  I've seen a database before that would auto populate the related fields below when one of the companies is selected.  Can you help me figure that out?  It's still not working with the pk_COID to the fk_COID or, for that matter, with pk_CONTACTID and fk_CONTACT ID related either.  Sorry!

                     

                    • 7. Re: Help with relationships
                      Jaymo

                      You are populating your foreign key with the company name. Looks like you need to change your popup menu to display the company id field and the company name. Use the company id in the first column of the value list definition and then company name in the second column. Also check the option to only show values from the second field.

                      • 8. Re: Help with relationships
                        sandygjohnston

                        Sorry, I had to leave for a while.  Correct me if I am wrong, actually I think I am not populating my foreign key with the company name. My primary and foreign keys are labeled pk_ & fk_.  It's probably confusing because I'm calling the calculation fields ID fields.

                         

                        In the company table, I have a regular plain serial number for pk_COID, and it's related to the contact table fk_COID, and those two fields are related.  They are the only related fields in this database.

                         

                        Also in the company table, I have a field called CO_ID Calc, and it is an auto-enter calculation of the company name, address, city, state, zip.

                         

                        In the contact table, I have another field called CO_CONTACT_ID, which is a value list based on the CO_ID Calc field in the Company table.

                         

                        Do I need to add a further relationship connecting contact's CO_CONTACT_ID with the CO_ID Calc in the company table?  Would that make it populate?

                         

                        So sorry to be thick-headed.  I'm having problems wrapping my head around this.

                        • 9. Re: Help with relationships
                          Jaymo

                          A relationship from the Company primary key to the Customer foreign key should do the trick. Populate the foreign key and the company information should show on the contacts layout. Don't think you need there auto-enter calculations if you setup the value list as described above. To help, attached is a basic solution.

                          • 10. Re: Help with relationships
                            sandygjohnston

                            Thanks so much!  I will try this!

                            • 11. Re: Help with relationships
                              sandygjohnston

                              That worked!  Thanks so much! But one more question.  Is there any way to show the company name instead of the company ID number? 

                               

                              The people using this database won't understand that they have to click into the field in order to see the companies.

                              • 12. Re: Help with relationships
                                Jaymo

                                The easiest way is to use a popup menu instead of a drop-down list. Make sure you set it to override data entry with value list in the inspector. If you really like a drop-down list then you can stack two fields on top of each other. The second field will be the related company name field. Make sure it is set to not allow entry via the inspector.

                                1 of 1 people found this helpful
                                • 13. Re: Help with relationships
                                  sandygjohnston

                                  Thanks sooooo much!

                                  • 14. Re: Help with relationships
                                    Jaymo

                                    You are most welcome. Happy FileMaking!