Relating fields question

[alopas]

Hi,

I have two different databases. The first one is for colors, and for each color there is a code. In the other database I have included items that can have different colors. I have made a pull out menu that shows for each color name field the different color names available. I would like to know the way of showing the code for each color once you select the color name, without needing to enter the code manually. That is, when I enter "color name 1", it automaticly shows the correspondent code in "color code 1", and the same for color 2, 3, etc (in the same layout) Any help is very much appreciated!

[Kurt Knippel]

Assuming that every color name is UNIQUE, simply define a relationship between the two file by Color Name.

Then show the related color number.

[alopas]

Every color name is unique, as well as every color code. I have already related both files, but I am not sure how to relate the different color names fields with their correspondent codes fields within the same layout (there are more than one color code field and name in the layout) If I only relate the files it only shows the code for the first color. I need to store in new color code fields the codes for each different color name, but I don't know how two relate both fields within the main database. Maybe using a calculation field like:

color code 1("main database")=color code(from "colors database", code correspondent to selection of "color name 1" in "main database"):

... maybe I am complicating things and it's more simple than this!. Thank you for your help.

[Fuper]

It sounds like you want to have several color codes for each item. It also sounds, then, like the trouble you are having is that you want to use a separate field for each instance of a code, AND you don't want to have to create a relation between each code field and the color file.

If it is appropriate, consider using a single code field and a single color field, and then either making them repeating fields OR showing them in a line-item portal. (The line-items would reside in a third file.)

As far as a portal: You may say, "This is only one item, so why would I want to coomplicate things by putting its colors in line items in a separate file and showing them via portal?"

But any item that can have two or more items attached to it is a candidate for such a relation: multiple addresses, multiple line-items. And multiple colors. They may not reside IN the item record itself, but they are viewable, printable, searchable, and displayable there.

As for not having to enter the code manually: