# Getting the Best Price.

Latest reply on Oct 10, 2018 by Bentech

Hello Community,

I have already asked this question in different scenario. I am asking this question again because I have hit another scenario.

I have an products table and I have products prices table.

Products may have multiple prices.

e.g.

Products

ID       Product

1      Black Rubber

2     Red Rubber

ProductsPrices

ID     ProductID     Qty     Price        Weight

1     1                      100     £1.50          98

1     1                      500    £1.40           390

1     1                      1000   £1.20         789

1     2                      100     £1.58         53

1     2                      500     £1.45          346

Example:

Product 1 has following prices.

ID     ProductID     Qty     Price          Weight

1     1                      100     £1.50          98

1     1                      500    £1.40           390

1     1                      1000   £1.20         789

What I want my FileMaker application to do is work out best price based on user input quantity.

Lets say user is buying product 1. And user is buying 50 Qty. So price it should get is 100 X 1.50.

If user is buying 130 Qty. The price user should get is 500 x £1.40.

If user is buying 640 Qty. The price user should get is 1000x£1.20

So whatever Qty user is buying; it need to round up to next nearest qty in pricing table.

How this can be achieved.

Many Thanks.