Solved: How to populate a virtual column with values in th...

pawann · 10-06-2020 09:31 AM

Hello there!

I got stuck with a challenge I hope you guys have an idea on how to accomplish.

It’s for an app where we offer the user to choose a preferred language. Based on the selected language, all content should be translated, including EnumList values.
Translation already works for column names etc using a large ‘Translations’ table and a corresponding expression for ‘Display Name’

The problem I face is around offering the user a drop down field (EnumList) that is

made up on values that should be displayed in the user’s language
pre-filtered based on other user’s preferences
and that are concatenated with other column values.

The current situation:
Table “Users” holding all information about the registered app users:
note: there is only one row per user

the current users language is stored in Users[Language] as Text
the current users items is stored in Users[MyItems] as EnumList

Table “Contacts” holding information for each Item.
note: table contains multiple rows per ItemID

the id of an Item: Contacts[ItemID] as Text
the contact email address for an Item: Contacts[Contact_Email] as Email
the type of contact for an Item: Contacts[Contact_Role] as Enum

Table “Translations”:

has one column per Enum value from Contacts table.
row is selected by users language string [DE,FR,IT,EN]

What I want to achieve:
Goal 1: Create a virtual column that translates the Contacts[Contact_Role] into the users language.

I’m using this formula to translate a column name into the users language which works flawlessly:
ANY(SELECT(Translations[COLUMN_NAME],([Language] = LOOKUP(USEREMAIL(),Users,"Email","Language"))))

Because for the situation at hand, COLUMN_NAME actually refers to an Enum value from Contacts[Contact_Role], I thought I could replace Translations[COLUMN_NAME] with a SWITCH statement that switches the columns based on the Contact_Role in the SELECT statement above, similar to Steve’s solution to this question

ANY(SELECT(
	SWITCH([Contact_Role],
		"Plant Manager", Translations[PlantManager],
		"Maintenance", Translations[MaintenanceManager],
		"Supervisor Plant Manager", Translations[SupervisorPlantManager],
		Translations[No_Contact_Role]
	),
	([Language] = LOOKUP(USEREMAIL(),Users,"Email","Language"))
))

But this results in an error saying “Unable to find column ‘Language’”.

When replacing [Language] with Translations[Language], it results in the following error:
Cannot compare List with Enum in (Translations[Language] = ANY(SELECT(Users[Language],([Email] = USEREMAIL()))))

What am I missing here? Any guidance, ideas or hints are highly appreciated!

Goal 2: Concatenate the virtual column with other information.
Once goal 1 is achieved, I guess it should be easy using CONCATENATE() formula to add other info, either directly in the expression of the virtual column from goal 1 or as a new virtual column à la CONCATENATE([Goal1VirtualColumn],[otherColumnsOrText])

pawann

Hi Steve, I went for your suggestion with the virtual column holding the calculated translated value based on the user’s language. It works very well. Thank you very much!!

When using a prefix in the term to identify the field, it is even easy to have only one ENUM translation table that holds the translations of all ENUMs for all the different fields.

Example:
‘Term’ column enum values for [Field1] are:
Field1_Enum1 , Field1_Enum2 , Field1_Enum3

Using this expression to suggest all translated ENUMS that include the string “Field1”:
SELECT
(Enums[Translation],
(FIND(“Field1”, [Enum]) > 0),
FALSE
)

View solution in original post

Steve

pawann:

ANY(SELECT(
	SWITCH([Contact_Role],
		"Plant Manager", Translations[PlantManager],
		"Maintenance", Translations[MaintenanceManager],
		"Supervisor Plant Manager", Translations[SupervisorPlantManager],
		Translations[No_Contact_Role]
	),
	([Language] = LOOKUP(USEREMAIL(),Users,"Email","Language"))
))

But this results in an error saying “ Unable to find column ‘Language’ ”.

The first argument of a SELECT() expression must be a table-column reference or a list dereference; it cannot be an expression. I suspect the odd error you’re getting is an artifact of the parser, rather than an actual problem with the Language column.

Note, too, the the result of your SWITCH() expression would be a list of values. For instance, in this snippet:

Translations[PlantManager] is a table-column reference that produces a list of all values of the PlantManager column of the Translations table. Although SELECT() does want a table-column reference, it must be given the reference directly, rather than one obscured within an expression like this.

In this case, Translations[Language] is another table-column references, an expression itself that produces a list of that column’s values. So you’re comparing a list of values from that table-column reference to a singular value produced by the ANY() expression. You can’t compare a list to a singular value, hence the error.

Still digesting the rest of your problem. More to come…

See also:

Steve

It may be too late for you to reengineer your solution, but my suggestion would be to make your Enum and EnumList values of base type Ref to a table where those values are key column values, and the label column is a virtual column that contains the user’s preferred translation. The stored values for Enum and EnumList columns would then always be the same regardless of the user’s chosen language–making them easier to use in expressions–but would be displayed to the user’s preference automatically.

Actual columns:

Term	DE	FR	IT	EN
yes	Ja	Oui	Sì	Yes

Plus a virtual column, perhaps named Translation, with an App formula expression of:

SWITCH(
  LOOKUP(
    USEREMAIL(),
    "Users",
    "Email",
    "Language"
  ),
  "DE", [DE],
  "FR", [FR],
  "IT", [IT],
  "EN", [EN],
  [EN]
)

Term would be the key column; Translation would be the label column.

pawann

Hey Steve!

Wow, what a response time - thank you very much for your fast responses!

Your suggestion sounds really interesting. I will try to figure out how to adopt my data model and will come back with the results asap.

pawann

Hi Steve, I went for your suggestion with the virtual column holding the calculated translated value based on the user’s language. It works very well. Thank you very much!!

When using a prefix in the term to identify the field, it is even easy to have only one ENUM translation table that holds the translations of all ENUMs for all the different fields.

Example:
‘Term’ column enum values for [Field1] are:
Field1_Enum1 , Field1_Enum2 , Field1_Enum3

Using this expression to suggest all translated ENUMS that include the string “Field1”:
SELECT
(Enums[Translation],
(FIND(“Field1”, [Enum]) > 0),
FALSE
)

How to populate a virtual column with values in the user's selected language that is based on an EnumList