3 Replies Latest reply on Feb 12, 2010 6:54 AM by ninja

    Working with 2 tables



      Working with 2 tables




      I am in the automobile industry and want to put together a database that I can keep track of.

      Now, when this car is entered into my inventory listing (using another table in Filemaker), I want

      it to recognize that a client is looking for that exact vehicle and it will give me the details on the car

      and the client's info to follow up with. 



      My customer table consists of several fields:                             And the inventory table consists of these:


      Name                                                                                 Year

      Address                                                                              Make

      Phone #                                                                             Model

      Date                                                                                  Ext Color

      Time                                                                                  Int Color





      Ext Color

      Int Color


        • 1. Re: Working with 2 tables

          customer table                              inventory table




          Phone #




          Year    \                                 / Year                                                                              

          Make    \\                              // Make

          Model    \\\                           /// Model

          Ext Color -----------[=]---------      Ext Color

          Int Color  //                           \\  Int Color

          Options   /                              \  Options

          Join the tables by all of the options you want to match.


          Then put fields from your customer table (Name Add. Phone#) on your layout based on the Inventory table.


          When there is an exact match, the records will then be related and the Customer name field will be populated.

          If there is any real possibility that more than one customer will want that exact car, consider using a portal instead of just a single set of customer fields.


          For periodic checks, do a find for * on this layout in the customer name field.  This will result in a found set of matches.

          • 2. Re: Working with 2 tables



            I've already joined the fields previously, but I am not sure how to

            obtain the results? What do I need to do next?

            • 3. Re: Working with 2 tables



              You likely already have a layout for Inventory of Cars.

              This layout would:

              1. be based on the Inventory Table (check under 'Layout' dropdown when in Layout Mode)

              2. Contain the pertinent fields from your Inventory Table.


              Now add another field to this same layout, in Layout mode, using the Field tool.

              - A popup comes up asking what field you want it to be...

              - From the top dropdown field in this little window, the one that starts with "Current Table", click on the arrow on the right side and expand this list.  You should see tables listed in categories (depending on the tables you actually have in play)...the categories are "Current Table", "Related Tables" and "Unrelated Tables".

              - If you have already linked your tables, the 'Customer' table should be listed there under "Related Tables".  Choose it.

              - Now below that, you see the fields from your "Customer" table...select 'Customer Name' and hit OK.


              Now that field in your layout will show the related data of 'Customer Name'.  If there is no related record in your Customer table (IOW if nothing matches the relationship criteria that you set), the field will be blank.


              Now mock up a hypothetical customer that wants something you already have in inventory (force a record to match the Inventory record criteria).  Verify that the customer name does, indeed, show up in the Inventory Layout.


              Again, read up on portals in the help index and decide if that is something appropriate for you.  A portal is a way to show a list of MULTIPLE related records instead of just the one.