Problems building Quotes from Products

[ddtully]

Example:

We purchase widgets for resale to our customers.

We get these widgets from multiple distributors.

Each widget has both a manufacturer's part number and a distributor's part number.

Depending on who is cheaper or who has one in stock, we may quote one distributor over another.

When I build a quote, I would like to select the model widget (manuf. part number) and then select a distributor from a pull down menu. Once these two items are selected, I need the appropriate cost and distributor part number to come into the quote.

Right now, a relationship exists when there is a match between manufacturer's part number in both the Product Listing and the Line Item Quote. Based on that relationship, I perform an IF statement that should return a distributor part number when there is BOTH manufacturer part number and distributor match. Unfortunately, either my relationship or my calculation is returning the first occurance meeting the criteria. Any other combination returns a zero as specified by the calculation where it should return the next distributors pricing, etc.. Where am I going wrong ?

[Fitch]

Does your Product file look like this, each line = a record:

Widget......Distributor

A..............X

A..............Y

B.............Y

C.............X

C.............Y

C.............Z

And are you saying that when you select a Distributor, it pulls up the price of the first matching widget? If so, you probably need to use a concatenated key, that is:

manufacturer's part number & "-" & distributor's part number

Make a calculated text field like that in both files you want to join, and make a relationship based on that.

[ddtully]

Product File actually looks more like this.

Widget..............Distributor..........Distributor Part #..........Manufacturer's Part #

My calculations are looking for a match on distributor AND Manuf. Part #. When that happens, I should see the appropriate pricing and Distributor's Part Number. Unfortunately, it works fine for the first Distributor selected, but returns zero's for subsequent distributors.

[Fitch]

I think you will find the suggestion I gave you will do what you want. Create a new field, WidgetCalc, a calculated text field that combines your two keys into one. This is the Path of Least Resistance... you will be assimilated... resistance is futile.