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:
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.
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.