How do I make a List from two columns - to display...

Ye_Myat_Aung · 08-22-2020 09:33 AM

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?

Marc_Dillon

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.

Ye_Myat_Aung

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.

Steve

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

Ye_Myat_Aung

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?

Marc_Dillon

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?

Ye_Myat_Aung

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.

Marc_Dillon

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.

Steve

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

Ye_Myat_Aung

Can you explain a bit more on this, Steve?

Steve

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.

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