I have a calculated field that does multiple Case tests against several related tables and returns a text status value (i.e. Approved, On Hold, etc) based on the results. Rather than hard-code the status strings into the calculation, I'd like to use a value list, in particular one created from a table, but I can't seem to find a way to do so directly.

I know I could use a global field with CR separated values and the various values functions, but since these statuses already exist in their own table I'd like to avoid that redundancy if possible.

Am I missing something?

If I understand this correctly, your calculation should run along the lines of:

Case (

test1 ; 1 ;

test2 ; 2 ;

test3 ; 3 ;

...

)

Then fetch the text result through a relationship to a table where each text has a numeric ID.

Or, if you prefer to use a value list:

Let (

value# = Case (

test1 ; 1 ;

test2 ; 2 ;

test3 ; 3 ;

...

) ;

GetValue ( ValueListItems (...) ; value# )

)

Edited June 8, 200916 yr by Guest
Added another way