11 Replies Latest reply on Nov 17, 2009 8:32 AM by kirvis

    Can I use subsummary values in another table?

    kirvis

      Title

      Can I use subsummary values in another table?

      Post

      Hi all,

       

      I am quite a novice when it comes to working with Filemaker 10, but I am in the process of learning quite a lot. There is however one problem I have not been able to solve up till now.

       

      What I would like to do is use subsummary values from one table, and display that in a field in another table, accompanied by its break field. To give an example: I have multiple customers that have three different kinds of subsummary values: amount paid, amount due, and total orders. These different subsummary values are all created in separate tables. What I would like to do is create an overview table, with in the first column the client name, and in the second, third and fourth column the client specific subsummary values, which FM takes from the other tables. 

       

      Up till now, I have not been able to find out of this is possible. I work with FM10.0v1 on a Mac, and am a novice with the software. 

       

      Hope someone can help. 

        • 1. Re: Can I use subsummary values in another table?
          philmodjunk
            

          What is the "break" value for the sub-totals?

           

          Do you want total per customer? Total per month for a customer?, Per week ? or...?

           

          I suspect that a relationship can be constructed that uses the Sum function to return the values you want.

          • 2. Re: Can I use subsummary values in another table?
            kirvis
              

            In my example the break field is customer. I have three different tables with orders and payments from customers. These tables contain summary fields where totals are summarized sorted by customer. 

             

            I want to create a related overview table where each customer has its own row with all those totals, which are taken from the subsummary fields from the other tables. Obviously, the values in this overview table will need to change automatically when new orders or payments are added, and new row will have to be added automatically when a new customer is added.

             

            Did I make myself a bit more clear? I do not care if there is a time component introduced by the way. 

            • 3. Re: Can I use subsummary values in another table?
              philmodjunk
                

              The simplest method doesn't require a separate table at all.

               

              In your existing table create a summary report with a "sorted by customer" subsummary part. Put your summary fields in this part.

              Since you want one line per customer, simply delete the body layout part. (Click the part label and press the delete key).

               

              In FMP 10, this report will be visible as long as the layout's found set is sorted by the break field. In older versions, it'll only be visible in preview or when printed.

              • 4. Re: Can I use subsummary values in another table?
                kirvis
                  

                Thanks for the reply, but I have figured that out; it is exactly how the subsummary values in my database are created. The thing is that I want to export these values to another related table. This because I have subsummary values based on the same break field in multiple tables, and want to export them to one overview table.

                 

                The database with the customers and orders I mentioned in my first post is just an example of what I would like to achieve. 

                • 6. Re: Can I use subsummary values in another table?
                  kirvis
                     Why is the database with the customers an example, or why do I want to combine subsummary values from different tables in an overview table?
                  • 7. Re: Can I use subsummary values in another table?
                    philmodjunk
                       Why doesn't the summary report work for you?
                    • 8. Re: Can I use subsummary values in another table?
                      kirvis
                        

                      Because I have a lot of data (subsummary values) in different tables, all with the same break field, and I would like to create a table with all that data grouped in one overview. The data is about multiple subsequent steps in time, and about multiple variables. That is why a 'simple' summary report does not work for me, and that is why I need to be able to grab subsummary values from one table and put them into another.

                      • 9. Re: Can I use subsummary values in another table?
                        philmodjunk
                          

                        Actually, that sounds like something you can do with a summary report. Summary reports can display data from multiple related tables and you can use finds to select the date range you want for your report.

                         

                        That's why I asked why this approach won't work.

                         

                        You could do this with a table of customers with relationships linking the customer record to different tables with the Sum function being used to compute sub-totals. This just seems like an unecessary amount of work is to produce something that could be created via a summary report is all.

                        • 10. Re: Can I use subsummary values in another table?
                          hiatts
                            

                          You can do this... use GetSummary function. This will retrieve the summary field value via the table occurrence join.

                           

                          GetSummary
                          Purpose 
                          Returns the value of summaryField for the current range of records when the database file issorted by breakField.
                          Format 
                          GetSummary(summaryField;breakField)
                          Parameters 
                          summaryField - field of type summary, or an expression that returns a reference to one.
                          breakField - field, or an expression that returns a reference to one. To calculate a grand summary value, use the same summary field for both the summary field and the break fieldparameters.
                          GetSummary must be set up in the same table as the break field.
                          Data type returned 
                          number, date, time, timestamp
                          Description 
                          This function produces subsummary values. If the database file isn’t sorted by the break field, the result is blank.
                          When a summary field is also used as the break field, returns the summary field value for the entire found set of records (a grand summary value).
                          Use GetSummary to capture summary values when you want to:
                            •
                          use summary values in a calculation
                            •
                          display subsummary values in Browse mode or in a body part
                          Calculations using the GetSummary function are unstored.
                          Note  You can get similar results using a self-join relationship and Aggregate functions. For more information, see Summarizing data in portals.
                          Examples 
                          GetSummary(Total Sales;Country) returns a summary of all records pertaining to the value in the Country field.
                          GetSummary(Total Sales, if(Number of Countries > 1, Country, Sales Zone)) returns a summary of Total Sales by Country if Number of Countries is greater than 1. Otherwise, it returns a summary of Total Sales by Sales Zone.
                          GetSummary(Total Sales;Total Sales) produces a summary of all records (similar to using a summary field, which is a total of total sales).
                          If(ThisCharge > 3 * GetSummary(AvgCharge;Customer), “Verify this charge”, “ “) displaysVerify this charge if the current charge is greater than three times the average charge.

                           

                          • 11. Re: Can I use subsummary values in another table?
                            kirvis
                              

                            Thank you for the suggestion, but it is not working. To illustrate even further what I would like to accomplish, I uploaded a sample file to my MobileMe account. You can find it at public.me.com/b.bijl

                             

                            What it is: 

                             

                            • three indicators: GHG output, energy use, and waste output
                            • one recurring entity: (substituted) end products
                            • two subsequent supply chain steps: components per end product, and processes per end product
                            • two connect tables where calculations take place (currently only in connect table components) and where end product is coupled to components or processes
                            • one output table for every indicator (3 in total), with supply chain steps separated in different columns
                            • one final output table with total outputs per end product 

                             

                             

                            As you can see, I have created a central entity with end products.

                             

                            overview 

                             

                            End products are made of different components and each component has its specific outputs during production which are depicted in the components entity. This is the first step in the supply chain where output is generated.

                             

                            components

                             

                            During production there are different processes needed for every end product, with also specific outputs per indicator. This is the second step in the supply chain where output is generated.

                             

                            processes

                             

                            I have defined two connect tables to prevent many-to-many relationships between end products and components, and between end products and processes. (One component can be used in more than one end product, and one end product can have more than one components..)

                             

                            connect tables

                             

                            Calculation takes place in the connect table for components, where the number of components needed to make the end product is specified. It then calculated a total output for the number of identical components needed for one end product. Furthermore, I have specified summary fields in the connect table components, to be able to summarize the total outputs per end product. (See layouts) So far so good.

                             

                            report

                             

                             

                            The problem is that I want to be able to create a bunch of overview tables, depicted in purple, where on a per end product basis, the summary fields from the connect tables is calculated automatically. So in essence, the in the green connect tables calculated sub summary values must be used by the purple output table, which then have to be able to use them further for calculations. (Adding up as you can see in the last overview picture I added).

                             

                            To illustrate: subsummary values from both components and processes for GHG output need to be used in one overview table for GHG output. In this table, these two values will be added (depicted in the two lower tables for clarity). These added values will then be used in the final overview table where on a per end product basis, all output indicators are depicted.

                             

                             

                            I hope I have made myself a little clearer on the problem. In my view, the essence of the problem is that I am currently not able to use subsummary values for further processing. Please help!