Find who owes to print monthly statements?

[tylertul]

Stupid question from a newbie…

How do I find the customers that owe us so that we can print monthly statements.

Master record (client’s table) from table A

- All client info and ID field

Child records (in portal on master record) from table B

- Child records contain fields for payments, charges and ID field, total and running total fields of all historic account activity.

Statement layout

- has a portal to all child records so that as I scroll through each customer all charges and payments show in portal.

I would like to just do a find for any client that owes us by searching the summary field (Total) but that obviously won't work.

Thanks in advance for your help!

Tyler

[Rebel Minion]

Howdy,

When faced with this problem, I kept two fields relating to money for each customer.

I had one holding the amount owed and one holding the amount paid.

So, when a client paid, the script handling that process would subtract the amount from "Client Owes" and add it to "ClientPaid" (as a side note, all charges and payments were independently tracked in a different table, but that is really not the point here).

So now you have a way to create the "Who Owes Me Money" list. Simply do a search on the "ClientOwes" field where the value in that field does not equal zero.

This will also give you clients that for whatever reason have a CREDIT to their account. You can allow for this by putting it toward future amounts or generate a letter/email/fax to the client letting them know they have a credit and how much.

Hope that helps.

R

NOTE: This is a simplified example. The "ClientOwes" and "ClientPaid" fields were actually totals obtained from tables used to track each individual charge and payment. If money is involved, each and every transaction should be clearly and seperately noted with as much detail as possible.

[tylertul]

R,

Thanks!

"The "ClientOwes" and "ClientPaid" fields were actually totals obtained from tables used to track each individual charge and payment."

Of what type were these fileds? Calculated, summary, or? and if they were either calculated or summary, how did you search on them?

That is where my brain is crashing :

Thanks again for your help!

Tyler

[comment]

There are several ways to do this. Much depends on how many clients do you have, and how many transactions. Here is a very simple way, that will work well with a limited amount of records, then become slow:

In the Clients table, create a calculation field =

Sum (Transactions::Amount)

Then you can do a find on this field.

If you have a lot of records, you will need something more sophisticated.

[tylertul]

Comment,

Thanks a million! I had tried the same approach but had put the field in the transactions table instead of the clients table and it just gave me the sum of a single transaction. Makes sense now. At what point do you think we will get bogged down using this? 1000 records, 100,000 records? And what would be the way to do this after we have too many records? Just a tip as to where to look in the future would be great! I'll keep notes.

Thanks,

Tyler

[comment]

That is a tough question (for me, at least). I really don't know - my test file has 3000 transactions and 10 clients and it is very quick. And surely there are other factors coming into play, like how many users are there, what other calculations are needed at the same time, and so on. Perhaps someone more experienced with large files can expand on this.

As for alternatives, that too depends on a variety of factors, especially your workflow. The question is how often will you need this, and how up-to-date it needs to be.

For example, you could run a looping script on opening the file, setting a number field in a Client table with the RESULT of the same calculation as above. Accessible at any time, but only correct as of this morning.

Another option would be to purge the file periodically, by archiving old transactions and replacing them with a single record reflecting their balance - similar to what your bank does, when your statement shows only your previous balance and subseqent transactions, not your entire history every time.

Rebel mentioned yet another way of dealing with this. As you see, you will have plenty of choices when you need this.

[tylertul]

Thanks Comment!