Hello,

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**

**-------------------------------------------------------------------------------------------------**

** **

**Step1:**

**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. **

Hello,

Off the top of my head, you should change this relationship:

since Qty will only ever return the related record whose PkgQty matches exactly, i.e. it cannot be both greater than AND less than the related pkgQty. Change this so that the OrderLineItems::Qty >= OrderLineItem_ProductPricing::PkgQty.

Then, change your OrderLIneItems::UnitPrice to auto-enter the OrderLineItem_ProductPricing::UnitPrice.