3 Replies Latest reply on Jul 10, 2014 8:09 AM by philmodjunk

    Relationship Advice



      Relationship Advice


           I'm probably the 1,000,000th post with that title har har!

           I'm working on a modified version of the Invoices Starter Solution on FileMaker 13 and I need some help relating some data.

           In the default solution each Customer has a "Company" which is just a field in the customer table.  I changed that field to "Groups" and related it to a new table called "Groups" via the "Group Name" field.  Then I created another table called "Projects" and each project is also related to a "Group" via the "Group Name" field.

           I did this so that when a customer fills out an invoice, I wanted each item (each entry in the Invoice Data table) to have a drop down showing them only Projects associated with that Group.  I did this by creating a Value List which displays a "Project ID" field in the Projects table which included "only related values starting from: Invoices".

           This works great, however I have some additional data in the Projects table that I need brought over.  If I add a "Project Name" field to the Invoice Data table that does a lookup in the projects table it will not always show the "Project Name" from the project the user selected.  I'm guessing it just looks for the first project associated with that group and lists that project's name.

           I tried relating the Project ID field in Invoice Data to the Project ID field in the Projects table directly but it had to copy the table (which I don't really understand) and still gave me strange results.

           Any advice on how I could set this up better?



        • 1. Re: Relationship Advice

               I'm not totally sure that you need a groups table here so please bear with some questions that may result in a simpler set of relationships:

               Can a customer be part of more than one group? (Answer appears to be no from what you have posted, but let's be sure of that.)

               Can a group be linked to more than one customer?

               My point here is that if it is always one customer to one group, you don't need both a customers and a groups table and this can slightly simplify your relationship graph.

               Linking Invoice Data to Projects will require creating a new "instance" (called a table occurrence everywhere else) of an existing table. This does not actually "copy a table", it creates a second reference to the same table that you already have. In your case a new occurrence of Projects should be linked to your existing Invoice Data table occurrence if you want each "line item" on your invoice to be able to link to a specific project in the project table and lookup (copy) or display (link to) data in a selected record in projects. Please note that you would have to select that project in your invoices portal before that link will work for looking up or displaying data from Projects.

          • 2. Re: Relationship Advice

                 Thanks for the help!

                 I tried creating a second instance of the table and it appears to work perfectly.  I'm not sure what I was doing wrong last time but it's working now, and it's nice to hear someone say that's an okay way to do it.

                 I might not need that Groups table, I do have that isGenetics field which I use in some calculations though.

                 Thanks again!


            • 3. Re: Relationship Advice

                   If it is one customer to one group 100% of the time now and in the future, then any fields defined in groups could be moved into the company table and your groups table could be removed. This is not a necessity, however, just a way to have slightly fewer "moving parts" in your database.

                   To learn more about table occurrences (Instances) and how to use them: Tutorial: What are Table Occurrences?