8 Replies Latest reply on May 28, 2012 11:21 AM by SteveMartino






      Converted MS user

      Hi, I hope someone can help with my database & relationships (for a service business)

      I have my database set up with multiple tables, and a relationship that doesnt appear to be working properly.

      Layout 1 has all basic customer info

      Layout 2 (second table) has all the customers equipment

      When making the relationship, I linked "customer last name' & customer first name labels to Layout #2.  Then I imported all the info from layout 1 to populate it.

      The problem is, when I add a new customer to Layout 1, they dont appear in Layout 2.  I thought that linking the last names would serve as my key, which is obviously wrong. 

      Do I need to give each customer a unique customer id to make this work?

      Or would it be better to keep all the info on one master layout, and create different layouts to view either delivery info or equipment info.

      Any help would be greatly appreciated



        • 1. Re: relationships

          A unique customer ID is a very good idea, but it's not why this isn't working for you.

          First some basic terminology: When you create a table in Filemaker, the system also creates a table occurrence of the same exact name. This is what we call the "boxes" found in manage | Database | relationships that we use to define relationships. Keep in mind that a table occurrence does not have to have the same name as the table it represents and you can create more than one occurrence of the same table. Also, the system creates a layout for you of the same exact name and puts selects the table occurrence of the same name in a drop down named "Show Records From" that you can find in layout setup.. Once again, a layout does not have to have the same name as the table or the table occurrence and you can have many different layouts that refer to the same table occurrence.

          Thus you have two tables whose table occurrences are linked in a relationship like this:

          Table1::Firstname = table2::firstname AND
          Table1::lastname = table2::lastname

          When you create a new record in table 1 using new record on the layout for table 1, this does not automatically create a new record in table 2. You'd need to do two things:

          1) go to a layout for table 2 and click "new record"

          2) enter names in the table2::firstname and table2::lastname fields that match the names entered into the name fields you defined for table 1.

          With your current design, filemaker will not do these two things automatically.

          A Unique Id number defined in table 1 will make for a better field to use to link to other tables such as your table 2. Names are not unique. People change their names and names can be entered misspelled. Using an auto-entered serial number that is unque and never changes avoids such issues.

          • 2. Re: relationships

            Thanks for your help...but that just bounced off my brain.  I really need a comprehensive text book (for dummies).  This is how I learned Access & Excel.  Anywho.

            Here's what I'm trying to accomplish, and I apologize for not knowing the correct terminology.

            I need my database to do these things.  This is for a home heating oil business.

            1. Customer records-basics

            2. A list of the rate their equipment uses fuel (This I already have done/converted with formulas, etc.).

            3. A list of their equipment.  This is necessary so I can inventory what quantity and type of equipment is out on the street, and keep parts in inventory.

            4. A summary of their deliveries of home heating oil.  So I can adjust #2.

            5. A list of their repair history.

            6. And a way to sort/find, so I can query the database to determine who needs a delivery

            7.  I would like to incorporate invoices, although Quickbooks seems to be unbeatable.

            So here's my question(s).

            Do I just use one giant layout with all the information, or try to break the info into different tables/layouts.  If I break them down, how do I link them?

            I honestly try to use all the help I can read, but it seems to me that Help starts with the idea you already know FileMaker and its terminology.  Sometimes the answers contain jargon, that I cant find the answer to.

            So I know it's alot to ask...getting me pointed in the right direction, but any help is greatly appreciated



            • 3. Re: relationships

              The point that I tried to make with my last post is that layouts and tables are not the same thing even though their names might be identical. A Filemaker layout serves the same purpose as a form or report in Access. You can have many different layouts that all refer to the same table just as you could have many different forms in Access that all quieried the same table.

              Just as you would do in access, you should define different tables for each type of data you need to store in your database.

              A table of customers

              a table of equipment

              a table to use as a repair history with perhaps a related table to list each part and labor charge in that repair

              An invoice and (probably) a lineitems table for invoicing. Repairs and oil deliveries could be listed in the same table so that you have a unified table for billing.

              There are a number of supplementary resources on FileMaker available. There are books you can find on sites like Amazon as well as traing videos and tutorials.

              Basic table/relationship design in FileMaker is fairly similar to Access, but you don't use SQL in Filemaker so you have to learn alternatives for when you want to query the database in order to bring up a specific set of records.

              • 4. Re: relationships

                Hey Steve,

                I suggest breaking it down first. You could use a Menu (or a Dashboard) with different buttons to go to the different parts of your database.

                This is a screenshot of the menu for one of My FM files. It's in Dutch so you can't read it, but you can see there are lots of different kinds of information you can store and work with. Mine has invoicing, expence tracking, vehicle damage, lists of vehicles, rental equipment, sales equipment, vehicle repairs, sheduling, ....

                For invoicing, I would add it all to Filemaker, that way you'll have everything together in one program.
                You need to make only one backup of one file and you don't need to type in information over again in two different applications. (Like client information etc.)

                Filemaker can do invoices like the best  :)

                For your structure I was thinking something like the image below.

                Please note that there are only 5 tables. The second blue one is a second Table Occurence of the Equipment table so you can make different kinds of relationships and keep everything neat and organized.

                Relationship Graph

                • 5. Re: relationships

                  Thanks to both of you for your responses.  First to DaSaint, so best to break them into different tables.  Am I correct, you saying both blue boxes are 2 different layouts of the same table? I guess I understand a little better, but referencing what Phil & I were discussing, and maybe this is so basic you'll laugh, when I try connecting with relationships, the forms don't seem to populate.  In other words, I would like to bring up a customer layout with basic info.  Then I may need to see either delivery history, service history, or fuel usage.  I cant seem to get my brain to do this logically.  Right now, if I have the 'customer layout' up, I have to switch layouts, then go find the same customer in that layout.

                  Second to Phil, I think were both on the same page.  The only difference is you know what your doing....lol.  Regarding books, etc.  I have yet to see a book for FM12, will a complete book about FM 11, be helpful, or does FM12, work much differently?

                  I appreciate the help, and I don't expect anyone to design my database for me.  I'll just keep plugging away and learning as much as I can.

                  Thanks again


                  • 6. Re: relationships

                    Filemaker 11 and 12 work almost exactly the same, so a book on 11 will definately help. More info can be found in the pdf's here:


                    To expand a little on the terminology:

                    First of all you make a table (In File - Manage - Database) That's the place where your data exists.
                    In your table you make fields. For a contact table you would make an ID field, a name field, an adress field etc. These fields can be tect fields, number fields, date fields, time fields etc. You use a container field to store an image or a file in a table.

                    When your table is made you go and create the relationships (like in my screenshot)

                    Every box in there is called a table occurence. They are all based on a table but you can make multiple occurrences of one table.
                    The idea is that you can create different kinds of relationships.

                    Sometimes you need these table occurences if you want to present a limited amount of information in a portal for instance. But don't worry about that just yet.

                    Then the next step is to create a layout. A layout is the visual form you use to enter data. It is always based on a table and contains fields, buttons, checkboxes, radio buttons, dropdown lists etc.

                    If your layout isn't properly displaying data then either you have based your layout in the wrong table. Or you are displaying fields from another Table Occurence then the one the table is based on.

                    If you create a layout based on the table Customers. Then your fields on that layout also need to come from this table.

                    Maybe you should open a starter solution to have a little look behind the scenes to see how it's done.

                    If you need any more help make sure to ask here!

                    • 7. Re: relationships

                      You may find this tutorial on table occurrences helpful: Tutorial: What are Table Occurrences?

                      • 8. Re: relationships

                        Thanks guys for all the help. I'm getting there, with ALOT of advice......slowly..

                        Thanks again