2 Replies Latest reply on Apr 1, 2014 9:52 AM by MartinShippey

    Sum of calculated fields in a filtered portal - blank!

    MartinShippey

      Title

      Sum of calculated fields in a filtered portal - blank!

      Post

           Hi,

            

           I am trying to create a Statement which shows unpaid invoices (including partly paid invoices) listed in a portal.

           The layout is based on the Customer table, with invoices from the related Invoices table being listed in the portal, related by account number.

           Each portal row consists of the details of the invoice including a calculated field which shows, after any part payment against that invoice, the outstanding balance (this is part of the Invoice table).

           The Statement portal filters out any invoice which has a zero outstanding balance.

           Although each row contains the amount outstanding against any invoice, I want to include a field on the Statement which give the total outstanding balance if more than one invoice is unpaid.

           I have created a 'Statement Total' field with the calculation Sum (Invoices : : Balance Outstanding) scenario a *million* times - it just remains empty.

           What am I doing wrong?

        • 1. Re: Sum of calculated fields in a filtered portal - blank!
          philmodjunk

               Can't tell from here why you are getting an empty result--most likely guess is that it's a relationship issue, but even if the result weren't empty it wouldn't be the correct total for a filtered portal. Sum evaluates at the data level and thus ignores any portal filter. It would sum all related records--not just those shown in the filtered portal.

               To show a total from a filtered portal, define a summary field in the portal's table that computes the total of the desired field. Then make a copy of your filtered portal (so that it has the same portal filter expression as the original), but change its setup to be a one row portal. Put your summary field from the portal's table in this one row portal to show the total of your original filtered portal.

          • 2. Re: Sum of calculated fields in a filtered portal - blank!
            MartinShippey

                 What you say makes sense - the best I ever achieved was to get the sum of all related records, or sometimes just the first one.

                 Anyway, your solution does the job - many thanks, I wish I'd asked sooner.