4 Replies Latest reply on Jan 10, 2011 10:01 AM by Danxyz

    Summary Data from all tables on one layout

    Danxyz

      Title

      Summary Data from all tables on one layout

      Post

      Is it possible to display grand totals for several fields from different tables on one layout?  I have a wine database and would like to show a Summary layout where I display things like Total Number of Bottles, Total Number of Producers, etc ...  Basically a summary of totals across the entire database.

      I have created summary fields in each table and they display correctly when that table is the reference for the layout.  But summary fields from other tables only show the value from one record.

      Must be another way to do this.

      Dan

        • 1. Re: Summary Data from all tables on one layout
          FentonJones

          I tried to describe why just plain Summary fields on a single layout would not work for what you want. But it was too convoluted to explain. Basically a layout is tied to a specify table occurrence. There are ways to get data from elsewhere. But how you do that depends entirely on what it is, and what connections (if any) it has to the data of the layout's table occurrence.

          So what is the structure of your file, and what is it exactly that you want to see?

          • 2. Re: Summary Data from all tables on one layout
            Danxyz

            Here is a simplified version of what I have.  If I can solve for this, I can extend the solution:

            Two tables:  One has Wine Producers.  One is Wine Bottles.  The relationship between the two is ProducerID.  Bottles has a field called DateConsumed so I can track what is in the cellar and what is no longer there.

            I have a summary field in Bottles that tells me how many bottles are currently in the cellar (excludes those with a valid DateConsumed date).  No problem.  

            I have a summary field in Producers that tells me how many unique producers are currently present in the cellar.  No problem.

            But I have not figured out a way to put both of these pieces of information on the same layout.  When the layout is referenced to Bottles, Producers only shows the value for one record.  Visa versa when the layout is referenced to Producers.

            So I basically am trying to put summary information from 'all' records in all tables on one layout.

            Dan

            • 3. Re: Summary Data from all tables on one layout
              philmodjunk

              When you place a summary field on your layout from the same table as the layout, it computes a value based on all the records in your layout's current found set (and sort order if it's in a sub summary part). If you place a summary field from a related table on a layout, it computes a total based on all the records in the related table that link to the current record in your layout.

              If you want a total based on all bottles in the bottles table to show on your producers layout, you need a relationship that matches any record in producers to all the records in bottles.

              Producers::ProducerID X AllBottles::ProducerID

              AllBottles is a new table occurrence of Bottles created by clicking Bottles in the Relationship graph, then clicking the button with two green plus signs and then renaming Bottles 2 to be AllBottles. The X operator instead of the default = makes this a relationship that matches any value in one table to all records on the other side of the relationship.

              Now add your Summary field from AllBottles intead of Bottles to your producers layout and you'll get a total of all bottles records in the table.

              • 4. Re: Summary Data from all tables on one layout
                Danxyz

                Awesome.  You always give me great advice that gets me out of the log jam.  I didn't even know about the 'X' operator.  I'll give this a try.

                Dan