All the labels in my interface can be displayed either in English or French, thanks to a custom function which adds the user setting choice English (0) and French (1) to the repetition nomber of a label string stored as one of a global's repetition. So LabelCustomFunction (3) will be an English word if user setting is 0 and its French version if user setting is 1. That works well.
However, when it comes to making dropdown lists display in one or the other language, things get a bit more complicated.
My strategy (above) relies on the use of a global; FM Value lists cannot be based on a global field or a calculation which is not indexed.
The Inspector does not provide a way to conditionaly use a list instead of another
Naming a Value List $$Category, where the variable gets updated with the proper language list does not work since FM considers that name as "$$Category", a text string, not a variable.
So below are 4 strategies I thought of, but I am wondering if I am over complicating something that could be simpler.
Strategy 1- When language setting changes, a trigger sets a field "unilingualCategory" with the value currently in "cCategory" (which recalculates when the language is switched to load either the English or French list). The Value List "Category" is sourced from the field unilingualCategory. So the actual data field, Category, is a dropdown field which values are from the list Category (one list per record). When exiting the record, the field unilingualCategory is set to "". That last step is essential since across all records you would end up with instances of each language which would defeat the purpose. Pros: it works. Cons: doesn't seem very robust. clutter as each value list requires such setup. requires some relationships
Stragegy 2 - In the list values table, enter each value as a separate record with the following fields: list it pertains to, language, value(string). Create relationships with the client table filtered by language and listname and client id. Unclear as how to connect that to product table. Pros? Cons couldn't get it to work; it returns all values regardless of language and listname.
Strategy 3 - Create 2 CatLists,E andF, first field iD and display only field 2, with corresponding values in either language placed at the same ID number. Put 4 fields on top of each other: Field CategoryE, CategoryF, CatDDWNe, CatDDWNf. Source the CatDDWNe to List CatE and CatDDWf to List CatF. Use Hide when to hide the E fields if client language is F and vice versa. Use a trigger to set both CategoryE and CategoryF (in English and French) whenever a value is picked in either dropdown so that switching client language will show the value in the current language.
Strategy 4 - Has to do with ExecuteSQL but dunno how to implement it. The table ValueLists can be accomodated (either one list per record or one list element with its listname and language per record) to the specific need.
I suspect this is the most promising since it does not require to create tons of relationships and fields and what not. But I don't know how to do it.