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

# Sum grand totals of a selection of invoices between two dates

### 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

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

Phil, YOU THE MAN!!

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

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!