Get the nearest price

[archrid404]

I have this problem on how to get the nearest price.

I have field qty and price ex. qty 100 price $41 and qty 250 price $39.

how to calculate to get the correct price if i enter 105 i should get 39

[Steve Martino]

More info needed, but maybe you can use an auto-enter calc on the price with a case formula to account for your price points.

[comment]

If I understand your question correctly (which is not at all certain), you want to lookup the price from a Prices table, using a relationship based on both ProductID and Quantity - see:
https://fmforums.com/topic/107383-sliding-scale-for-pricing/?do=findComment&comment=484356&_rid=72594

But your example does not make sense to me, because if the minimum quantity for the price of $39 is 250, then for the quantity of 105 you should be getting a price of $41, not $39.

 

36 minutes ago, Steve Martino said:

maybe you can use an auto-enter calc on the price with a case formula to account for your price points.

That is not a good idea. Prices change over time and hard-coding them into calculation formulas is not good practice.

 

 

[archrid404]

On 10/19/2022 at 9:34 PM, comment said:

If I understand your question correctly (which is not at all certain), you want to lookup the price from a Prices table, using a relationship based on both ProductID and Quantity - see:
https://fmforums.com/topic/107383-sliding-scale-for-pricing/?do=findComment&comment=484356&_rid=72594

But your example does not make sense to me, because if the minimum quantity for the price of $39 is 250, then for the quantity of 105 you should be getting a price of $41, not $39.

I think this is the correct answer, I was looking other way around. Another question is what if i enter 40 i should get price of $41

[comment]

1 hour ago, archrid404 said:

Another question is what if i enter 40 i should get price of $41

I am sorry, but you will have to explain your logic in more detail. If you are trying to define your price breaks by the maximum rather than the minimum quantity, then you will have a problem defining the last (lowest) price brake's maximum quantity. Strictly speaking, it should be infinity - which cannot be expressed in Filemaker. And an arbitrary high number is not good practice, IMHO. But technically, it's possible - you only need to reverse the ≥ relational operator to ≤.

 

Edited October 21, 2022 by comment