Trying to figure out how to handle this.

Have 4 Tables.. Order, Lineitems, Products, and PriceMatrix.

Products and PriceMatrix are related via SKU.

Products has a Portal to PriceMatrix to show all the unit price based on QTY.

Example

Up To Quantity

100  $1.00
200  $0.90
300  $0.85

The thought is if a customer buys 90 of this item they will pay $1.00 x90=$90

If they buy 205 it would be $0.85 x 205=$174.25 because 205 is over the 200 qty they get the pricing of the 300.

I'm needing a way write a calculation that can do the math based on the QTY ordered.

Thanks

 

 

This is not a matter for a calculation, but for a relationship. To make it easy, I would suggest you structure your PriceMatrix table a little differently:

FromQty    Price
1         $1.00
101       $0.90
201       $0.85

Then define a relationship between Lineitems and (another occurrence of) PriceMatrix as:

LineItems::SKU = PriceMatrix 2::SKU
AND
LineItems::Quantity ≥ PriceMatrix 2::FromQty

and sort the records from PriceMatrix 2 by FromQty, descending.

 

With this in place, you can lookup the price into a field in LineItems, and then do a simple multiplication.

Thats makes things so much simpler. I was trying the hard route once again and over thinking it.

Works great.

Thanks