Discounted price calculation
I have set up my invoicing database fine. My products table has a trade discount field, which has the data "1" if that product record can have a discount applied to it. Each customer has a different percentage discount which originates on the cutomer table - customers::discount. I have a look up field on the invoice table for this invoicedetails::discount. And for the products line tabel, invoice items, I have the field 'discountable' which uses a look up to the trade discount field mentioned earlier.
How do I apply the discounts to the prices? I had done the calculation using an
Discountable = 1 ;
invoicedetails_InvoiceItem_PRODUCTS::Sales_Price - ( invoicedetails_InvoiceItem_PRODUCTS::Sales_Price
* INVOICE DETAILS::Discount Percentage);
This is supposed to calculate the product price as the usual price unless it is discounted in which case it applies the customer's specific discount to it.
But I am unsure it this is the best way about it. I also want to show the what the total discount is on an order which would mean having seperate fields for discounted and normal price totals So I can subtract one from the other..?
on the actual order/invoice page layout, there is the field showing the looked up field, INVOICE DETAILS::Discount Percentage. If this is adjusted, I want the lines in the portal below to reflect this - is this possible or do i have to delete and re-enter the portal line records (invoice items)