Running Balance (again)

[Nanore]

Hello,

Running balances... I've looked at several good threads on the subject, and also downloaded a couple of good example files. However, I'm having a difficult time wrapping my head around how to apply those techniques to my particular solution.

Goal: Show a running balance inside of a portal.

Setup, as simple as possible:

-I've got an Invoice table and a layout based upon it.

-On that layout, I've got a portal, which shows records from its child table called Payments. The relationship is InvoiceID = InvoiceID between the Invoice and Payments tables.

-The Payments table has the following fields: Date, Amount, PaymentID, InvoiceID.

That works great for showing me all Payments belonging to a particular Invoice when I'm on the Invoice layout.

Now I want to add a Running Balance inside of that portal, to show the total of payments. So I assume that will be a new calculation field inside of Payments...?

-The portal is always sorted by Payments::Date.

-There can be more than one Payment record with the same Date, so it should still do it right, but maybe based on something smaller than date, like create and use Timestamps instead?

What kind of relationship changes will I need to make, if any, and what would the calculation look like?

I just can't seem to figure out how to apply the examples to this scenario.

Thanks for any help!

[comment]

Have you tried defining a summary field in the Payments table and placing it in the portal?

[Nanore]

Have you tried defining a summary field in the Payments table and placing it in the portal?

Wow, that did it! Thank you. How embarrassing, I missed that entirely.

Ok, there's one more thing in regards to that. Need to bump it up a notch then I think I'm all set:

I need a second new field now.

-The Invoice table has a second child table, called Line Items.

-Line Item table has InvoiceID, LineItemID, Price

-As you might expect, it's InvoiceID = InvoiceID between Invoice table and Line Items table.

-I've got a calculation in Invoice that sums up the Prices of the Line Items, it gets displayed on the Invoice layout (not in a portal). Let's call that z_cLineItemsSUM

-I need to take this z_cLineItemsSUM and somehow use it against a Running Balance field in Payments portal. For example:

z_cLineItemsSUM = 100. There are 5 payment records of varying amounts which equal 100. I want to show on each Payment record in the Payments portal, a dwindling "Balance Due".. basically.

How hefty will this be?

Thanks again!!

*walking over to the Tips jar soon*

[comment]

How hefty will this be?

To my surprise, not very. Just add a calculation field to the Payments table =

Invoices::z_cLineItemsSUM - sRunningTotal

Note however, that this may not refresh well. If you edit the related data, you should follow this by Refresh Window [Flush cached join data]. A report produced from the Payments table would be a more robust solution.

[Nanore]

To my surprise, not very. Just add a calculation field to the Payments table =

Invoices::z_cLineItemsSUM - sRunningTotal

Note however, that this may not refresh well. If you edit the related data, you should follow this by Refresh Window [Flush cached join data]. A report produced from the Payments table would be a more robust solution.

Excellent. That works wonderfully. Thank you very much! So much simpler, wow.