How do I make a List from two columns - to display one and store another

Hi,

How can I make a list from any two columns out of a table to populate an enum.

Let’s say there are column A and column B in the table and I want to make a list of those two.

And in the enum, I want to display the values of A but when user select a value, I want to store the value from column B.

Is it possible?

LIST( [column a] , [column b] )

This sounds like a key vs label situation with a Ref type column.


If none of that helps, please explain your situation more.

1 Like

Hi @Marc_Dillon,

Thanks for the quick reply. The reason why I want to achieve something in this way is that if I were to use Ref, I could only have one key & one label per table - therefore I will have to create multiple tables for different sets of key-labels. So instead of having multiple tables, I want to have a single table with multiple columns.

Let me know if my understanding on the ref limitation is incorrect.

Do you know about these?


1 Like

A table can only have one key column and one (textual) label column. There is no way around that.

1 Like

Thanks, @Steve.

I guess I don’t have much options then.

If I have to use different tables, is there still a way to create dependent enum across multiple different tables?

Yes.

However, I’m not sure how dependent dropdowns relate to what you have originally asked here. Perhaps you can describe your situation more (screenshots help) and someone can give some bigger picture advice?

1 Like

Hi Marc.

The dependent drop down was an off-topic question actually.

However, what I was trying to achieve was the following:

I have Division, District and Township enums (dependent ones) which I want to display in my native language, but when user pick the selection, I want to store the values in English to the spreadsheet.

I could do dependent enums easily if all three columns are in the same table but since now it will have to be separated due to key-label limitation, I am not sure how I can still make them dependent on each other.

Ahh, I see. That is an interesting problem. Not sure what the best way here is, but you can definitely accomplish that with separate tables. You’ll just need to enter your own valid_if expressions to create the dependent dropdown behavior.

So you’ll create a Division table, with the English as key-column, and the other language as label column.

Then you’ll create District table, with similar 2 columns (English key, translated label), and a third column to hold the appropriate Division value (English key).

Then for District’s valid_if you’ll input:

FILTER( Districts , [Division] = [_THISROW].[Division] )

And I’ll let you extend that process for Township, but feel free to post here again if you can’t figure it out.

2 Likes

Or the label column can be a virtual column that chooses the appropriate translation.

1 Like

Can you explain a bit more on this, Steve?

1 Like

Consider a table like this:

English Spanish Japanese German Label
Yes Si Hai Ja Si

English would be the key column. Label would be a virtual column with an App formula expression like:

SWITCH(
  USERSETTINGS("Language"),
  "Spanish", [Spanish],
  "Japanese", [Japanese],
  "German", [German],
  [English]
)

Assuming you’re using User Settings to allow the user to choose their language.

See also:




3 Likes

Hi Marc,

I’m having the following error in the Valid If of the District Enum.

Error in expression ‘[uniqueID].[division]’ : Unable to find column ‘division’

If I remove [_THISROW], the error goes away but District enum isn’t dependent on the Division.