AnsweredAssumed Answered

Best Price algorithm

Question asked by on Oct 18, 2018
Latest reply on Oct 19, 2018 by fmpdude



I am working on this best price algorithm. It works out the cheapest price.


I have an Orders, OrdersLineTable, Products & ProductsPriceBreaks table.


User may buy a product in bug quanity; than using the price break table I would like to get the cheapest price.


Here is step by step algorithm to work out the cheapest price.


Quantity Required : 450


Price Breaks:     


PckQty Price

60                          £4.66

500 £3.86

1000 £3.66




Divide the Quantity by the lowest Pricebreak pckqty and round up.

450 / 60 = 7.5….


Round up = 8.


Step 2:

Multiple the answer of step 1 to the lowest price break pckqty.


60 x 8 = 480



Step 3:


Multiple Each of the price break cost with answer of step 2.


480 x £4.66 = £2,236.80


It has looked at the bracket of the price breaks and automatically picked based on reviewing the 480 against the price breaks above and seeing this falls in the bracket below our price break @ 500.




A separate calculation should then evaluate if the price break is worth taking advantage of and do the following.


To get price break we need to buy over 500kg


Price break 500 / MOQ 60 = 8.33


Round up = 9


9 x 60 = 540


540 x £3.86 = £2,084.40


This compares to the £2,236.80 and we are seen that it is more cost effective to order the 540kg. In most cases this won’t actually be the case unless the price break is significant but we do have the odd product that will require this calculation.


I can do the first couple of steps of this price algorithm but not sure How I can working out the cheapest.

I have attached the demo file.