6 Replies Latest reply on Dec 30, 2009 8:17 AM by philmodjunk

    Creating a table referring back to summary fields

    btr94

      Title

      Creating a table referring back to summary fields

      Post

      In one of my database files, I have multiple tables - each referring to a different coin collection of mine. I have tables such as "Standard Coins", "Paper Money", "Special Sets", etc...

       

      I am looking to create a report or table that will create a listing of each table name and their applicable total summary field (I have in each table a field called "Total Value of Collection" that counts the Collectors' Value and sums it up).

       

      It would look like this:

       

      TABLE                         VALUE

      Std. Coins                    4532.09

      Paper Money                 1224.82

      Special Sets                  54234.19

       etc...

       

      What is the easiest way to accomplish this?

        • 1. Re: Creating a table referring back to summary fields
          philmodjunk
            

          Why separate tables? How difficult would it be to merge all your records into a single table with one added field "Type" set to identify each record as "standard coins", "Paper money", etc. ? Then, adding a new collection does not require you to define a new table. You can also set up a summary report based on this combined table to summarize your collections.

           

          You'd define a sub-summary report sorted by the above "type" field, put a summary field defined to compute the "total of" your value field in this subsummary part and delete the body part so that you get just one row per collection.

          • 2. Re: Creating a table referring back to summary fields
            btr94
              

            PhilModJunk wrote:

            Why separate tables? How difficult would it be to merge all your records into a single table with one added field "Type" set to identify each record as "standard coins", "Paper money", etc. ?


            Actually, very difficult. Each of the sets of tables have different fields - conditions are rated differently, there are some extra notes and organizational fields in each that are unique to each table. Is there really no easy way to create a summary field in a seperate table that would refer back to another summary field? I even could just put text boxes to hold the table names to ease the process.


            • 3. Re: Creating a table referring back to summary fields
              comment_1
                 You need a relationship in order to show summary values from other tables. Since you want the total of all records in the other tables, you can define the relationships to use the x relational operator (with any two fields as the matchfields). Then place summary fields from the collection tables on the layout of the overview table - or define calculation fields using the Sum() function.
              • 4. Re: Creating a table referring back to summary fields
                btr94
                   Thanks. I'm relatively new to FileMaker so I'm not 100% sure what you were recommending me to do with the "x operator" but I'm going to investigate and play around and see if I can figure it out. Thanks again!
                • 6. Re: Creating a table referring back to summary fields
                  philmodjunk
                    

                  "Is there really no easy way to create a summary field in a seperate table that would refer back to another summary field?"

                  Yes, there are ways to do that as Comment has suggested. It's just easier and you can get more flexible reporting options if you have either a single table of this data or if you have a common table with related "detail" tables used to document the specific differences. Note that "...conditions are rated differently, there are some extra notes and organizational fields in each that are unique to each table." are issues that can dealt with even when all or most of your data is stored in a common table.

                   

                  Consider a database I worked with for a previous employer as an example. The company manufactured extremely different products with very different processes and material needs. Yet we had a common table of SKUs (stock keeping units), descriptions and such. Related tables then documented the different details in each product's specs and manufacturing process. Thus we had specs on printed wine corks, metal bottle caps and shrink wrap capsules all starting from a common table.