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.
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.
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.
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.
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?
Why doesn't the summary report work for you?
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.
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.
You can do this... use GetSummary function. This will retrieve the summary field value via the table occurrence join.GetSummarybreakField - field, or an expression that returns a reference to one. To calculate a value, use the same summary field for both the summary field and the parameters.When a summary field is also used as the break field, returns the summary field value for the entire of records (a grand summary value).Note You can get similar results using a relationship and Aggregate functions. For more information, see Summarizing data in portals.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.
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.
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.
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.
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..)
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.
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!