Calculation to return contents of different fields in dfferent records

[schmegol]

I have a table with field 1, field 2, field 3. I am trying to figure out a calculation to return either field 1, 2, or 3 based on other field values in another table. This is kind of like picking values out of matrix where the vertical is the records and the horizontal is the field 1, 2, 3 in each record.

Does anybody have any experience with this?

[Vaughan]

Have your tried the case or choose function? Assuming the two tables are related together and the expression returns an integer beginning with zero:

Choose( tableb::field1 ; tablea::field1 ; tablea::field2 ; tablea::field3 )

Note how difficult this is to understand then tables are fields d not relate to anything real. Next time use real entities please.

[comment]

As Vaughan says, it would be best to describe what this is about. In general, a table with multiple fields for the same thing is poorly structured. It would probably be best to have a separate record for each value (perhaps in another table) and use a relationship with two predicates to pick the correct record.