Value lists in calculation fields

[Phillip Prahl]

Hi,

I have a calculation that starts by getting an "id_user" value from the table "checkins". Instead of returning this id number, I would like to use it ("id_user") to look up a matching "user_name" in table "users".

Is this possible?

Right now I solve it by the common practice of creating a popup menu where the first value is the id and the second the name. I then only show the second field.

However, I was curious if I could somehow do all of it in the calculation field so I could insert the name via a merge field and not having to use a popup menu.

Thanks,

Phillip

[comment]

Right now I solve it by the common practice of creating a popup menu where the first value is the id and the second the name. I then only show the second field.

However, I was curious if I could somehow do all of it in the calculation field so I could insert the name via a merge field and not having to use a popup menu.

If you have a relationship with the "users" table, based on matching the selected "id_user" value, you can simply place the "user_name" field from the "users" table on the layout, no calculation field required.

[Phillip Prahl]

Thank you - that makes sense. However, I was trying to avoid adding another TO. 

Perhaps my question is better phrased as:

Is it possible to access a value list in a calculation field?
(Similar to accessing a dictionary in python?)

[comment]

Is it possible to access a value list in a calculation field?

Yes, it is possible through the ValueListItems() function. However, only the first field can be accessed that way. In order to calculate the value of the corresponding second field, you would have to first define another value list, using values from the "user_name" field and sorted by the "id_user" field, and then use a calculation like this one: http://www.briandunning.com/cf/908

Or you could just place another instance of the local "id_user" field on the layout, format it as pop-up menu and disallow entry.

Yet another option is to have a calculation field = users::user_name and set it to evaluate from the context of a TO that is related to the users table.

Edited September 26, 2015 by comment