Looking a price from a volume discount details table

App Development
1

Hello everyone,

I have a parent table with a requested quantity field and I need to pull the discounted price from a details table that contains volume pricing:

Parent table:
Requested_quantity

Details table:
Min_quantity , Max_quantity, Price
1, 99, 250$
100, 499, 200$
etc…

If the user enters a quantity of 150, I need to lookup a record where {Min_quantity}<{Requested_quantity) & {Max_quantity} > {Requested_quantity} but I haven’t found a way to do it yet.

I thought of using a Sum complex formula as the result would always include a single record but conditions can’t compare 2 fields, just a field with a value.

Has anyone found a way to do this?

Thanks!