Previous Balance in Statement

[copies]

I have 2 tables. Table One lists all invoices for many customers. Fields are Customer Name, Invoice Amount, and Cumulative Balance. When sorted by customer name, it shows the cumulative balance for each customer. Table Two is a statement form, which contains a portal which pulls in all the records for one customer for a specified period of time (current month), showing the fields listed above.

 

In Table Two, how can I:

1. Show the previous balance (the cumulative balance) from the last invoice prior to the defined period, and

2. Add the previous balance to the first invoice in the portal, so it shows a total of the previous balance plus the invoice amount?

 

This would be simple to do in a spreadsheet, but I just can't figure out how to do it in FMP. Maybe the portal isn't the right tool, and I should go another route. Any help would be appreciated.

 

I imagine I could create a script to pull out the previous balance, but I'm thinking that a calculation of some sort would be more effective.

[Maarten Witberg]

How do you track payments?

Also, maybe you should not directly discard a scripted method to generate the statements. The drawback is, you have no live data. The plus side is, you have no calculated overhead from a lot of unstored calcs. I think you'd need to use the GetNthRecord () function for this. And this could cause a performance hit since if I understand correctly, a whole history of monthly reports will dangle below the current report. But I am not sure how it would work out in your case.

It's entirely possible that I am misfiring here so wait for more advice.

[aaa]

Helo, copies.

Look attachment.

Here you will have one idea how you can do.

[aaa]

Sorry, kjoe!

[Jack Rodgers]

Let's assume your invoice file has both amount due, amount paid and balance.

 

You could create a calculated fields based on a statement date:

 

Previous and unpaid: If invoice date -30 (whatever)

 

New Invoice: If invoice date -30 (whatever)

 

This would give you balance from previous statement and new balances.

 

Sum (previous and unpaid)

Sum (new)

Sum (balances)

 

For historical purposes you might want to add a table for these numbers:

 

Statement Date

Client

Unpaid

New

Total

 

These an other numbers make a good ongoing report.

[copies]

Thank you, Kjoe, AAA, and Jack!!

Kjoe, payments are tracked in table 1 as a separate field which is calculated in the cumulative balance (actually, while I'm working out the basics, it's a negative amount in the Invoice Amount field). I messed with the GetNthRecord () function, but, frankly,wasn't able to figure out how to make it find the last invoice for a customer before the first invoice in the portal!

For example: Table 1 could have invoices in this order:

Customer 1, Invoice 1

Customer 2, Invoice 2

Customer 1, Invoice 5

Customer 1, Invoice 6

The portal in Table 2 would show current invoices #5 and #6 for Customer 1, and need to add the the previous balance from Invoice 1 to the cumulative balance of Invoice 5.

I haven't totally discarded the idea of a script, and your comments make a lot of sense. My thoughts on the file overhead were to create the statements, print them, then (via script) reset the dates to 0 in the portals.

 

AAA's sample file has helped quite a bit as well--thank you, AAA, for the time and thought you put into it. Looks like it does just what I was asking for!

 

Jack, your methods and suggestions make a lot of sense, too, and I think I'll be incorporating them into the methodology provided in AAA's sample--along with some more serious thought to scripting.

 

I'd like to say that this forum, and the people who contribute, has been helpful beyond words! You guys have helped me out of a tight spot more than once, and just reading the questions and answers has always been enlightening and often, even fun! Thanks, again!