Relationship Lookup not returning correct Record...

[Royce]

Greetings friends and fellow FileMakers... I need your help please...

I have a quotes database, an inventory database a quoteitems database (to build the specific product line for each quote) and a pricebyquantity database. Since the price of our products changes by quantity, I don't have a single price for an inventory item, I usually have 25 different price points, from 10 to a million. So I need to reach from the quote, thru the inventory to the pricebyquantity (PBQ). No sweat. But I have a problem. If I ask for the price of the itemnumber by one of the actual price points in PBQ (like 25, 100, 500, 1000, etc.) I get the correct price returned. [ NB: I am doing a Lookup in the quote price each to the PBQ so it fills it in automatically when the quantity and itemnumber are put in the quote (actually through PHP from the web, but that's all working fine...) ] NOW the rub... if I ask for a price OTHER than an acutal price in the PBQ (like 29, 145, 1122, etc.) It defaults to the next lowest price (which it should because I have the "Copy Next Lower Value" switched in the Lookup), BUT... it then ALWAYS pulls the next lower value from the FIRST item in the items database - NOT the item I am looking for. so no matter what item I am looking for, if I put in a quantity other than the specific points in the QBP db, I get returned the price from the first item in that dababase of all items ( so if the first item in my database is CDJackets, which it is, no matter what I put in, CDWallets, Discalopes, Digipaks, etc. it pulls the Next Lowser Value from CDJackets...

Now having gone back and read this post, I see that I am clearly not thinking clearly... but, I did, I think, explain the problem... can you point me, get me started, hint, cajole and otherwise help me? I know it's probably simple, I am just missing it... Thank you.

Kind regards,

Royce

[IdealData]

If you add another field in PBQ to show the UPPER boundary of the price for the item then you can use a different relationship setup.

Presuming you are using a '=' relationship you could change this to use '>' AND '<' to define a range between LOW and HIGH value for a PBQ. So..

22 > LOW_VALUE AND 22< HIGH_VALUE will get the price for 20-29 (say)

[Royce]

ID - Yes I can see how to make that work - thanks... but I also need to figure out why it's not doing what it should be doing (I want to know the logic behind why it's not happening so I can be a better FM programmer!) - so I need to keep digging....any other thoughts? Any one? Thanks...

[LaRetta]

Can you provide us with screen shot of your graph with the keys showing? We need to see how the ProductID tracks through the relationships. If the relationships are correct, the lookup should work as well (pulling the next lower value), as you've interpreted. Knowing the data types of these keys will be important as well; a break in data type can change the relationship on the child end.

[Royce]

Well, I woke up this morning with a start saying "it can't be that simple" (knowing full well it had to be that simple). Now either I haven't made this error in a long time, a long long time (to quote Obi wan) - OR, it's not supposed to work this way!

The relationship I had set up is -

Quantity (Quote) = Quantity (inventoryprices) AND

Item number (Quote) = itemnumber (inventoryprices)

I switched the order of the relationship to be

Item number (Quote) = itemnumber AND

Quantity (Quote) = Quantity (inventoryprices)

So now item number is first. I am assuming that the logic says something like, "okay, find the itemnumber first and stay there while searching for the quantity and once the right quantity is found take it but if not default to the next lower value. AND in reverse I had quantity first SOOOOO it found the first quantity which was the FIRST record group, STAYED THERE looking for the itemnumber, AND if it found a perfect match, pulled it - BUT not finding a perfect match on the inbetween quantity AND a good item number, it STAYED on the first record and pulled the Next Lower Value from the FIRST quantity/record it was on.... grrrrrrrr... I should have seen that instantly.... well, glad it's over.... now I can finish automating the web interface for doing quoting on line (if you like, see our website at www.datadisc.com).

Thanks for pushing me. When you started mentioning things it could be post, it got my mind working to go back, start at the top and work it down - and I guess my subconscious worked on it while I was asleep!

Thanks again.

Royce