4 Replies Latest reply on Apr 3, 2012 11:50 AM by kallain

    Table Relationship Question



      Table Relationship Question


      Hi All,

      I'm sure this is a simple question but I just can't think of the answer. 

      I have a table called Breakouts which contains all of the overhead for a project (i.e. employee salaries, etc.). These are static costs which don't change, and I have set it up so they are all in one record on one layout with 6 different subtotal fields. I have a second table called Main that contains fluxtuating costs for several shots. Each shot cost is in its own record.

      I would like to have the subtotals from the one record on the Breakouts table to appear on the Main table so I can calculate all the costs together on one layout. I'm having trouble figuring out how to relate these two tables together since there's no relator field (i.e. no prime key) on the breakouts table. Can you guys help advise me how to set this up?

      Please let me know if you need additional details.

      Thanks a lot!

      - K

        • 1. Re: Table Relationship Question

          You need some kind of relationship here. Will there only be one record in the Breakouts table and will it always match to all records in Main?

          If so, you can set up a relationship like this:

          Breakouts::anyfield X Main::anyfield

          the cartesian join operator is something you can select in place of = in a relationship if you double click the line linking these two table occurrences in Manage | database | relationships.

          But be advised that I wouldn't structure my breakouts table with separate fields for each expense. I'd set up a table where each expense in it is a separate record.

          • 2. Re: Table Relationship Question

            Thanks for the reply Phil,

            Yes, there will only ever be one record in the Breakouts table, and yes it will aways match to all records in the Main table. I adjusted the relationship per your suggestion and it (so far) seems to do exactly what I needed it to do.

            You suggest I restructure my Breakouts table so each expense is in its own record. What would be the advantage of doing it that way versus just in one record?

            Thank you,


            • 3. Re: Table Relationship Question

              Please note that this change would also require a change in the relationships used to combine data in your two tables.

              But consider what you must do if the business changes in some way and you discorver that an entirely new expense must be documented in the Breakouts table. With your current set up, you have to add a new field to your table and update any calculation fields that compute total costs.

              If each expense is it's own record, such a change requires simply adding one more record--which is much simpler and does not require a Filemaker developer to do. That's just one example of the increased flexibilty possible when you put each expense in a separate record instead of using separate fields in the same record.

              • 4. Re: Table Relationship Question

                Thanks Phil, you bring up some good points. I'll definitely have to give the structure a little more thought.

                - K