I'm not sure which approach you need here:
Specify a discount % on the invoice and have it apply to all line items in the invoice--overriding the computed/looked up pct that was added automatically for all the items.
Specify a different discount % line by line to override the original discount for just that line.
Assuming the first option, you can define a number field in your Invoices table, DiscountOverride. Then modify your calculation in line items as:
If ( Invoices::DiscountOverride ; //compute percentage discount using DiscountOverride here ; // put your original calculation here )
Apologies for the delay Phil,
On my invoices, which are rental invoices, I have a cost involved for my rental, and each invoice may have more than one vendor. I would like to modify the discount amount for each involved vendor individually, so I can essentially adjust my cost once the items are on the invoice.
Right now, I have a vendor record with a default discount, when I add an item on an invoice that I buy from that vendor, it looks up that discount and performs the approprate calculation so I can cet get my cost. The flaw in the system is that I can't "renegotiate" my discount once the line items are added, without doing each one manually, or including a discount percent field in the line items table.
Maybe it would be best to perform this with a script? Either way, my end vision for this is to have a portal on each invoice that shows all the vendors I am getting items from for that invoice, and a discount percentage for each vendor so I can apply it to all related line items at once.
Sounds like you need another join table. The LineItems table serves as a join table between the rental invoice and the list of items that can be rented. Now you'll add a table that links the invoice to all vendors that supply one or more of the items listed in LineItems for this invoice. You'll need this table to record override discounts for each vendor. The table can "look up" the default percentage, but since these records are in their own table, you can "renegotiate" by editing the values in this new join table.
I'm guessing you have at least these tables in your database system:
Invoices----<LineItems>-----ItemsForRent>-----Vendor (>---- means "many to one")
We need to add a join table for Vendor_LineItems while keeping the above relationships in place:
LineItems::VendorID = Vendor_LineItems::VendorID AND
LineItems::InvoiceID = Vendor_LineItems::InvoiceID (Enable "Allow creation of records..." for Vendor_LineItems.)
where VendorsByInvoice is a new Table Occurence (TO) of Vendor. LineItems::VendorID will be a value looked up from ItemsForRent.
A script trigger will be needed to add records to Vendor_LineItems each time you add a record to the LineItems table for a vendor not already listed for this invoice.
If [Not Vendor_LineItems::VendorID]
Set Field [Vendor_LineItems::VendorID ; LineItems::VendorID // this triggers the creation of a new related record in Vendor_LineItems]
A number field in vendor_LineItems can look up the standard discount from VendorsByInvoice, but now you can override the discount by editing this field in your portal. Your LineItems calculations can then refer to this field in Vendor_LineItems to calculated the correct discount.
Phil, would this be something that is backwards-compatible with data already in the system?
Your new join table won't automatically populate with records for existing invoices. To show this for existing invoices you'd need to, either by hand or with a script, create the needed records in the new join table. But I don't think that you would really need to do that as this new capability would be primarily needed for all new invoices so that you can better manage the discounts.
Thanks Phil, I will dive into this and hopefully come out with a working solution!