# Work out the cheaper prices out of multiple prices.

Question asked by muhammad.ikram@contedia.com on Oct 2, 2018
Latest reply on Oct 4, 2018 by philmodjunk

Hello Community,

Got a tricky question.

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

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.

If user is buying product 1. AND USER IS BUYING IN 56 QUANTITY...The price I need is get is £1.50

If user is buying product 1 in 400 quantity. The price user needs to get is £1.40.

and If user is buying product in 700. The price user should obtain is £1.20

so I hope you understand this logic. So the higher the quantity user is buying we need to give user best price on that quantity.

How I can achieve this in FM

ALSO.

If user is buying product 1 in 130 Quantity. The best option is NOT £1.40 (Price of 500 quantity). The best option would be buying buying 2 x 100 quantities at £1.50.

Similarly, if user is buying 160 quantities, if user go for product price with 500 quantities that will be more expensive. So I want user to get 2 X prices of 100 quantities each. Because this will work out cheaper.

Overall,

I want FileMaker to work out cheaper price for user. It might round quantity to nearest quantity in Price table or may work out using multiple quantities.

Many Thanks.