Language Localization in FileMaker Solutions

There are plenty of reasons to build in support for multiple languages in your FileMaker solutions. For example, one day I noticed one of our clients had a sign posted in a common area that contained instructions in no less than four different languages. That was in Chicago, in the heart of the Midwest. Never mind globalization and expanding markets, which are other reasons you may want to add multi-language support to your solution, there is need for this functionality where you might not expect it.

Of course, the term “Localization” encompasses more than just translating field labels for your user interface. Other considerations may include translating field content, converting date formats, currency formats and even current exchange rates, among other issues.

For the scope of this article, we will focus on translating our user interface, or UI, for the sake of simplicity. This may be all you need, but should get you started in any case.

There are many different techniques that have been around for a while now. The technique discussed here attempts to show how you can leverage more recent features and development techniques with the goal of a narrow, lightweight, easy to manage solution.

There are a couple concepts to review before we proceed.

1. Namespaces for your Variables

By now, most FileMaker developers should be familiar with the concept of setting a variable. One pitfall with variables is that when you set a variable, you will overwrite it’s previous content, if it had any.

This can happen inadvertently if you are setting a lot of variables. To avoid conflict, or collisions, we can assign a namespace for the different categories of variables that we will set.

For our UI variables, which are all global variables, we will start them with a prefix of “$$UI.” and then the name of the variable. So instead of a global variable named:

$$FirstName

we want to set

$$UI.FirstName

This way, if we want to set a variable “FirstName” but from somewhere else like in a script to handle record contents, we can assign it a namespace ($$Data.FirstName) to avoid colliding with our UI variable.

Instead of using a fixed text label like “First Name” for a field, we can use a Merge Variable by entering the text “<<$$UI.FirstName>>“ that will be populated with the contents of that variable.

This becomes increasingly important as we consider our next concept…

2. Setting Dynamically Named Variables

It is possible to set a variable with a name dynamically using a Let statement. What that means is that anywhere you have access to FileMaker’s calculation engine, you can set a variable. By using the Evaluate function, you can dynamically set both the value of a variable, as well as the name itself.

If we have a variable named $this.pair with the text string $$UI.lang = “Language” then we can dynamically set this with a Let statement that gets Evaluated like this:

Evaluate ("Let ([" & $this.pair & "]; "" )")

Down the Rabbit Hole in FileMaker Solutions

Now, using these concepts, let us see how far down this Rabbit Hole goes…

If the aim is have a narrow table, with the fewest amount of fields and overhead as possible, you can get away with a single table with only three fields. Each language would have key/value pairs for the name of the variable, and the content of that variable. So that is three fields:

code, key, value

Once we populate the table with a little content, we get something similar to this:

If we find all records where “code” is equal to “en”, then we can loop through and name a global variable in the UI namespace for each record found.

This also has the advantage of being maintainable without having to add new fields or otherwise modify the database schema to support adding new labels or entire languages.

Since global variables persist throughout the duration of the session, or however long you have a file open, setting the variables is only be necessary when you first open the file, or when you change your language preference.

That’s the gist.

Default and Selected Languages

In our sample, we have added a few features to make this more user-friendly. This includes a lightweight user interface for managing the different languages. We have added in a parent table for the language where we can also store the preference of what language is currently selected.

Side note: Of course this may depend on your FileMaker solution. If you need to load a localization based on a user’s profile, you may want to set the selected language from their stored preferences instead. The sample shown is meant to provide a useable example that you can further customize.

In addition to the selected language, we can also designate a language set as the “default” just in case we have some UI elements that do not need a translation. For this, we simply make an initial pass to set the variables needed from the “default” set, then a second pass to cover the selected language, which will overwrite the default.

Setting the variables

Now we get down to the fun part, setting the variables. We can avoid layout dependency and set all our UI variables from any context by using a ExecuteSQL function call to “SELECT” from our table.

Once we have the results of our ExecuteSQL function, we can substitute in a couple places to get it in the right format to get it Evaluated as a Let statement and set ALL our UI variables in one pass.

What does that look like? A single pass would look something like this:

Let ([

  this.namespace = "UI";
  this.lang = "en";

  this.sql = "SELECT key,value FROM Translate WHERE code = ? " ;
  this.values = ExecuteSQL ( this.sql ; "||" ; "¶" ; this.lang );
  this.values = Substitute ( this.values ; [""";"\""] );
  this.pairs = "$" & this.namespace & "." & Substitute ( this.values ; ["¶";"";¶$" & this.namespace & "."] ; ["||";" = ""] ) & """;

  $load_values = Evaluate ("Let ([" & this.pairs & "]; "" )");

  this.end = ""
] ;

""

)

You will note that I have also used a namespace for the variable names in the let statement. This also avoids collisions with field names, since they do not require dollar signs in the Let scope.

This is a simplified version of what the sample contains, since this is a single pass with the language value hardcoded as “en” to find matching records.

Found records are returned in a list, with a record delimiter of double pipe characters so we can easily substitute those out. By building a Let statement as text, when we use the Evaluate function, all our variables are set.

Note: there is a limit to how many variables can be set with a “Let” statement in FileMaker of 1,000. If you need to set more than that, you may want to handle that by looping through and set as many as you need.

We are not done yet. We can set these variable a couple ways in FileMaker solutions.

1. Call a script that has “set variable” script step,
2. Create a custom function,
3. Use a layout object attributes, like visibility, to set these variables,
4. Some combination of the above.

In our sample file, we have a custom function that sets all UI variables. There is a script that runs the file is first opened that calls the custom function. This can also be run at any time to refresh the variables, in case a new language is selected.

You could also modify this to run based on a user’s preferences record if you have such functionality in your solution. It would be fairly simple to set a field using a value list of available languages that allows a user to set their language preference.

Limits of the Let Statement

Something to note using this technique, is that there is a limit of 1,000 variables that you can set at one time. There is a similar limit in custom functions with recursion, to avoid infinite loops.

In those cases where your user interface may approach this limit, we can simply find all the records in our translate table and loop through them. This takes a little longer than setting them with a Let statement, but even with several thousand records in my testing, the difference was negligible. By counting the values in our translate table, we can either set them with a Let statement or freeze the window and loop through them quickly.

Just for fun…

Before we go any further, let me say that you should be wary of going too crazy with setting variables that run ExecuteSQL. You should consider best practices when developing and think twice before including business logic in layout or UI elements. If nothing else, it could become very difficult to maintain.

So what we are about to show is not considered best practice.

That being said, let’s have some fun. We will create a custom function that does our big let statement. The one included in the sample file is called “_LoadUI” so it sorts to the top of our function list. This function makes two passes for both the defaults and our selected language.

Again, just to show you it is possible, we can call this custom function from a layout object. In this case, our object even lives off to the right of our layout so the end user never has to see it. Select the object and bring up the Data tab on the Inspector. Under Behavior, there is a calculation for “Hide object when” that gives us access to the calculation engine…and we are off…

The only thing you need to enter there is “_LoadUI” which calls our custom function.

Now, *any* time you go to this layout or refresh the window, this will evaluate and set all our UI variables.

Again, it would probably be better to perform this within the confines of a script, to organize your code, and for better maintainability. This is merely provided for informational purposes.

Download the File

We hope you enjoy this sample and if you have comments, leave them below.

References

1. FileMaker – Using Variables:
   http://www.filemaker.com/help/14/fmp/en/html/create_db.8.42.html
2. Google Translate:
   https://translate.google.com/