6 Replies Latest reply on Oct 28, 2016 6:54 AM by beverly

    Summary of a Portal

    coreequip

      Hello,

       

      i get right away to my problem:

       

      I have two portals that have the same input data, but one of them shows Paid-Invoices and the other one shows Unpaid-Invoices.

       

      Now i would like to have a summary field that shows the total amount of Paid or Unpaid

      My current field shows just the amount of all invoices, paid and unpaid invoices.

       

      What would be your suggestions.

       

      best regards

        • 1. Re: Summary of a Portal
          philmodjunk

          From your description, it would appear that you have two filtered portals.

           

          If so, take each portal while in layout mode and duplicate them so that you have two more portals that each use one of the two filter expressions. Open Portal setup for these portals and change the number of rows to just 1 portal row. Put your summary field from that portal's table occurrence inside this one row portal.

           

          The summary total will then be the total of all related records that pass that portal's filter.

          • 2. Re: Summary of a Portal
            ninja

            It appears as though you have a single Parent-child relationship, and you are filtering your portals in the portal setup.

             

            If you instead have two relationships, with the filtering done in the relationships, you could simply use the Sum() function.

             

            Customer::CustomerID --- Invoice::CustomerID  AND

            Customer::gUnpaid   ---  Invoice::Status

            for the relationship to unpaidinvoices table occurrence, with total being SUM( UnpaidInvoices::TotalDue)

             

            and similar to a second table occurrence to Invoices, but with gPaid as the second link field...

            • 3. Re: Summary of a Portal
              beverly

              If they are filtered portals. Try this:

              https://filemakerhacks.com/2012/09/13/aggregates-in-filtered-portals/

               

               

              Sent from miPhone

              • 4. Re: Summary of a Portal
                ninja

                Learned something new today...Thanks!

                • 5. Re: Summary of a Portal
                  philmodjunk

                  As typical of the site, Beverley's recommended article is an excellent primer on summary fields both in summary reports and filtered portals.

                   

                  Even old dogs like us can learn something new from time to time. I didn't see anything in that article that I hadn't seen before, but I only yesterday discovered via another conversation here that you can drop the "foundcount" layout object into a portal row of a filtered portal and get the number of related records that pass the filter--which can reduce the number of fields in your table by one if such a count is the only reason for defining that field in the first place.

                   

                  There is one caution that needs to be made about this technique for those researching report options that might come across this thread: A cross tab report accessing large numbers of related records via filtered portals can be very slow and can even have a negative effect on server performance for other users at the time you bring up the report. Thus, you need to test your solution with realistically sized  data sets and be prepared to use other methods if you find that producing this report takes too long to produce.

                  • 6. Re: Summary of a Portal
                    beverly

                    absolutely on the slow (as can getting these values with ExecuteSQL). there are many techniques for cross-tab (pivot table) reports. cautions are warranted!

                     

                    beverly

                     

                    p.s. the article *does* mention the use of the {{FoundCount}} in filtered portals.