1 Reply Latest reply on Apr 2, 2013 8:50 AM by philmodjunk

    Debit - Credit Amount

    trevor4370

      Title

      Debit - Credit Amount

      Post

           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).

           -----------------------------------------------

           Tables

             
      •           Customers
      •      
      •           Recordings (cd’s)
      •      
      •           Orders
      •      
      •           Credit balance

           Credit balance has

             
      •           IDCredit
      •      
      •           ID
      •      
      •           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)

             
      •           IDOrder
      •      
      •           Payment Status (Drop list: Credit, To Pay, Cash, No Charge)
      •      
      •           Quantity
      •      
      •           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

        • 1. Re: Debit - Credit Amount
          philmodjunk

               I'd use a script to put that negative mount into the add Credit Amount field of the related Credit Balance record. Presumably, there is some way set up that "marks" a given order record as complete. (We use a status field that changes from "pending" to "printed" when we print the receipt.) If you either use a script to initiate that status change or use a script trigger on the field that you modify to change the status, the script thus performed can check for an outstanding credit balance and deduct the current order amount from that credit balance.