Using Regional settings in your script
Forums
I saw a posting at the IDEA web site in which the person was having problems because sometimes the equations used would take a "," and other times it would take a ";". The reason for this is because of the regional settings. In Canada we deal with both English and French languages, in English the list separator is the "," and in French it is the ";". So if you are creating an equation in the equation editor you would use the "," or ";" based on the language. So an @left function would look like @left(FIELD, 2) in English and @left(FIELD; 2) in French. This is well and good if you are working in that language and you are entering the function into the equation editor directly but what if you have an equation in a script? If you post the script for general use it might be used by persons with different regional settings than you and they would have errors because of this difference. Unfortunately IDEA doesn't seem to have a function (or none that I have found) to get around this, fortunately Excel does and as Excel uses the same language as IDEA you can tap into this. So the example script below will create an excel object and then go get the International setting for the list separator (you can find a list of all the international option here), what you would do is create a variable to hold the separator and use that instead of the "," or the ";", now your script should work in any setting. Love to hear any comments on this or anyone else that has had experience doing this.
Const xlListSeparator = 5
Dim sListSeparator As String
Sub Main
Dim excel As Object
Set excel = CreateObject("Excel.Application")
MsgBox excel.International(xlListSeparator)
sListSeparator = excel.International(xlListSeparator)
Set db = Client.OpenDatabase("Your File.IMD")
Set task = db.TableManagement
Set table = db.TableDef
Set field = table.NewField
eqn = "@left(NAME" & sListSeparator & " 2)"
field.Name = "TEST"
field.Description = ""
field.Type = WI_VIRT_CHAR
field.Equation = eqn
field.Length = 4
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set table = Nothing
Set field = Nothing
Set excel = Nothing
End Sub
I found and adapted the
I found and adapted the following. This uses the windows kernal so you don't need excel to be installed. It will return the following based on the computers regional settings so this is great for using for list separators which may be a "," in some languages while a ";" in others.