Find Data within DB and Summarize

[Corey H]

Hello!

I have two databases, one is Client info, the other is Billing and Payment info. I want to be able to have a running balance for each client at time of service. If client is seen on 1/1 and is charged $100, but doesn't pay, the balance due is $100. Then when client is seen again on 1/7 and is charged $100 and pays $50, balance due would be $150. This seems like this would be easy enough to do with a GetSummary field, but it doesn't work out that way. I either get the entire client history (future appointments and past appointments) or I get the entire database--all clients' payments and charges--getting added up. How could I go about finding only the client's info and only where the Date is < or = to the date of the current appointment being shown?

Hope this makes sense. Can anyone help/

Thanks!

Corey

Edited August 14, 2005 by Guest
Typo

[SlimJim]

One way of doing this is to first relate the Billing and Payment info to a second TO of itself using the field Client ID. The summaries over that relationship then refer to one client only. The second step is to restrict that relationship by date so assuming that the appointment date is part of the Billing and Payment table you add in Date <= Date so that whatever record you are looking at the only related records will be current or past.

The specifics of doing this will depend on the current structure of your solution.

[Corey H]

I have tried this method. I either get nothing showing up in the field or I get the totalling of all clients again. Here is the calculation I use in setting up te field:

If (Invoicing2::Date ≤ Date and Invoicing2::Client = Client; GetSummary(Total Due;Client))

Does this sound correct or am I missing something? (I'm probably missing something!)

THANKS!

[RalphL]

I posted a sample file some time ago that may answer your needs.

http://www.fmforums.com/forum/showtopic.php?tid/105735/post/168811/hl/runningBalance/#168811

Use the last file posted.