Jump to content

Running Balance (again)


This topic is 4317 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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*

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 4317 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.