I need to be able to validate a field based on two others. I have a vendor database listing (in a portal) all of the items that my company buys from that vendor. The vendor database lists the vendor's name, and the company information. A portal displays all of the product information.

In the product database, I have set the item number to be a unique field. I figured that way there would be no repeats in product entries. However, I have run into the problem that there are duplicate item numbers for different vendors. Meaning Sears and Kmart may have a product with the item number 11111.

I need a way to keep the unique item number while makeing sure it is only being validated in one particular vendor and not over the entire list of vendors. I'm guessing that it will have to be some kind of calculation based on the vendor and item number but that is as far as I have gotten.

Any help would be greatly appreciated.

Jess

Validate a new Text Calculation field to be unique

= Product::ID_Vendor & "~" & Product::ItemNumber.

Edited January 25, 200619 yr by Guest

Not a calculation field, but a text field with auto-entered calculation (replace existing value).

Thanks for all of your help! It worked great.

Jess