Jump to content

Price Matrix


This topic is 2305 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

 

 

Link to comment
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.

  • Like 2
Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.