3 Replies Latest reply on May 1, 2015 3:19 PM by philmodjunk

    aggregating a portal



      aggregating a portal


      Help I don't know why this isn't working

      I have a portal in clients that shows records in lines the relationship is client::kp_client ID = line::kf_Client ID the portal is filtered by current month so only shows lines of current month. I have a calculation field in Client that is total_time = sum(lines::project time) the field total_time is underneath the portal but shows the total of all project time ever, not just the ones in the portal. what am I doing wrong I have had this sort of thing work in the past?

        • 1. Re: aggregating a portal

          Calculations have no way to "know" that there is a portal filter. In fact, you can place that same calculation field on a layout without any portal at all. So aggregate functions such as sum, count, average.. ignore portal filters.

          You'll need to use a different method to compute your monthly total.

          ExecuteSQL can do this.

          You can also change the relationship so that the current month is a value in a field in clients and used as one more match field in the relationship on which the portal is based so that you can remove the portal filter. Such a calculation field can be an unstored calculation and it will still work.

          • 2. Re: aggregating a portal

            I thought that might be the reason, not sure I can change the relationship as I may want to look at last month or three months ago at some point. is there a problem of me calculating the result via an if statement i.e.  if(line::Project_Month=current month;sum(lines::project_time))?

            • 3. Re: aggregating a portal

              Your added match field can be a calculation field or even a field set up with a value list.
               (we were "filtering" portals long before FileMaker 11 by using additional match fields.)