There are multiple methods for selectively summing the data in a table. A filtered portal is just one (and does not necessarily require the use of a "global" table--I can think of several ways that might be used.
A more detailed description of how you need the filtered portal to work would help us be more specific.
Possible options include: a filtered portal, filtered relationships, ExecuteSQL() and a summary report using sub summary layout parts.
Thanks a lot for your answer, I will try to be more precise!
In attachment you can see my P&L table for which each cell is a small portal as you nicely explained me before.
I have one P&L per project and now I would like to see within the same table :
- total P&L for all projects
- total P&L for some projects depending on a couple of dropbox filters I would need on the same layout (project type, investment type, etc.). Ideally, for the dropbox filters, I would not only need the possibility to i.e. have the total P&L for all projects for only one project type, but also the total P&L for all but one project type.
How do you think I could do this? Having both the total shown correctly but also keeping enough flexibility to be able to report different types of P&L within the same report?
Thanks in advance for your input!
Note : I see that the years are missing in my screenshot, these are above my columns of course.
Can't read the language shown in the screen shot and that makes this screen shot less helpful to me in figuring out your current design.
And what relationship have you been using to link your layout's table to the data you want to show in the portal's?
Is this a list view layout where each row shown is either a different record or a sub summary layout part?
To improve performance and make the totals computed in the portals accessible to scripts and other calculations, I would use a relationship that matches to the data instead of a portal filter.
You can define relationships to a series of table occurrences with a global field defined as the match field for the first column's relationship and unstored calculations that use the year specified in that global field to calculate different years as match fields for the relationships used for the other columns.
Layout is linked to Project table (fields of that table are above the table screenshot) and the fields of the screenshot are linked to the Revenue table : Project ---< Revenue
The screenshot that you see is a combination of small portals (one portal/cell) with in columns the Year values (Year field of Revenue table) and my different Profit & Loss fields (in french :-)) as lines, as Fenton Jones described in following thread : http://forums.filemaker.com/posts/cd07daacde
This is thus not a list view but really a form view with one form per project. Also, each project is described by a certain number of "attributes" (type of project, type of investment, location of project, etc.) and also each year of the project has a description (build phase, starting phase, full capacity, etc.).
What I would need now is a view, I would say (same) form, where I can see the total P&L for all my projects. In addition to this, I would like to dynamically "play" with the form in order to filter on the different attributes (only projects of type A, all types of project except type A, for each project only the years labeled as full capacity, etc.). I think that this should maybe be done via multiple radio buttons that let the user select what should be included in the total P&L or not.
In order to have the numbers summing up, I thought I could add a table to my relationship that is Global--<Project with only one record in global "global" and link the layout to that global table.
But question is : how to apply these filters (radio buttons) to show only "partial" sums?
Thanks in advance for your input!