You seem to be missing a unit price field in your products table. Shouldn't adding a new record to Invoice|line items look up a unit price from products?
There are many different ways you might set up discounts and promotional pricing. Can you explain in detail how you want these promotions to work?
Good catch...Im just sandboxing the solution so some fields are missing. But yes, I would incorporate an amount in the products table.
I have certain Products :
Table : Products
ID Name Amount
1 Eyeglasses $300
2 Contact Lenses $100
3 Exam $90
I want to create promotions on these products that would be grouped into Categories, such as :
Table : Promotions
ID Group Product Discount
1 1 Associate Discount 1 Eyeglasses 30% off
2 1 Associate Discount 2 Contact Lenses 30% off
3 2 Friends and Family 2 Eyeglasses 40% off
4 2 Friends and Family 3 Exam 50% off
Then, from the Invoice Details layout I want to be able to add promotion Categories from a portal (From the above example, either 1) Associate Discount and/or 2) Friends and family.
Table : Promotion Line Items
1 2 Friends and Family
From the same invoice layout I will choose what products to add through a portal to Invoice Line Items. This row would include the Product Name, Amount, Discount and Total (If any discounts match from above).
Table : Invoice Line Items
ID Product Amount Discount Total
1 3 Exam $100 $50 $50
2 2 Contact Lenses $100 0 $100
I hope that clarifies things. Thanks!
So if a Discount Category is selected on a given Invoice, certain products will have a percent discount specific to that product-discount category combination applied to the price? And any number of items can be purchased at that discount rate?
If so, your initial relationships, for setting up your discount rates would look like this:
Products::__Item_ID = ProductDiscount::Item_ID
You could then use a portal to ProductDiscount to create each relevant discount with a DiscountCategory field set up with a value list of the different discount categories.
Then, to use a looked up value auto-enter setting to copy any such rate into a line item record, you'd need this relationship:
LineItems::Item_ID = ProductDiscount|LineItem::Item_ID AND
LineItems::DiscountCategory = ProductDiscoun|LineItemt::DiscountCategory
ProductDiscount and ProductDiscount|LineItems are two occurrences of the same data source table.
This allows you to copy the relevant discount rate into line items where a calculation can include it in the calculation to compute a line item cost. But note the fact that this requires each lineItem record to have the DiscountCategory specified. Since you aren't going to want to select a discount category over an over for each line item on a single invoice, you'll need to use some method to automatically copy this value from a field in your invoices table.
Make LineITems::DiscountCategory a field with global storage. Use a script performed by both OnRecordLoad and OnObjectSave (on the Invoices::DiscountCategory field) to perform a script that uses set field to copy this value to that global field.
Make LineItems::DiscountCategory an unstored calculation field that simply copies the value of Invoices::DiscountCategory
Use an auto enter setting on LineItems::DiscountCategory to copy over the value.
The trouble I am getting into is the "method to automatically copy" the category to the line items. I can carry something forward if I only apply a single discount. What if there are multiple discounts applied per invoice? Than I would have to figure out which DiscountCategory I wanted to copy to the line item keyfield. What if there are multiple DiscountCategories that can apply to the same line item?
In my current solution, I select the discount from every line item. This is time consuming and would be difficult for reporting because I would possibly have many of the same discounts per invoice. I want to track which discounts were applied to which invoice, not necessarily the line items.
The discount category could be a drop down list field in Line Items. It could even be a check box group. But the whole idea behind copying a value from Invoices was based on the assumption that you would only specify one discount option for an invoice and that it would potentially apply to the same invoice. This saves you the trouble of selecting this value over and over again, but limits you to a single discount option.
You aren't really limited to having it only one way here. You could select a discount category from a field in Invoices that applies to all line items and then, if you place the field in LineItems that copies this value into the portal row, you can edit the value for those line items where you choose to apply a different discount option.
What if there are multiple DiscountCategories that can apply to the same line item?
Then a check box field sounds like your best bet here. This will complicate your discount calculations, however as you will need to combine all of the discount values for that product into a single discount. You'd need to set up an auto-enter calculation that uses the sum function to copy the total combined discount percentage into a field in the line items table. (The return separated values entered into a field via a check box format will match records by any one of the check box values, so if you select two discount check boxes, line items now matches to two related records in the ProductDiscount table.)
I see what you mean. I ended up using 3 different fields to select various discounts. I also had to set up three different calculations fields to figure the amount to apply after the discounts.