Hello! I have a table(X) with a list of names with values. (say a = 1, b = 2, c = 3) in another table(Y) I want someone to select the name (from value list a, b or c) and I want it to look up the value from my tableX.

So I've used the case calculation like so:

Case ( name = "a"; tableX::a ; name = "b" ; tableX:: ; name ="c" ; tableX::c)

can someone tell me where I'm going wrong? I know if I replace the "tableX::a" with a number it works but I want it to look up the field value.

Thanks for your time!

If I understand correctly, might you be better off creating a relationship between Table X and Table Y based on relating TableX::Name to TableY::Name_List.

Then you can create a portal display of the data from TableX based on the name/relationship selected in TableY.

Phil

Hmm I'm trying to understand what you mean...basically i want a field in layout that I choose out of (eg) red, blue, green - a value list? then based on what i chose, the colour refers to a number 1, 2 or 3. sort of like having a field of fields if this makes any sense?!

walk me through it

thanks for replying!

Basically you are going to create an indexed list in table 2 of all of the names that you have in table 1 and then you are going to create a relationship joining tables 1 & 2 by the name fields. You will then place the value filed from table 1 into the layout for table 2 based on the relationship. Now as you choose a name in the indexed field in table 2 the associated value will display.

Please see the little sample file I've attached to this reply.

Phil

NameRelation.zip