Setting up a prepay feature for accounting purposes

[Dorian L]

I am very new to filemaker and have been having some trouble coming up with a good way to keep track of money owed and paid by clients in out database. I hope I have this in the right spot as it deals mostly with calculation and summary fields.

Here is some background on the database. I have three tables (Customers, Jobs, and Accounts Receivable). Customers has a one to many relationship with jobs and jobs has a one to many relationship with accounts receivable

These are the fields I have for Accounts Receivable.

CustomerID

JobID

JobPrice

PaymentStatus

PaymentDate1

PaymentDate2

PaymentType1

PaymentType2

CheckNumber1

CheckNumber2

PaymentAmount1

PaymentAmount2

cBalance = (Balance for the particular job calculated by JobPrice-PaymentAmount1-PaymentAmount2)

BalanceSummary = (Summary Field that should give the outstanding balance for all unpaid dumpster rentals for a particular customer and dispalyed on customer layout.)

cPrepayAdd = (Calculation field that tells the database to either add funds to a prepay balance if the job type is prepay balance or remove funds from the prepay balance if the job is a dumpster rental and the payment method is by prepay purchase.

PrepayBalance = (Summary field that should show the amount of prepaid funds that are left for a particular customer.

We have a setup where clients can order a dumpster rental and they can pay by cash, check, invoice, credit card, or prepay balance. There is also the option to add funds to a prepay balance by buying a prepay purchase job.

This is an example of what I need to have happen and cannot figure out how to do. I am a beginner with this so details would be helpful.

1. Lets say I have customer A and they want to add funds to a prepay purchase job. They currently have no outstanding balance. They want to add $1000 to prepay purchase, but have not yet paid for it.

2. At this point the cBalance field would need to show $1000 owed for that particular job.

The Balance Summary should also show $1000 owed total for that customer.

PrepayBalance field should show $0 currently.

3. When the prepay purchase is paid it should subtract that amount from cBalance and from the Balance summary

4. It they pay the full $1000 it should show $0 for cBalance for the job and $0 for the Balance Summary for that customer.

5.Because this is now a paid for prepay purchase job the $1000 should go into the PrepayBalance field for that customer.

6. Now that same customer wants to order a dumpster that costs $100. When marked as unpaid it should show $100 dollars in the cBalance for that job and BalanceSummary field for that customer.

7. When they pay for the dumpster and want to pay by prepay purchase $100 should be subtracted from the cBalance and BalanceSummary fields and subtracted from the PrepayBalance field as well (leaving $900) in the Prepay Balance for that customer.

8. It should do this for each prepay purchase job until there are no funds left. If there are no funds or insufficient funds left in the PrepayBalance summary field for a job being entered and paid for by prepay purchase it should not allow the user to enter in the job payment as a prepay purchase and give a statement that says "Insufficient funds available in Prepay Balance for Customer A, Customer A has "amount left in prepay balance" left in prepay balance.

I am having the most trouble with getting it to give me a message when the PrepayBalance summary does not have enough funds to support an entered prepay purchase job.

Currently it will let me enter the job and will just give me a negative PrepayBalance for the customer. So if I have $1000 prepay balance and enter a job that is $2000 and mark it as paid by prepay purchase the cbalance for the job goes to $0, the BalanceSummary for the customer goes to $0 and PrepayBalance goes to -$1000.

I really want it to not allow the full $2000 to be entered as a prepay purchase if there is only $1000 left in the PrepayBalance summary field.

I apologize for the long post, but hope this provides enough information to explain my situation. Thanks in advance for any help on this.

[LaRetta]

Unfortunately I don't have time to respond properly but you really need to change your structure so I would suggest doing that before proceeding with your issue:

CustomerID

JobID

JobPrice <---- no. This should be sum of the lineitems (unless you are suggesting that this the quoted amount for the total job)

... the above should be an invoice table and the following should be lines in a lineitems table but only one per record as:

JobID

PaymentStatus

PaymentDate

PaymentType

CheckNumber

Amount

As it stands, what happens when someone has a third payment? And don't assume it won't happen, I guarantee it will. Also, they may pay by check but the check will bounce. You may easily end up with several transactions (and listing even same check number more than once as it is re-submitted).

I will try to assist further later when I have some time. Your 'Accounts Receivable' should be Invoices with LineItems. ALL transactions must be invoice related; either a payment, a return, a credit, late fee, discount and so forth ... one line for each transaction.

Edited September 22, 2010 by Guest

[Dorian L]

I would appreciate any help. I agree your way does sound like it would be better, but this solution is not really going to be fully utilized for accounting. It is more of a double check system to ensure a job has been paid or hasn't. If there is a relatively simple fix to make it more accounting friendly I would bee interested though.

If there are more than three payments we currently just change one of the payments to reflect the third or forth amount until the cBalance is equal to 0. We also haven't worried about bounced checks as all we are really trying to keep track of is if a job is paid or unpaid. If someone pays by check we don't change the payment status to paid until it actually clears. For us it is just a double check to our peachtree software. We just use it to display who owes money, who is paid up, who needs credit cards charged, who needs an invoice, and who has made a prepaid job. If you still feel there is an easier or better way to set this up I am all ears. Thanks for your help.