# Price Matrix

This topic is 2919 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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

##### Share on other sites

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.

• 2
##### Share on other sites

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

Works great.

Thanks

##### Share on other sites

This topic is 2919 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account