Value list based on two fields

[Kukuberra]

Let me see if I can explain this clearly. I have one database (MetaDB) that contains unchangable data. When an item comes in to recieve it there is a second database (SampleDB) that a new record needs to be created for each item of that type recieved.

So I have it set up that they create a new record and type in the item number. The item number is related and it pulls up a value list for the color name. That works perfectly. The color name is a pull down that gives them the list of red, blue, or green or whatever is related to the item number.

The problem is that there is also a color code associate with each item number and item color name. These color codes are not standardized. The code for item 34567 red could be r4566 and then for item 2345 red it could be t5673. So when I made the color code a look up on the color name it would grab the wrong number. If I make it another value list pull down the users still won't know which code number goes to which color.

It seems there should be an way to make a lookup based on two relationships but I haven't found it yet.

Any Ideas? Thanks,

Kukuberra

[ October 17, 2001: Message edited by: Kukuberra ]

[LiveOak]

There is indeed a way to base a value list upon a relationship. First create a new file to hold all the colors (I'll call it "ColorDB"). It will need the fields:

Item Number

Color Code

Color Name

Create a relationship between SampleDB and ColorDB based upon Item Number matching Item Number. Define a value list based upon this relationship (not on a file) and displaying both Color Code and Color Name. Attach this value list to the field Color Code in SampleDB. The relationship will limit the values displayed to those for the Item Number of the current record in SampleDB. This will enter the Color Code. To display a Color Name field in SampleDB, you will need a relationship between SampleDB and ColorDB based upon Color Code. Use this relationship to display the related Color Name field from ColorDB.

-bd