Quantity price break lookup question

[jpelkey]

Hello again. I have another serious issue. It seems like I end up trying to do the hardest things possible. Anyway, here is the dilemma (in as plain o' english as I can muster...)

I want to create a system that allows an end-user to select a product, enter a quantity, and then have the system look up a quantity break unit price. In other words, for each product, there are breaks: 1-5 = .15, 6-9 = .14, 10-19 = .13 and so on, and if you enter 7 into the quantity field, the system returns .14 for the unit price.

Now, the reason I posted this in the Portals forum is because I have it set up where there is a file called "Products," and a file called "Quantites" that are related by Product_ID. The quantites file is a huge file that stores a "qty_low," "qty_high," and "Unit" fields for each product. I did it this way because some products may not use quantity break pricing, some may use only 3 breaks, and some may use 20.

I am stuck at figuring out how to move the unit price from the "Quantities" file to the "Line Items" file without using a script; calculations or lookups only.

Help.

JP

[Fitch]

No calc needed -- just use a lookup, but set the lookup "If no exact match" to use the next lower value from your Quantities file.

[jpelkey]

OK, I think that's the right track; thank you. But there's one catch: It also needs to match the Product_ID as well. So, for example, in my "Quantities" file I have 6 records that relate to Product_ID 001 (so Product 001 uses 6 quantity breaks) and I have 4 records that relate to Product_ID 002 (4 breaks), and in my "Line Item" file, I have a drop-down for "Product_ID" and a field for "Quantity." I need to be able to enter Product "001" and Quantity "12", and have it look-up the correct number.

As it stands right now, its not actually separating the product IDs, so if I have Product 002 selected, it may still take a price from Product 001 breaks.

Thoughts?

JP

[Fitch]

In both Quantities and Line Item, you'll to make a calculated field that concatenates Product and Quantity:

Product & "." & Right ("0000" & Quantity, 4)

(You can use more or fewer zeroes depending on your needs. Also, the calc above could work as a number result, but your Product ID has leading zeroes, so maybe you'll want to use a text result -- in which case you can use an underscore or dash or whatever as a separator, you don't have to use a dot. )

Now make a relationship and lookup based on your new concatenated key fields.

[jpelkey]

Tom:

Thanks for your help. Everything works great with the addition of a "Max. Quantity" and "Max. Quantity Price" field to handle anything over a particular range. Wow, that took all day to figure out, when I thought it was going to take an hour or two... Whew. I ended up with hilarious calculation results like "2.005000" for a relational index.

Again, thanks a million.

JP