4 Replies Latest reply on Mar 26, 2010 11:22 AM by mdaracz

look up of prices with different levels.

Title

look up of prices with different levels.

Post

I have filemaker 11 Pro Advanced and I am trying to figure out the following problem. I am taking products from our sql system and deriving prices based on the customer, customer category, and the pricing level.  The pricing level is the quantity of the product that must be met before a better price kicks in.

In this case I have a product with teh following level

if you buy 1 - 499 pound of product the price is 1.91

if you buy 500 - 999 price is 1.49

if you buy 1000 and up price is 1.39

and so forth.

This is how the relationship is defined.

you will see that the operator is set to = for the mininmum_qty and pricing level.

this returns the values correctly only if I type in literrallly 1 pound or 500 pounds or 1000 pounds. There is no problem there and it works. I type in teh qty, and the correct level price is populated  But I have to make it so that if I type in numbers WITHIN the ranges mentioned above it ALSO pulls up the correct pricing.  How would you guys go about this problem. Thanks in advance!

• 1. Re: look up of prices with different levels.

what is the pricing level data?

Is there a min & max value? I assume here you are using sorted records to ensure you get the lowest pricing level first? therfore the next record is the max + 1 of the previous.

for starters I would think using = as the qty / price level operator is not correct... try <=

• 3. Re: look up of prices with different levels.

the relationship is defined to extract all the special pricing from the special pricing table in my sql server based on the criteria on the screen shot.

so this product for example has 5 special pricing records related to it. to be concise they look like this

record one

specialpricingcode = 1321

customerkey = 232

productkey =2131

pricinglevel = 1

priceamunt = 1.91

specialpricingcode = 1321

customerkey = 232

productkey =2131

pricinglevel = 500

priceamunt = 1.49

specialpricingcode = 1321

customerkey = 232

productkey =2131

pricinglevel = 1000

priceamunt = 1.39

specialpricingcode = 1321

customerkey = 232

productkey =2131

pricinglevel = 1500

priceamunt = 1.29

etc

using a different operator like the one you mentioned doesnt extract the right info.

so for example if i put in 1 pound the level referenced is correct and it changes the price to 1.91

but as soon as i put in 2 pounds the price level referenced is the next one (1.49)

• 4. Re: look up of prices with different levels.

@COMMENT THANK YOU that worked perfectly!