Lookup Value Within A Range

[mwb]

Hi Everyone,

I've got what I hope to be a quick question with a relatively simple answer. I'm new to FMP so this may be easier than I'm making it out to be.

What I want to do is set up a layout that will calculate purchase order prices based on quantity. I've got a table of part numbers with the respective "price breaks" per quantity. For example a qty of 10,000 units might cost $2 per unit, qty of 15,000 = $1.50... etc. The problem is that the price breaks are different for each item number. Where item "A" may cost $2 at 10,000 units, item "B" could be $10 at 10,000 units. I need to be able to input a quantity and have a calculated field (or script?) that will reference the item number and quantity, and return the appropriate price break.

I've been trying to "logic-out" the guts in Excel and am getting stuck there as well. Any input would be GREATLY appreciated.

[Fitch]

Is it just a single price break for each item?

Case( qty <= break_qty ; priceA ; priceB )

If each item has multiple breaks, then something like this:

Case( qty <= qtyA ; priceA ; qty <= qtyB ; priceB ; etc.)

Does that make sense? In both examples, the values on the right-hand side of the "<=" are related values from your part numbers table.

[mwb]

Thanks for your feedback. I got the formula working in Excel by using a vlookup. Now I just have to recreate in FMP somehow. Correct me if I'm wrong, but the "Case" function sounds kind of like an If statement. If (condition, value if true, value if false)...

I'll tinker with this and see if it works. Thanks!

[Fitch]

Case is less cumbersome if you have more than one condition to evaluate.

[mwb]

I don't know if I'm doing this wrong, but only the first two tests work. If my buy quantity is less than 10000, the test returns the "20000 unit price). Here is what I've got input.

Case ( Qty ≥ "20000" ; 20k Price ; Qty ≥ "10000" and Qty < "20000" ; 10k Price ; Qty ≥ "7500" and Qty < "10000" ; 7.5K Price ; Qty ≥ "5000" and Qty < "7500" ; 5k Price ; 5k Price )

In the event that buy qty is less than 5000 units I want the 5k Price... hence the last "5k Price" argument.

[comment]

I suggest you look at these:

http://www.fmforums.com/forum/showpost.php?post/294096/

http://fmforums.com/forum/showpost.php?post/300768/

[mwb]

Thanks for your comment, Comment. I created a price list table that lists out each price break qty and price for every item... kinda like what you mention here: http://fmforums.com/forum/showtopic.php?tid/195995/post/294096/#294096

What I don't know how to do now is setup the calculation. I want to be able to enter a PO quantity for an item and have it look up the corresponding price (or the next lowest break if in-between values). Can you give me an example of what this might look like?

[comment]

That's what the other thread explains. Note that this shouldn't be a calculation: you want to really lookup the price, i.e. copy the value into a field in the line items table. This ensures existing records will not be modified when the prices change.

[mwb]

Ok I got it working... sort of. I'm running into a snag when trying to lookup multiple buy quantities. For example, I want to enter a purchasing schedule...

PO 1 Qty: 20000 units

PO 2 Qty: 10000 units

PO 3 Qty: 5000 units

Final PO: 2500 units

I would like it to lookup the price breaks for these quantities, but what is happening is it only looks up the first field "PO 1 Qty" and doesn't analyze the others. Ideas? I think it's just an issue with my relationships, but not sure.

[comment]

No, the issue is with having multiple fields for the purchased products, instead of multiple records - see:

http://fmforums.com/forum/showpost.php?post/309136/

[mwb]

Ok I got everything working, with the exception of one little quirk. When I enter the quantity for the first purchase order the pricelookup doesn't return any value. However once I enter the second purchase order the lookup works, and I can then go back and enter the first quantity and it will reference price. My assumption is that it has something to do with the "Lookup Next" function, but I could be entirely wrong. Any ideas?

[comment]

Not really. Perhaps you should post your file (preferably simplified to show only the problem).

BTW, I wouldn't use lookup next when the relationship is based on two matchfields.

[projay]

Hi did you ever get this to work for you. Jay.

Need similar help.

[bcooney]

Jay,

Have you looked at the examples Comment linked to?

I've added to Comment's demo the Price Breaks for Products on an Invoice.

hth,

Barbara

InvoicesDemo-PriceBreaks.fp7.zip

[projay]

Hi Barbara thanks for your link I sent a PT or something like that. Jay.

[bcooney]

Got your pt. But, I prefer any questions to be added to this thread, bcs many people learn from our discussion.

Notice the new table PriceList and the relationship from Line Items to PriceList has two predicates. That is essentially the key to this technique. What didn't you understand about the demo?