Normalize Field Name

Brian Element

This snippet will take a variable and translate it to how it would appear as an IDEA field name.

Snippet

Field names in IDEA need to be uppercase and not contain any special characters. This snippet will first take a field name and change it to upper character and then check and change any non alpha numeric characters to an underscore.  It could be used if you are asking a user for a field name that you will be creating, so that the variable will hold the same as what IDEA will show you would use this funciton as IDEA will automatically make changes to the field name and if you don't do the same you may have errors in your script.

Function normalizeFieldName(sTempFieldName As String) As String
	Dim iLen As Integer
	Dim i As Integer
	Dim sChar As String
	Dim sNewFieldName As String
	
	sTempFieldName = UCase(sTempFieldName)
	iLen = Len(sTempFieldName)
	
	For i = 1 To iLen
		sChar = Mid(sTempFieldName, i, 1)
		If (Asc(sChar) < 48 Or Asc(sChar) > 57) And (Asc(sChar) < 65 Or Asc(sChar) > 90) Then
			sNewFieldName = sNewFieldName & "_"
		Else 
			sNewFieldName = sNewFieldName & sChar 
		End If  	
	Next i
	
	normalizeFieldName = sNewFieldName
End Function

 

avikerem Thu, 04/30/2020 - 18:22

Hi Brian
The problem with field names restrictions, such as uppercase only, is that field names cannot include characters from the extended part of the ASCII table (IE 128 - 255) were foreign languages store their native character set.
The strange thing is that it is not quite true. If you ignore the restriction and provide foreign language, such as hebrew, field names during import, IDEA accept these non-standard characters, but present the field namesas as gibberish.
To make things even strangier field manipulation actually shows these field names correctly. well, almost all of them correctly, except one letter which is shown as underscore... 
I personnaly dont care so much about this restriction and I am used to convert all field names to english manually during import or automatically. But I am asked again and again why can't IDEA support a larger set of characters from the full ASCII table.
Any hope that this will be addressed in the future?
Have a pleasant evening
Avi Kerem
 

avikerem Thu, 04/30/2020 - 18:46

I am uploading an example. As can be seen The hebrew fields have been loaded, however any change to the field names must be from the restricted part of the first half of the ASCII table.