3 Replies Latest reply on Jul 12, 2011 9:27 AM by Corné

    Sum grand totals of a selection of invoices between two dates

    Corné

      Title

      Sum grand totals of a selection of invoices between two dates

      Post

      Hi all,

      I like to sum the grand totals of a selection of invoices between two dates. I can make the selection through a portal with this formulain the filter: Invoices2::date ≥ Invoices::gDateStart and Invoices2::date ≤ Invoices::gDateEnd
      Invoices is a TO and Invoices2 is a self-join relationship of Invoices.

      From the invoices that are filtered I would like to sum the grand totals. I seem not to be able to find the right formula, nor I'm able to find a similar thread on this forum. Any help or direction to a similar thread is appreciated! Thanks in advance.

      Corné.

        • 1. Re: Sum grand totals of a selection of invoices between two dates
          philmodjunk

          If you are trying to do this with a calculation field defined in Invoices that uses Sum ( Invoices2::InvoiceTotal ), that won't work for a filtered portal as Sum will give you the total of all related records, not just those that meet the requirements of your filter.

          You have two options here:

          Use the global fields and inequalities you've placed in your filter expression as the actual fields and operators for your portal relationship. Then Sum will correctly compute the total.

          Define a summary field that computes the "total of" your invoice total field. Create a second portal referring to the same table occurrence and using the same portal filter expression on your layout. Make this portal just a one row portal and place your summary field from Invoicess2 in this single portal row. This summary field, passed through the same filter, will compute your total for the specified date range of invoices.

          • 2. Re: Sum grand totals of a selection of invoices between two dates
            Corné

            Phil, YOU THE MAN!! Smile

            Works like a charm. I chose option two and it was a hit the first time. Thank you!

            Now my second challenge is to split this total in cash payments and a checkbox used when payed with a card. I'll dive in it myself, but feel free to jump in when you feel like it ;-)

            Best,

            Corné.

            • 3. Re: Sum grand totals of a selection of invoices between two dates
              Corné

              Found it! I made two portals extra (with one row and one cell). I changed the formula from 

              Invoices2::date ≥ Invoices::gDateStart and Invoices2::date ≤ Invoices::gDateEnd

              to

              (Invoices2::date ≥ Invoices::gDateStart and Invoices2::date ≤ Invoices::gDateEnd) and f 2::card = "CARD"

              (Invoices2::date ≥ Invoices::gDateStart and Invoices2::date ≤ Invoices::gDateEnd) and f 2::card NOT "CARD"

              NOT is actually the NOT sign.

              Thanks again!