3 Replies Latest reply on Oct 5, 2010 9:17 AM by mootles22

    Creating individual portal totals and grand total

    mootles22

      Title

      Creating individual portal totals and grand total

      Post

      Hi again,

      Finally getting somewhere with the last post (if anyone saw it), thanks Phil for all your help.

      FMP11 on Windows XP btw.

      I've created my line items table and have portals in quotes/invoices table/layout linking back to it. Since I need about 20 options on each quote, I have a tab layout with portals on each tab linking back to line items. Each portal is filtered by quote number and then by option number (ie. portal6 on quote 2000 shows only line items that are submitted for that quote number and option number 6, etc). What I want now is for each of the 20 portals to have it's own subtotal of the line items within it and then a grand total of all the portal subtotals.


      Think I can do the latter (grand total) as it's going to be a simple SUM but I'm struggling to get each portal subtotalling correctly. I think it's maybe because I'm filtering by two criteria on the portal (quote number and option number) so I'm maybe not creating my SUM calculation correctly.

      Any help would be much appreciated, I'm sure it must be fairly simples!

      Cheers,SK

        • 1. Re: Creating individual portal totals and grand total
          philmodjunk

          Sum won't work with filtered portals as the sum function references the portal's relationship and this completely bypasses any filter expression you set up on the portal.

          Define a summary field in the portal table that sums the desired field. Place a one row portal with invisible boundaries just below the portal you need a total for and place this new summary field in that portal and give this new portal the same filter expression as the portal just above it.

          For your grand total of all the portals, you can probably use the sum function as this would be the same as computing the sum of one unfiltered portal to all your line items.

          • 2. Re: Creating individual portal totals and grand total
            mootles22

            Thanks for that Phil, I've done what you said, the summary field (called OptionSummaryTotal) and a small portal is bang on, works perfectly filtered by QuoteRef and OptionNumber.

            I've tried SUM-ing OptionSummaryTotal up to give a NetTotal but it doesn't seem to like it. Any suggestions?

            As a work around for now I've created a copy of the small portal to OptionSummaryTotal from each of the options and removed the filter for OptionNumber so the filter is now just by QuoteRef. It seems to work OK for whole figures (ie. whole pence) but if you have a lineitem which ends up with a fraction of a penny, the three OptionSummaryTotal portals might read £0.31, £0.62, £1.20 but the NetTotal portal is £2.12 and not £2.13 because option one is actually £0.305 and another £1.195 and has been rounded up as we want currency to 2 decimal places.

            Obviously it's a simple rounding problem because both sets of totals are looking back to the lineitems at different stages, rather than the NetTotal adding up the Subs, but it doesn't look right on the printout and a customer is bound to start questioning the extra penny they've been charged! Is the easiest thing to go right back to the components/stock table and have it so that our RetailPrice (worked out from TradePrice*Markup) is duplicated automatically into another field which rounds up to whole pence? And then use that whole number field from then on? Also might end up with the same problem with VAT and Gross at some point...

            I'm about to start searching the forum reference the rounding, just thought I would put it on here in case you had a quick fix.

            Thanks in advance.

            • 3. Re: Creating individual portal totals and grand total
              mootles22

              Think I've sorted the rounding. I've made the LineSubtotal from this:

              Quantity * RetailPrice

              To this :

              Round ( Quantity * RetailPrice ; 2 )

              And that seems to work so far. Presume I do the same with any VATAmount fields...