Understanding Outlook User-Defined fields (part 2)
Part 1 focused on what characters should be used to create any Outlook user-defined field (in fact, a good rule to apply creating any field in any database program).
Part 2 focuses on “why the field name you select may matter” when data is shared between Outlook and another program. This will only deal with the simplest of reasons and is not intended to be technically complete or overwhelming. The primary purpose is to simply highlight what may occur.
While every file/database system share a common basis for what is deemed to be a valid field name, there are differences that need to be handled on a case by case basis. In short, any program that deals with multiple file/database types needs to ensure that all the “exceptions” are handled. Once again, the emphasis is not on whether any given developer has dealt with every possible option but rather to minimize the potential of encountering a problem if the exceptions have not been addressed.
A quick example that demonstrates the issue of “invalid” characters can be easily seen between Microsoft Excel and Outlook. Create an Excel worksheet consisting of 2 rows with 2 columns. The first row is the “header row” containing the field(column) names for the subsequent data rows.
In row 1 – enter “FldName1″ in column 1 and “Fld.Name2″ in column 2. Enter whatever data you want in the two columns for subsequent rows. Import this worksheet into Outlook (you will need to create a “Named Range” in Excel to complete this).
What you will find is that when this worksheet is imported, the “.” (period) in “Fld.Name2″ is replaced by the “#” character since the period character is not considered a valid field name character by the Microsoft Excel file driver. As noted in Part 1, the “#” is also an invalid character in an Outlook user-defined field name but this is not relevant since you would only be mapping the Excel field to some corresponding Outlook field.
If a field name is created with one or more spaces, it must be bracketed with left and right square brackets (in other words the left/right brackets must be added to the field name) when used in an SQL statement (i.e. [First Name]) to prevent an error occurring.
Similarly if a field name contains a single quote character as part of the field name, each occurance of the single quote character must be “qualified” by the insertion of another single quote character. A field name created as <John’s Place> must be changed to [John”s Place] in order to be used in an SQL statement.
The purpose of this is not to explain the reasoning for the changes that are required nor to cover every possible option but strictly to point out that something needs to be done programmatically in order to avoid an error from occuring
Part 3 will focus on how you can retain the flexibility of having the field names be as meaningful as you want in the Outlook user-interface while simultaneously minimizing potential problems with other programs.
Category: Understanding Outlook
