Much depends on the "rules" you define for discounting and whether you want to record the discount as only a total amount at the bottom of your invoice or by deducting it from the unit price of the individual items in your lineItems portal. I'll stick with just a discount total for the entire invoice for now as it's a little simpler to set up.
I would assume you have these tables:
You may be able to use fields in Products to document each type of applicable discount or you may want to establish a separate related table of discounts. The fields and data for documenting your example discounts might look like this:
ProductID MinQty MaxQty Dtype Damount
1234 5 9 1 1.00
1234 10 9999 2
And to get a count of total products for each ID for just the current invoice, make a new occurrence of LineItems and link it like this:
LineItems::ProductID = LineItemsSameProductInvoice::ProductID AND
LineItems::InvoiceID = LineItemsSameProductInvoice::InvoiceID
You can define a calculation field, cInvoiceQty, as:
Sum (LineItemsSameProductInvoice::Qty )
To compute the total of each type of item ordered.
You'd link your discounts table to LineItems like this
LineItemsSameProductInvoice::ProductID = Discounts::ProductID AND
LineItemsSameProductInvoice::cInvoiceQty < Discounts::MaxQty AND
LineITemsSameProductINvoice::cInvoiceQty > Discounts::MinQty
Then, once the line items are all selected in your portal to line items on your invoice layout, you can run a script that loops through the line items records and checks first for a related record in Discounts and then uses the Dtype and Damount fields to determine how to apply that invoice.
Thanks. I think I understand most of it, but I'm pretty green, so i'll try and see what I understand.
What is Dtype? and is the LineItemsSameProductInvoice the new occurence of LineItems or a new table all together?
Dtype is just a field where you specify what kind of discount. Your example showed two types of discount one for a dollar off each item and one that deducted the cost of the 10th item. Thus, I gave it values of 1 and 2 as a way to distinguish what "rules" would need to be followed in order to apply the discount.
LineItemsSameProductInvoice is indeed a new occurrence of LineItems.
I'm not sure I understand where it gets a quantity for this line Sum (LineItemsSameProductInvoice::Qty )?
This is the Quantity field defined in line items so that if someone purchases 3 identical socket wrenches, you can make a single entry in your line items portal with 3 in the quantity field. Since LineItemsSameProductInvoice is an occurrence of LineItems, it has every field defined in LineItems. By referring to LineItemsSameProductInvoice, the relationship matches to all line item entries for the same invoice and same product ID and the sum function totals up all the qty fields for those records. This way, whether you have 3 line items for "socket wrenches" with 1 in each qty field, a single line item with 3 in the qty field or 1 with 1 and the qty and a second with 2 in the qty field, you get the same total qty, 3 so that you can use it to determine what discount might apply to them.
Keep in mind that not all details of the looping script have been discussed yet. One issue is to keep from applying the same discount more than once should there be multiple line items in the invoice with the same productID.