How to Internationalize and localize your scripts

Brian Element

Mon, 08/03/2015 - 10:03

Wikipedia defines internationalization and localization as:

Internationalization and localization are means of adapting computer software to different languages, regional differences and technical requirements of a target market. Internationalization is the process of designing a software application so that it can potentially be adapted to various languages and regions without engineering changes. Localization is the process of adapting internationalized software for a specific region or language by adding locale-specific components and translating text.

So you may be wondering what this has to do with IDEAScript, and once upon a time I would probably agree with you.

I first found out this was important when I was giving one of my first IDEAScripting courses. I had been giving IDEA training for many years but I had just finally jumped on the band wagon and was learning how to use IDEAScript. Now the reason I was asked to give the training was that it was in Montreal and the training was in French, the regular trainer at the time that gave the IDEAScripting course didn’t have enough French to give the course so I was asked if I would give it a shot, since I wanted to add this to my accomplishments I agreed right away to try it out.

Now here I am giving the training, we are doing some basic extractions, probably where using some code like the following:

Set db = Client.OpenDatabase("test.IMD")
	Set task = db.Extraction
		task.IncludeAllFields
		dbName = "asd.IMD"
		task.AddExtraction dbName, "", "PERCENTAGE_SUM > 44.5"
		task.CreateVirtualDatabase = False
		task.PerformTask 1, db.Count
	Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)

The code runs fine on my computer but when some in the class tried it they received the following error: “Error on line 14: Bad equation provided”

So here I am giving my first IDEAScripting class and I come up with that error, what a head scratcher. Well to make a long story short I finally realized that as I was in Montreal and I was giving this training in French that some of the studentès computers had their regional settings set for French Canada. In the French language the decimal place that is used is a comma (,) and not a period (.). So once those students with the problem changed the equation from "PERCENTAGE_SUM > 44.5" to "PERCENTAGE_SUM > 44,5" everything worked fine and that is when I realized the importance of making sure that your script can run in other regional settings.

Some of the things you need to watch out for that could change in an equation are:

The decimal (period versus comma)
List separators (comma versus semi colon)
Date format, every region usually has one or more date formats

So the first thing you need to do is get this information so that you can use it in the script. Fortunately the windows kernal keeps track of all this information and you can access it through IDEAScript. You would then use a variable to hold the decimal place or the list separator. You can find a list of all the items that the windows tracks here.

The first items that we will look at is the decimal, separator, date and time separator, so we are interested in the following items:

Public Const LOCALE_SLIST = &HC             '  list item separator
Public Const LOCALE_SDECIMAL = &HE          '  decimal separator
Public Const LOCALE_SDATE = &H1D            '  date separator
Public Const LOCALE_STIME = &H1E            '  time separator

So what we want to do is create a variable to hold each of these items so that we can now use them in our script. The following is a short script that will populate the variables with items from the windows kernal.


Public Const LOCALE_SLIST = &HC             '  list item separator
Public Const LOCALE_SDECIMAL = &HE          '  decimal separator
Public Const LOCALE_SDATE = &H1D            '  date separator
Public Const LOCALE_STIME = &H1E            '  time separator

Public Const LOCALE_SYSTEM_DEFAULT& = &H800
Public Const LOCALE_USER_DEFAULT& = &H400

Dim sListSeparator As String
Dim sDecimal As String
Dim sDateSeparator As String
Dim sTimeSeparator As String

Declare Function GetLocaleInfo Lib "kernel32" Alias "GetLocaleInfoA" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
Sub Main
                Call populateLocalVariables()
                
                MsgBox "List Separator: " & sListSeparator
                MsgBox "Decimal: " & sDecimal
                MsgBox "Date Separator: " & sDateSeparator
                MsgBox "Time Separator: " & sTimeSeparator
End Sub

Function populateLocalVariables()
                sListSeparator = ReadLocaleInfo(LOCALE_SLIST )
                sDecimal = ReadLocaleInfo(LOCALE_SDECIMAL )
                sDateSeparator = ReadLocaleInfo(LOCALE_SDATE )
                sTimeSeparator = ReadLocaleInfo(LOCALE_STIME )
End Function

'*****************************************************************************************************************
'* VB function found at http://www.xtremevbtalk.com/showthread.php?t=162703
'* will return the regional setting based on the constant sent to it.
'*****************************************************************************************************************

Public Function ReadLocaleInfo(ByVal lInfo As Long) As String
    
                    Dim sBuffer As String
                    Dim rv As Long
                    
                    sBuffer = String$(256, 0)
                    rv = GetLocaleInfo(LOCALE_USER_DEFAULT, lInfo, sBuffer, Len(sBuffer))
                    
                    If rv > 0 Then
                        ReadLocaleInfo = Left$(sBuffer, rv - 1)
                    Else
                        ReadLocaleInfo = ""
                    End If
End Function

So now we know how to get the variables let's talk about how we can use them in the script.

List Separator

The list separator would be used to separate items in an @ function within the equation editor. An example would be the @left() function, this function needs two items, the field or characters are the first part and the second part is the number of characters to extract from the left. So if you are using this function in an English language you might have something like this @left(MY_FIELD, 2) but if you change your regional settings to German this equation would give an error as it is expecting a ";" instead of a ",", so this equation in German regional settings would be @left(MY_FIELD; 2) would be correct.


Set db = Client.OpenDatabase("MyDatabase.IMD")
	Set task = db.TableManagement
		Set field = db.TableDef.NewField
			field.Name = "TEST"
			field.Description = ""
			field.Type = WI_VIRT_CHAR
			field.Equation = "@Left(CHAR_FIELD, 2)"
			field.Length = 2
			task.AppendField field
			task.PerformTask
		Set field = Nothing
	Set task = Nothing
Set db = Nothing

So the following code would run fine in a region where the "," is the list separator but in another region that uses the ";" you would have an error.

So what needs to be done is to replace the list separator with a variable that holds the separator. In the previous code we defined the sListSeparator variable to hold this code. So now we just swap out the "," in the example with the variable and the code will now work in different regions. So your final code would be something like this:


Set db = Client.OpenDatabase("MyDatabase.IMD")
	Set task = db.TableManagement
		Set field = db.TableDef.NewField
			field.Name = "TEST"
			field.Description = ""
			field.Type = WI_VIRT_CHAR
			field.Equation = "@Left(CHAR_FIELD" & sListSeparator & " 2)"
			field.Length = 2
			task.AppendField field
			task.PerformTask
		Set field = Nothing
	Set task = Nothing
Set db = Nothing

Handling commas in numbers

This is a bit trickier in how you handle it in IDEAScript. Now we have to split up where the number is coming from. If it is from a user then I would hope the user knows where to use a "." or a "," as a decimal, so I am going to make the assumption that the user will use the right character as a decimal for user input (but this leaves us with another problem that we will come back to in a minute).

So in IDEAScript it tracks decimals internally as a "." no matter what the regional setting (now I have tested this out on quite a few regions and I believe it is true but there might be exceptions).


Sub main
	Dim myNumber As Double
	myNumber = 1 / 2
	MsgBox myNumber
End Sub

So the above will always return 0.5 no matter what the regional setting. Now the problem is what happens if you need to use this myNumber variable as part of a criteria, depending on the region it might give you an error if you are working in a region that uses the "," for a decimal place. Fortunately visual basic has a function to get around this which is the format function, it is a very powerful function and I won't bother to go in depth with how to use it as there are many pages on the web that explains how to use it, here is the ms page to start you off.

So suppose you are creating a virtual field with the following equation:

field.Equation = "MY_FIELD * " & myNumber

In areas that use the "." as a decimal you won't have a problem with IDEA but if the region uses a "," you will get the following error "A valid equation is required for this field", not what you want in the script.

So you need to format the number first before inserting it into the equation, here is an example:

Dim myNumer as string
myNumber = Format(.5, "#0.00")
field.Equation = "MY_FIELD * " & myNumber

Now to explain this a bit. I defined the variable as a string as the format() function returns a string, this is not a problem as the equation is a string. Also I used "#0.00" as the formatting, so I am telling the format() function that I want two decimals and it will take the decimal place that is part of the regional settings. So if your script will be used in different region you should format() any IDEAScript generated numbers that will be inserted into an equation or criteria.

Now you remember there is another problem when dealing with user input. One way to verify that a user entered a number is to use the IsNumeric() function, the problem with this is that the IsNumeric() only will accept ".", if you enter in a "," you will get an error. Here is an example code, if you use a "," as the decimal it will say that this is not a number.


Sub main
	Dim myChar As String
	myChar = InputBox("Input a number with a decimal")
	If IsNumeric(myChar) Then
		MsgBox "This is a number"
	Else
		MsgBox "This is not a number"
	End If
End Sub

One way to get around this is to replace decimal from the user input prior to the number being verified. So above we found out how to find out what the decimal is and we placed it in the sDecimal variable. What we can do now is a replace the decimal with a "." before using the isNumeric() function.


Sub main
	Dim myNumber As String
	Dim sDecimal As String
	
	sDecimal = ","
	
	myNumber  = InputBox("Input a number with a decimal")
	myNumber = iReplace(myNumber, sDecimal, ".")
	If IsNumeric(myNumber) Then
		MsgBox "This is a number"
	Else
		MsgBox "This is not a number"
	End If
End Sub

So what I did is use the IDEA function replace which I accessed through the iReplace function, so I am taking the number entered by the user and then removing the regional version of the decimal with the "." and then checking to see if the input is a number.

I am still learning tricks on how to make my scripts run on different regions so I would be interested in hearing other people's experiences.

How to Internationalize and localize your scripts

Share This Page