Debit - Credit Amount
I am at a dead-end and need some help.
I have a database that records cd purchases. Some clients pay when they order, some pay on pickup and others have a credit balance (The Credit Balance is the one I am having trouble with).
- Recordings (cd’s)
- Credit balance
Credit balance has
- Add Credit Amount (Add amount for credit amount – Clears every time the list all Orders script runs)
- Credit Balance (calculation – “Add Credit Amount” + “Credit Balance”
When you add an amount to the “Add Credit Amount” field a calculation adds or subtracts (if neg amount used) the amount to the “Credit Balance” (No problems here – all works fine).
Orders have (in short)
- Payment Status (Drop list: Credit, To Pay, Cash, No Charge)
- CD Cost (from Recordings table)
- Total Cost
When a quantity to purchase is entered, the “Total Cost” is calculated based on the “CD Cost” (This works fine).
This is what I cannot work out.
- How can you debit the “Total Cost from the “Credit Balance” When a CD is sold to a customer (when “Payment Status = “Credit”)
- The “Total Amount” needs to be debited against the “Credit Balance”. (Credit balance $50.00, total cost $10.00 therefore the Credit Balance = $40.00).
- I can get it to work manually by entering a neg dollar amount (say -10) in the “Add Credit Amount”.
- Not sure how to do it automatically, calculation or script. I would say a Calculation but not sure how to go about it.
- I am not even sure if I have done this the right way.
Maybe someone could just point me in the right direction that would be a great help – thanks
Hope I have explained this OK