How to subtract Last Row or Subtract a Row on a Portal?

[Mark Pastoril]

This is continuation that i'm working on a Water Billing App.

I need this calculation for my Disconnection Letter.

I would like to subtract the last portal row or the row that is not "Due" and don't have penalties yet.

so like the data show above, the bill #-08-0000133-5 is not yet Due coz it does'nt have a penalty yet, so this row will have to subtract to the total balance.

like this Total Balance= 2,934.568-580.30

Total is 2,354.27 

and it will show here:

 

Thanks for the reply in advaced.

[Wim Decorte]

I will warn against using calculations for this kind of data.  They will be unstored by nature and have a tendency to become huge performance dogs when you later want to report on them (for instance "show me all clients with a balance of more than $1,000").

The balance gets sets when you receive payments and when you issue new invoices.  Those are very specific events with their own workflow so it is easy enough to add a step in the workflow to calculate the balance and set it as a static number in the balance field.

[Mark Pastoril]

it's ok sir, coz i am not gonna use that data any other than this form or letter.

[comment]

I don't understand the logic you describe.

I believe the best way to produce the result you show would be to find all Billings records where DueDate has passed and DatePaid is empty (this requires no calculation fields, neither stored nor unstored). Then sort them by customer and print them using a  layout having a sub-summary by customer as its only part, with a page break after each sub-summary. Using a summary field to get the subtotal will result in each customer seeing the total of their past-due-date amounts. 

This is assuming you're printing the letters. If you need to produce individual PDFs or e-mails , it gets a bit more complex.