Confused with what should be a simple calculation.
Hi there, I am trying to do something which I would have thought simple however each and everything I have tried has given me a duff result. Now I've tried this so many ways I think I've gone snow blind!
I have a database of products, each product has 3 fields to enter potential discounts, i.e. you can set the quantity to buy to get a discount. Thus there are three potential price breaks, and three field to set the discount.
There is also a 4th field to set an override discount which takes presedent over all else.
So then to calculate the result I want it to look at the overide discount if thats not empty then it can base the final calc on that. I.e qty*price*override discount.
or pass it through to the 3 discount fields which then must assess if the qty required >= one of the price breaks then use that for the final calculation. i.e. qty*price*discount field
or if there is nothing entered then it just goes into a final calc of qty*price.
This is set into a global value to pass off to another database.
What I can't seem to master is getting the whole thing to work, I can get some bits working but not all. I have tried various versions of case or if requests and nesting them but its going wrong somewhere. I dunno now if I'm even using the right statement.
where i seem to be having problems is if the discount fields are left blank and handling the price breaks.
Any advice would be appreciated as I just can't seem to get my brain around this one!