Lookup Problem

[johnlyons]

I am having trouble with the following:

Lookup based on Sell price with a relationship between 2 tables on Model, A & B fields.

Here is an example of some data

Model A B Sell Price

SRAC 6 12 $4.42

SRAC 8 8 $4.21

SRAC 8 12 $4.76

When I enter:

Model A B Sell Price

SRAC 7 12

I get Sell Price = $4.21 which is how the 'Copy Next Higher value' is working. I would prefer to get the result of $4.76 so in theory the system would first match on Model, then on A, then on B. If B wasn't matched it would then take the next higher value of B.

Can this be done?

[Kent Searight]

Yes, it can be done. Try this.

ModelPrice.zip

[comment]

I'm not sure this is as simple as that. Suppose the pricelist goes like this:

SRAC 1 1 $11

SRAC 1 9 $19

SRAC 3 1 $31

SRAC 3 9 $39

and the Entry is "SRAC 2 5".

A simple multi-criteria relationship, with lookup next higher, will return the price of $19.

IIUC, the correct price in this case is $39.

I think a better approach would be to define the relationships thus:

LineItem::Model = PriceList::Model

AND

LineItem::A <= PriceList::A

AND

LineItem:: <= PriceList:

and sort the PriceList records by A and B, descending. Now the first related record in PriceList has the correct price.