Creating dynamic deductions for use in invoices

[iordanis]

Hi,


It seems like a puzzle for me, so I call for some help:


I have an invoice master table in relation with an invoice detail, for the items, prices, quantities etc. I want to create Deductions table, where I want to store various types of deductions on an invoice's final price, for each type of invoice (so Invoices - Deductions are in a Many-to-Many relation), either as a percentage of the total quantity, or as an absolute value.

So, i need to figure out where to store the SQL to be run automatically, but without having to update it, to work for a particular invoice. In other words, the feature I'm trying to accomplish is, when the deduction (invoice_ID, deduction_ID, Amount (default $0.00) ) is inserted to the N:N table, its Amount column must be evaluated automatically for that deduction, based on total quantity of the invoice.

I hope I'm describing the issue adequatelly. A.I. didn't help that much.



TIA.

[Welshgasman]

I would expect you to have a range of discounts per product and/or per total value of product or invoice.?
Then just look for the appropriate value. Could be a UDF that you use in in your query?

[Micron]

If you're calculating this on a form, perhaps have a combo that looks up the deduction. When chosen, a calculated control (textbox) on the form uses the the next column of the combo to calculate the deduction amount. This assumes that the next combo column contains the deduction factor. If this is all for the invoice total, then you'll need to store the deduction factor along with the rest of the invoice header fields. If it's to be based on line item basis, then store it in the line items table.

[iordanis]

My invoice deductions table is like this (discuss if it needs change)

INVOICE_DEDUCTION
--------------------------
invoice_id PK
deduction_id PK
amount <- this column should be auto-evaluated when the deduction_id above, is changed.

the deduction_id is a combobox using data from the DEDUCTION table

DEDUCTION
--------------
id PK
description
is_percent_or_absolute (T/F)
value (decimal) <- either the percentage or the absolute value depending on the previous column
order_of_execution (for future use)
SQL_to_execute <- this holds the query to execute, when deduction_id is changed above.

Note:
The thing is that the SQL_to_execute column should:

1) in some cases have a numeric parameter to work with the invoice that the invoice_id column holds above (for the percentage deductions, based on the total QTY of the invoice) and
2) in other cases, have a text parameter that holds the customer username (for the absolute value deductions based on the customer)

I'm trying to create a generalized solution on the automation of the SQL execution call

[Micron]

If I get the picture, I think you're going about this the wrong way in trying to do it with sql. First, you still haven't said if this goes against line items or the invoice header. Regardless, it is a calculation and I suspect you intend to store it, and that's generally ill advised but I think it's ok for invoices because you need a record of the discounted amount owed. So I see your deductions table more like

DeductionIDpk	DeductionType	Rate	Notes
1	Friends	-10%
2	Family	-20%
3	etc	-5%
4

then the invoice line item shows item, quantity, description, whatever else, gross amount, discount amount (calculated as I said) line net amount. Then the net line amounts carry up to the invoice header, or footer as the case may be. There are others around here who do this for a living (or did) so I should let them chime in.

[mike60smart]

Are you able to upload a copy of the database?