|
|
|
|
Multilingual Solutions
|
Level: Intermediate
Version: FileMaker 19
Category: Calculations
Thursday, March 18, 2021 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
I've talked to other developers, read articles and even attended presentations on how to create multilingual FileMaker solutions. I've always thought to myself... "I'll never have to do this myself" so I only half listened. Now I have a client who needs multilingual field labels, titles, button text, tooltips and placeholders. I'm going to walk readers through the process of determining the best solution for my particular client so you can learn the way I did. You may have to adjust your approach to your client needs but this should get you started.
Example File
The example file included with this article (which can be downloaded below) covers two basic approaches to multilingual solutions. The first is how to change the text in field labels, titles, button bars, text blocks and popover bars. The example file only demonstrates how to change field labels but the same concept can be applied to any of the types listed above. Essentially, anywhere there is static or calculated text, that needs to contain language centric text, this technique works easily and efficiently.
The second technique is adaptive or dynamic, automatically adjusting to the current object using a single formula. In the example file, I demonstrate the technique using a tooltip on a field. Since the field contains information that can be retrieved via functions, it can be used to determine, on the fly, the text that needs to be inserted into the tooltip. I will discuss the ups and downs of adaptive versus static scripts as the article continues.
My Solution
Every FileMaker solution has different needs so I want to explain my perspective so you understand my unique multilingual needs. My client has staff who speak English and Japanese. In addition, the Japanese employees want to see the text in Kanji and Kana, depending on their disposition. That means I need to switch the field labels, tooltips and placeholder text using a preferences table to track each user and their settings.
There's a lot more than that going on in the solution in regards to multiple languages. For example, the solution also needs to output in all three languages. It's a complex solution but what I wanted to do is highlight the techniques I developed for switching languages on the fly for the user interface. Others before me may have come up with similar techniques but I couldn't find anything current when I searched the internet. Please post links to valid FileMaker 19 solutions in the comments below so we can pool all the resources in one place.
Labels
Since labels display on almost every layout, this part of the process had to be efficient. I didn't want live calculation formulas looking to a table of translations. This would require an unstored calculation for every field label and would recalculate each time the record was changed (as well as clutter up Manage Database). In other words, I don't even have to test this "live" calculated approach as I already know it will be super slow when multiplied across all the labels on a single layout.
FYI: Unstored calculations can really tax a FileMaker solution when not kept under control. For more information, read the article Designing for Efficiency.
Therefore, I turned to global fields as the best solution. I opted to use a global repeating field and a script that runs on open or whenever the user changes their language in preferences. To keep things simple in the example FileMaker file, I simply change the field labels when the language is changed using a script trigger. This is going to be one of those articles where you'll need to adapt the technique to your own specific needs so focus on the essence of this article and glean just what you need.
I selected a global field to display the field labels because I didn't want to store the data permanently. Imagine using a regular field and trying to store the language on every single record. Imagine what you'd have to do if the language is changed. You'd have to loop or replace all the record for every field label (a slow process to say the least). This doesn't even cover the blockade with record locking issues in a multi-user scenario.
The only issue with global fields is the content is lost when the solution is closed (in a multi-user situation). That's fine since we're going to make this solution as speedy as possible. I've chosen a hybrid approach of adaptability and hard coding. Despite what you might read on the internet, static programming isn't always the wrong way to approach a solution. Just because someone used the fancy word "indirection" when hocking their FileMaker technique doesn't mean it should be applied in every, or even any, scenario.
It's all about whether or not the complexity of dynamic program pays dividends. For example, let's say you have to set the contents of ten fields with data. Indirection would suggest a single Set Field by Name script step with dynamic code to change the target field each time the script loops. But, if there will never be additional fields added to the process, what's the point in wasting your time with adaptive code. Best just to hard code the values to be placed in the fields. I'd go as far as to say most solutions don't need indirection in favor of simplicity, efficiency and time savings.
Not only am I using a global field but a global repeating field. No sense in cluttering up Manage Database with a bunch of interface fields. In my solution, I have almost one thousand field labels set to the repetitions in a single global field (the maximum repetitions for a single field is 32,000). That would be a lot of fields! All you have to do is specify the repetition for the field label you want so it's not much harder than setting a regular field:
<<CONTACTS::xlabel[172]>>
You can use merge fields, regular fields or even button bars to display the correct repetition. It all depends on the other considerations of your unique solution. However, I'll provide you with a few insights to help you determine the best approach. First, notice the fully qualified name containing the field name and the table in the merge field example shown above. While a global field can display in any table without a relationship, the table name still has to display. Switch to a regular field and the table name is hidden in the definition of the field in the Specify Field dialog. It's a little bit more work to specify the repetition via the Inspector but the advantage of smaller object dimensions can be a great asset on a complicated layout.
If you have some reason why merge fields work better than regular fields (and, there are many such as automatic sliding in browse or preview), use a technique to save space by reducing the font size between the chevrons. If you leave the outer characters the font size you want, FileMaker won't care how small the text is between:
<<CONTACTS::xlabel[172]>>
The Table of Languages
Now that we've vetted the basic approach, the first thing you need to create is a table for all the translations. I'm calling my table MULTILINGUAL and there will be one record per word or term with one field for every language. And, the last field is for the name of the record so it can be easily searched.
I highly recommend specifying the "name" field as unique in the validation area of Manage Database so you don't accidentally name two records the same.
ExecuteSQL
I played around with using FileMaker Find Mode to search for all the labels. My thinking was searching via the FileMaker Find Mode is always faster than ExecuteSQL. ExecuteSQL was introduced to declutter Manage Database so developers could search a table without creating a relationship. I use it quite willingly with preference solutions as seen in an article I wrote called Preferences and Options.
While ExecuteSQL is known for being slow, it really wasn't slow in this situation. In my testing, I found entering find mode versus ExecuteSQL was a wash in terms of performance. Therefore, I chose the easiest to implement solution. You might choose differently but I found entering additional translations as I progressed in the development cycle was far easier with ExecuteSQL. Here are the two scripts I used for speed testing:
Find Mode script:
ExecuteSQL script:
The blue arrows represent the steps that were added to record the elapsed time and are not needed for the script to perform properly. The reason I chose ExecuteSQL is easy to see when you consider readability. With the find mode script, I have to look at a very verbose script and try to remember what to modify. It's just more straightforward with ExecuteSQL, with a single line for each label. All I have to do is add a new line and change the repetition or label name. And since there is no speed difference, I like the ExecuteSQL solution better.
FYI: Testing a FileMaker solution for speed in a single-user scenario is almost pointless. Always test for speed over the slowest network connection and with the weakest device that you know will be used. For general testing, look at these two free and open source tools: dsBenchmark and Punisher.
Custom Functions
ExecuteSQL is definitely more complex, as you will see in this section. I've hidden most of the complex work in some custom functions but I'd say, the real work comes as you are adding new translations to the MULTILINGUAL table. A little more work upfront to create the custom functions is not really where I spent my time on this solution. Most of it was spent adding to the script whenever a new translation was needed.
If you are unfamiliar with ExecuteSQL, I'd recommend starting with an article I mentioned above called Preferences and Options. It will get you comfortanle with the basics of ExecuteSQL and also teach you one of the best uses for it. Even so, ExecuteSQL is more complex to learn and troubleshoot. Just one missing semi-colon or letter and the whole things goes to crap. There's really no syntax debugger like the calculation engine so you are mostly on your own to figure out the issue. Think of it in the same vein as HTML coding without a WYSIWYG editor.
Regardless, once the simple ExecuteSQL code is complete, you never have to look at it again. I highly recommend becomming familiar with ExecuteSQL to declutter Manage Database when applicable. Here's the ExecuteSQL code as it appears in the example file.
Labels(Name; Language) =
ExecuteSQL(
"select " &
Language
& " from MULTILINGUAL where name = ?";
"";
"";
Name
)
As you can see, it's a simple search for the contents of the "name" field. Once it finds the record, the contents of the global language field determines which field from the MULTILINGUAL table will be grabbed. In the example provided, I use a direct match between the language specified in the "xlanguage" field and the fields in the MULTILINGUAL table. You can get more complex but there's no need to muddy the waters with a conditional statement.
Tooltips
Tooltips don't display or even calculate until an object is hovered over. Therefore, there's no need to preset the values (in an effort to speed up the opening of a solution). Just let them calculate one at a time as needed. There will be no speed issues since the calculation process occurs one formula at a time.
Tooltips(Name; Language) =
ExecuteSQL(
"select " &
Language
& " from MULTILINGUAL where name = ?";
"";
"";
Let(
@Name = GetFieldName(Name);
Right(@Name; Length(@Name) - Position(@Name; "::"; 1; 1) - 1)
)
)
This custom function is also adaptive to the field where it is attached. This works because of the Self function when the custom function is called:
Tooltips(Self; CONTACTS::xlanguage)
Since the GetFieldName function provides a fully qualified field name and the ExecuteSQL function only needs the field name, the table and colons are removed with text parsing. If you don't need or can't implement adaptive tooltips (e.g. tooltips for a text block or button), try a simpler approach:
Tooltips2(Name; Language) =
ExecuteSQL(
"select " &
Language
& " from MULTILINGUAL where name = ?";
"";
"";
Name
)
Just so it's absolutely clear, the "name" field in the MULTILINGUAL table has to match the exact name of the field to which the tooltip is attached.
A Simpler Approach?
I lived with this solution for a couple months while I programmed other areas. But, when we got ready to start testing on a server, the opening script took minutes to perform. This is unacceptable so I reexamined the multilingual script, did some testing and came up with a different approach. The idea was to loop through all the records in the MULTILINGUAL table and set each one to a global field repetition.
The script starts by freezing the window so it's as fast as possible. For even more speed, switch to a layout that has no fields on it. Then, all records are shown and the first record is selected. The script loops through the records and uses the following formula in the Set Field step to grab the correct language:
Case(
CONTACTS::xlanguage = "English";
MULTILINGUAL::english;
CONTACTS::xlanguage = "German";
MULTILINGUAL::german;
CONTACTS::xlanguage = "Spanish";
MULTILINGUAL::spanish;
CONTACTS::xlanguage = "Japanese";
MULTILINGUAL::japanese
)
I was thinking I would get double the speed but test runs on my server show over thirty times the speed. The ExecuteSQL script took over 60 seconds to perform and the loop less than two seconds. Wow! Was I astonished! I expected a speed increase but not that much. Just goes to show, trying multiple approaches can pay off. So, I thought to myself, let's try a Replace Field Contents instead. It's generally twice as fast as a looping script with Set Field. Unfortunately, it was approximately the same speed. Good try though.
Setting by Order
One last thing I did was improve the flexibility by including an "order" number field in the MULTILINGUAL table. Instead of using Get(RecordNumber) as the repetition number, I used the order field instead. This allowed me to have the records out of order which could offer a gigantic benefit in terms of flexibility.
As I played around with the solution a bit more, I realized there was another benefit to the order field. The advantage was I didn't ever have to modify the script again! I just had to make sure the order contained a unique value. And, when I'm trying to apply an existing translation to a new layout, I don't have to hunt through the entire script for the corresponding label. All I have to do is search the MULTILINGUAL table and then use the value in the order field to setup the repeating field with the correct repetition number.
ExecuteSQL
The ExecuteSQL technique is still good for tooltips. No sense in trying to set every tooltip on open, despite the speed of the final solution. Tooltips calculate as needed and since it's also possible to show one at a time, it's a quick evaluation of the formula to find it in the MULTILINGUAL table. In other words, it's about using the right tool for the job.
In addition, I'm not totally sold on the dynamic version of the formula. There are lots of tooltips attached to objects that can't be dynamically calculated such as text blocks, buttons, portals, etc. Might as well just use a single straightforward solution for all tooltips.
Feedback
I welcome any feedback on this solution, especially if you can increase the ease-of-use or the speed. Leave comments or links to other articles below so we can make this a hub for multilingual techniques.
Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com
Example File (available to patrons at $5.00 per month): Download here if you are a patron
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
|
|
Comments:
|
|
|
L. Allen Poole
|
03/18/2021
|
|
|
Thanks for this great update on a longstanding issue!
One thing I was hoping you'd address at the same time is managing how business logic interacts with field data that's entered in multiple languages. Suppose someone might specify their role as student, or employee, or other. If users are entering data both in French and English, those three data points could take 8 forms (or 6 if user's aren't asked to overlook the changes associated with gender in French). Now suppose some business logic in the system is governed by these role names... either the data has to be stored in some language-independent way (the user thinks they're entering "étudiante" but they're really entering "1") or the business logic needs to know that "student," "étudiant," and "étudiante" all mean the same thing. Reporting can add another dimension to this problem, if reports need to translate field data into a single target language.
|
|
|
|
