5 Replies Latest reply on Aug 2, 2011 11:35 AM by philmodjunk

    Related Table Fields



      Related Table Fields



      New to FM.  I’ve created two tables, for simplicity lets say Customers and Agents.  Each customer can only have one agent from the Agent Table.  In each Customer record I have three fields from the Agents table; two of those fields you have to choose the entry from a value list.  A third field on the customer record should be automatically entered based on the selection from one of those two original fields from the Agent table.  The Problem: The third field is not automatically filling in.

      Each Customer Record

      1. Vendor Table field 1 (Value list Selection)
      2. Vendor table Field 2 (Another Value list Selection)
      3. Vendor table Field 3 (auto filled when Vendor Table Field 2 is selected)

      Vendor Table

      1. Field One (value list)
      2. Field Two (value list)
      3. Field Three from values on table

      What am I doing wrong?  Thanks for your help.


        • 1. Re: Related Table Fields

          It's not really clear what you have set up here. Using actual field names instead of "field 1", "field 2", etc. makes it easier to see the big picture and this helps us to help you.

          How are the two tables related?

          If you have this relationship:

          Customer::VendorID = Vendors::VendorID

          (You use "vendor" in one part of your post as your table name and name it "agents" in another so I had to pick one of the two here.)

          Then you can add any fields you choose from Vendor to your Customer layout and they will display the appropriate vendor data once you have entered or selected a matching value in the Customer::vendorID field.

          • 2. Re: Related Table Fields

            I thought I had done what you described above.  But the third field still isn't automatically filling in.

            Here's more detail.  Table 1 are Customers.  Table 2 is Agents.   The fields for Agent table are

            Field1 = Refer Source (picked from one value list)

            Field 2 - Agent Name (picked from a different value list) (I can display Field 3 from this value list)

            Field 3 - Mktg Credit (auto fill from Agent Name)

            Is the problem the value lists?

            • 3. Re: Related Table Fields

              Then what are "Vendors"? I assumed they were "agents" but now I'm not sure. Is a "vendor" a "customer"? That seems a contradiction in terms. Also, Field 2 and Field 3 both show the agent name? Wouldn't that be the same name in every case?

              I'm going to use my own table and field names here. You'll need to translate them into yours accordingly.

              Customers::AgentID = Agents::AgentID

              However you name them, you have many customer records linked to a second table, so the serial number field must be defined in that second table rather than customers here.

              The Layout should be based on Customers, not Agents. (Layout should be based on whatever table you use on the left side of the above relationship.)

              Field 1: (Refer Source) should be defined as a field in the Customers table. Otherwise, all customers for the same Agent will show the same Refer Source value here. Thus, it is not affected by the presence or absence of a related record in any way.

              Field 2: AgentID this is not the agent name, but a number field and used to link a Customers record to an Agent record. Format it as a drop down or pop up of Agent ID's from Agents in field 1, includ Agent Name in field 2 so that selecting an Agent Name enters that Agent's ID number.

              If you format Field 2 as a pop up with the show only second field option specified in its value list definition, Field 2 serves to show the agent name. If you prefer a drop down, add the Agent Name field from Agents to your layout so that the name will be displayed here when you exit the drop down list.

              Field 3: You have two choices. You can add the field with this value from Agents to your customers layout or you can define a field in Customers that uses a looked up value field option to copy the value from Agents. Both produce the same results when you select or enter an Agent ID in the Customers::AgentID field, but with a looked up value setting, editing the value in the Agents table does not automatically update the looked up value field in Customers. This may or may not be a desirable thing, so you'll need to decide on methods here.

              • 4. Re: Related Table Fields

                This is working great.  Thank you,

                I have another question regarding related tables.  I have a section of each record that has insurance agent contact information and it is added to each record via  (3) different relational tables (the tables are based on different "types" of agents.)  After selecting which type of agent it fills in their contact data nicely.  How  would I fill in the contact information again in other fields in my layout (it's for billing purposes.)

                So if InsideAdjuster:InsisdeAdjusterID were selected than Customer:Billt o would fill in as well.

                • 5. Re: Related Table Fields

                  There are two basic approaches. Which method is best depends on your data and the design of your database.

                  See this thread: Auto Fill