Devin Posted March 5, 2016 Share Posted March 5, 2016 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 More sharing options...
comment Posted March 5, 2016 Share Posted March 5, 2016 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 Link to comment Share on other sites More sharing options...
Devin Posted March 5, 2016 Author Share Posted March 5, 2016 Thats makes things so much simpler. I was trying the hard route once again and over thinking it. Works great. Thanks Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 2966 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 accountSign in
Already have an account? Sign in here.
Sign In Now