4 Replies Latest reply on Nov 29, 2014 1:43 AM by FredH

    Summary fields (in Portal) based on different filters

    FredH

      Title

      Summary fields (in Portal) based on different filters

      Post

      Hi Experts,

      I initially have 2 tables : 

             
      • Project
      •      
      • Revenue

      with : 
      Project ---< Revenue
      In my Revenue table, I have a Year field, different Revenue fields and also a field that gives more info on the year (first year, go-live, etc).  For each project, I have 10 records in the Revenue table (1 record/year).
      In my Project table I have my project name but also a couple of characteristics for that project (type, etc).

      I have created a layout linked to my project table where I show the project details as also the revenue figures.  The revenue "table" is created via multiple portals (one cell/portal) as I needed to have my columns (different revenues) as lines and lines (years) as columns.

      What I would now need is to have the exact same table but which is showing me - cross project - the sum per revenue. 
      Also, I would need to apply different types of filters using the extra field of both the Project & Revenue Table in order to see specific sums.  I.e. show only projects of type A, only the project years of "go live", etc.
      I should also be able to filter not only on specific values but also on the "exclusion" of some values.

      In order to have the sums correctly, I figured out that I should create a new table "Global" which has only one record and that all projects could be linked to that record.  I tested this with a summary field and it seems to work.
      But how do I set up the filters?  I saw a couple of threads explaining that filtering in search mode does not affect the summary fields of a portal but what is than the best way to work?

      Thanks in advance for your input
      Kind regards,

      _Fred_
       

        • 1. Re: Summary fields (in Portal) based on different filters
          philmodjunk

          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.

          • 2. Re: Summary fields (in Portal) based on different filters
            FredH

            Hi PhilModJunk, 

            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!
            Kind regards,

            _Fred_

            Note : I see that the years are missing in my screenshot, these are above my columns of course.


             

             

            • 3. Re: Summary fields (in Portal) based on different filters
              philmodjunk

              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.

              • 4. Re: Summary fields (in Portal) based on different filters
                FredH

                Hi PhilModJunk, 

                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!
                Kind regards,

                _Fred_