Quantity discount based upon steps...

[PaulRene]

The main case:

I have a invoice system that is based upon discount if quantity is over certain steps. Lets say one product costs 470, and every items over 10 costs 430, and every item over 20 costs 400 and so on. The number of steps is not the same for every product.

So the calculation should be like this (for one item)

Quantity: 27

The 10 first items (1...10) = 4700 (10*470)

The 10 next items (11...20) = 4300 (10*430)

The 7 next items (21...27) = 2800 (7*400)

The total amount for 27 of this product would be would be: 11800 (4700+4300+2800)

Total discound: 890 (11800 - [470*27])

Price per item: 437,04 (11800/27)

How on earth can I make this?!? ... calculation? repeating fields? relationship?

Another case:

The productID will be entered in another file (orderform), and the price 470 will be displayed automatically in the "price" field in the same file. The challange is that I want the "price" field in the orderform to change based on the value of the "amount" field. In the exsample the "price" should be 470 it it was 1 item of this product and 437,04 when it is 27 items in the "amount" field.

Can anyone help me with this challange? I

[Kurt Knippel]

For situations like this I would definately go with a seperate file to store your discounts and the related criteria for those discounts.

For example:

Invoice.fp5

InvoiceItem.fp5

Product.fp5

ProductPricing.fp5 <-- contains the stepped pricing information.

I can only see this really working with some kind of looping logic, although I suppose someone with stronger mathematical background can develop an algorithm to calculate the total. I would just use a loop along with the steps defined in the related file for that product to total the price.

I would also suggest that you use this model for ALL your pricing even if it has only one level, since this will make you logic more consistant and easier to deal with changing pricing levels.

[LiveOak]

Now you see why this is a common pricing practice! Setup a pricing file with fields as follows:

Break1 Price1 10 470

Break2 Price2 20 430

Break3 Price3 30 400

.

.

.

BreakN PriceN

The price calculation is then:

Case(

Qty <= Break1, Qty*Price1,

Qty <= Break2, Break1*Price1 + (Qty-Break1)*Price2,

Qty <= Break3, Break1*Price1 + (Break2-Break1)*Price2 + (Qty-Break3)*Price3,

Qty <= Break4, Break1*Price1 + (Break2-Break1)*Price2 + (Break4-Break3)*Price3 + (Qty-Break4)*Price4,

.

.

.

If you have more quantity break points than you use, just make the quantities really large (10000000000000) so they will never trigger.

-bd

[PaulRene]

I think I got it right! Thanks for all your help!!!

My next challange is this:

What happens when the prices gets changed in the "Product" file.

I want the calculated value to be stored in the "Order" file.

Paul Ren