Need Help Planning a Time Entry/Billing Solution

[The Professor]

Hi All,

Warning! I have some dumb questions about some probably basic FM concepts, so thanks in advance to all those kind enough to help.

I have a law firm client using an ancient OS 9 based time entry/billing program called Timeslips. I have been tasked with recreating (and hopefully improving) it in Filemaker.

I have the time entry portion up and running fine. Each record is an entry of billable time tied to a particular client. But now I have to create a printed monthly bill for each client. Seems simple enough, but there are some wrinkles.

First, it has to maintain a rolling balance due. Most of the firm's clients have outstanding balances that are allowed to remain on the books for months and months. When payment is received, it's rarely for the full amount of the bill. So I have to allow for random, partial payments that will be reflected on the next month's bill.

Secondly, I don't have the luxury of a strict calendar month based search for each printed bill. Sometimes time and expenses are entered retroactively, so that the new November bill will include "new" charges dated from April.

Lastly, and most importantly, how do I track payments when they do come in? Do I need a separate DB/table for this? My Time DB just tracks billable time. Where do I "put" the payments as they come in? Do I need a separate DB/table for this, and if so, how do I get all the time entry records in from the Time DB? Can I achieve this via a report?

Sorry for the vague nature for the problem. I'll try to explain it better in response to questions.

[Sanjai]

In the time entry portion, each client has only one record or multiple records for every billable time?

Finding rolling balance due for a client would not be a problem. When a payment is recieved then we can check that if the payment is more than the current bill then check if there is any outstanding amount in previous bills. If there is an outstanding amount then we would first pay the amount in the oldest bill to close the old transactions before newer.

Answer to another question: We can keep track of bills based on dates issued instead of tracking the billing dates.

I would say we can use the billing table to store the payment information because there cannot be two payments for one bill by the time. We can have a separate field OutstandingAmt which we can use to keep track of how much is the outstandingamt on each bill.

[The Professor]

Each record is one entry of time per client, so a given client could have hundreds or thousands of records. I managed to get a report that totals all the time for each client, but I still have three problems:

My report shows every record for every client in the database. Is there a way to restrict the records shown in a report? I tried a simple find of ten given records, but when I switched to the report, it ignored the search results and defaulted back to one massive report of every record.

Secondly, I still don't know where to "put" the payment amounts. Should I create a different layout within the Time DB itself dedicated to incoming payments? Have a payment field which has its own running total which is subtracted from a running total of all time billed? This would be a different kind of record than the normal time entry record. Is it possible/wise to "mix" them in this way? If the payments need to be in another table, how do I do that?

Lastly, I need to show the amount of the most recent payment in the report. How can I "find" the payment record with the most recent date and display it in the report?

Thanks again to all for any responses and sorry for the newbie questions.