Hi, I'm new to the AppSheet, trying to develo...

expressions
(Luís Pinto) #1

Hi, I’m new to the AppSheet, trying to develop a survey app for a research project. I have been struggling with some issues, most of then related to multi-language support. Here are the main steps, regarding this issue:

  1. Languages activated in User Settings, with 5 language options, based on valid_if from table.

  2. Tables with data in each of those five languages, each column a different language, with the column name exactly the same as the language names in user settings.

  3. Reading some answers in this community, I realized that I cannot use UserSettings() to substitute field names in expressions such as LOOKUP or ANY,

so trying a different approach, with a Virtual field [country-language], calculated with valid_if formula:

ifs(usersettings(Language)=“Deutsche”, Deutsche, usersettings(Language)=“English”, English,

usersettings(Language)=“Español”, Español,

usersettings(Language)=“Français”,Français, usersettings(Language)=“Lietuviškai”, Lietuviškai,

usersettings(Language)=“Português”, Português)

But then I get a problem with the validation of the formulas that I use to: (a) select the Enum column for the dropdown lists:

=SELECT(valid_if_Countries[country-language], true)

(b) select the column for the Display name based on the chosen language:

=ANY(SELECT(valid_if_fieldNameTranslations[country-language], [field-name] = “gps-coordinates”))

In both cases, I get the following message:

Unable to find column ‘country-language’, did you mean ‘country-language’?

What am I doing wrong? Can it be a problem with column type? the virtual field is of Enum type, but also tried as Text, error keeps the same. Any help much appreciated, thanks!

(Aleksi Alkio) #2

Why are you using multi tables and colums where you could create the structure only with one table and one column for each question? Is there any reason for that?

(Luís Pinto) #3

Hi @Aleksi_Alkio, thank you for your feed back.

Sorry, I didn’t mentioned that there is in fact one single, main table, used to record all survey entries, with ±30 different fields, so one column for each question, as you mention.

But as most of the questions are closed questions, to be answered by selecting an option from dropdown menus, I created another table to store the options to use in those dropdown menus. One column for each field, containing the different options for each field. but this is only in one language.

And then I also had a list of countries, as well as occupations. But I had those list already translated on the 5 language. I realized that, if I wanted to have different languages, and to give the possibility to also choose the options in those languages, I would need one column for each language, but mixing that in one single table, with 5 columns for each field, would then be complicated to try to implement the language choices.

So I though about creating different tables for the lists of Countries and Occupation, where the names of each column are the name of the Language (Column 1 name is ‘Deutsche’, Column 2 name is ‘English’ and so on). These language names are exactly as the Language options available at the User Settings.

The idea was that, organized like that, I could simplify the use of languages by checking the Language choice in User Settings, and then automating things like the Drop down menus in the corresponding language with something like:

=SELECT(valid_if_Countries[], true)

I also have another table with the names/questions to be used in the Form, for each field, also with one language by column, with the same language names. Like that I could use something like the following expression to get the Field display:

=ANY(SELECT(valid_if_fieldNameTranslations[], [field-name] = “gps-coordinates”))

But I have been failing in crossing this with the Language option in User settings.

Furthermore, I also have the problem of the other options for the rest of the fields in the main table, as they are currently only in english. If I do the same for each field, I will end up with 30 different tables, one per each field. I realized that, instead of one table per field with all languages in it, I could also have one table with all field lists for each language, which means I would end up only with 5 tables (besides Countries and occupations, which are already ‘organized’).

Personally, the advantage I see on having one table per field with all languages in it is that it is easier to update, and check discrepancies. Especially with large lists, such as world countries.

Getting one table per language is perhaps easier in terms of table management (more effective in terms of DB processes also?).

I probably need to optimize the structure of the app. But besides this, independently of the approach, I have been failing to use the language information stored in User Settings to effectively use .

Sorry for the long post!