How does: INVOICE DETAILS::Discount Percentage get a value?
It is an auto enter calc of
Nevermind, I think I am confusing table occurrences here.
What do you call the table where you list your invoice items? Is it LineItems?
If so, LineItems should have a "Sales Price" or "unit Price" that looks up (copies) the current price from Products. This is so a future price change doesn't cause older invoices to recalculate, but instead still shows the dollar amounts for the price that was in place when the invoice was first processed.
Then you can set up a "DiscountAmount" field in LineItems like this:
If ( Discountable ; SalesPrice * Qty * INVOICE DETAILS::Discount Percentage )
This field will be empty if the line items is not discountable and 0 if the discount percentage field is zero or empty.
Sum ( LineItems::DsicountAmount )
will show the total discounted value for a given invoice if defined in Invoice Details.
And your lineItemcost calculation would be:
SalesPrice * Qty - DiscountAmount
The "lines' join table is called invoice items. It does look up the prices from the products table fine as I may want to change these mid order and not alrter all future orders..
I see how your calc works..
So I can just have my normal unit price field showing the price if discounted or the original price if not. And also the new discount amount which will be used to work out the totals or the invoice by returning a 0 if no discount etc etc...
Think you have answered my questions...