3 Replies Latest reply on Dec 2, 2011 11:45 AM by PageWatkins

    Table Relations



      Table Relations


      To make this simple: I have no experience using FMP or any other database software.

      My problem is that I have set up a database with two tables.  These are for contacts and work orders.  The contacts include the company name, contact, and billing information.  How would I go about making it so that when someone chooses the company name from the drop-down menu on the work order form, the billing information automatically fills in?  I have gone to the relationships tab and related the fields, but it won't bring up any info from the company aside from the name when I select it.



        • 1. Re: Table Relations

          I have gone to the relationships tab and related the fields, but it won't bring up any info from the company aside from the name when I select it.

          You should have only one pair of fields that are related, one from each table.

          Contacts::ContactID = WorkOrders::ContactID

          The ConactID field in Contacts should be an auto-entered serial number field. You could use a name field in each--it seems simpler to set up, but you set yourself up for trouble when you get two contacts with the same name (happens with both people and companies) or you find you need to change the name at some point after you've entered data in both tables for that contact (which can be tricky to do without breaking the connection between your contacts and their associated work orders).

          With that relationship in place, you can set up the WorkOrders::ContactID field as drop down list or pop up menu. You can use Manage | Value list with the "use values from field" option to list the ContactID's and their names from the Contacts table. Make ContactID field 1 and Contact name field 2 in this value list setup. Now you can select a contact by name from this value list, but it enters the matching ID number into the field to link the work order to the selected contact.

          From there you have two choices and which method you use depends on what works best for your business. You can choose a set up that copies the data from contacts into work orders. This preserves the contact data current at the time you created the work order. If the contact info changes in the future (they move, change a name, etc), those changes will not appear on your current work order--only new onces you create for that contact. You can also just link directly to the data in your contacts table. With this approach, any changes to a contacts info automatically appear in any work orders for that contact.

          Here's a link to a thread that spells out both methods so that you can choose and apply the approach that works best for you:  Auto Fill

          • 2. Re: Table Relations

            how are the tables related? what does your layout look like?

            • 3. Re: Table Relations


              You, sir, are amazing.  Thank you. I only needed the one field to relate instead of all of them.  Also, I'm underpaid for setting this database up on top of my other duties.  Trust me, I am extremely appreciative of you for this.


              Thank you again.