SQL view creation for a form

For each table that is built in the system (except for the attachment tables), an SQL view is automatically created. This view uses the form name as the view name and the field names (not a display label in one of the views) as the column names. The names are created by using the following rules:

• All alphabetic and numeric characters remain as defined.
• All other characters are converted to an underscore (_ ).
• If the first character is not alphanumeric, a leading A is added to the name.
• If the name of a field is blank, a field name with a leading A followed by the fieldId is used.
• If the name is one of the reserved words for the database, the string _x is appended.
• If the name exceeds 30 characters, the string length is trimmed to 30 characters. Also if the 30 characters of the name contain any multi-byte characters, the name is further trimmed to accommodate the 30 bytes.

The name of the table must be unique after the conversion. If it is not, three digits are appended to it, beginning with 001 (if necessary, the name is truncated to fit the maximum length allowed for an SQL name). If 001 does not make the name unique, 002 is tried, then 003, and so on until a unique name is created. Column names must also be unique, so the same naming convention is used.

The name of the SQL view must also be unique after the conversion. If it is not, the schema ID is appended to it (if necessary, the name is truncated to fit the maximum length allowed for an SQL name). Column names must also be unique, so the same naming convention is used.

The SQL view of the status history table follows the same strategy as the SQL view of the base table. The name of the table is created by adding SH_ to the front of the name of the base table view. The column names are mapped to the name of the Request ID field, and the names of each of the Status values with _TIME and _USER appended. So a form with two states, New and Closed, ends up with columns in the view named Entry_Id, New_USER, New_TIME, Closed_USER, and Closed_TIME.

These SQL views are re-created when the name for the field is changed or when a change is made to the form that affects the underlying table (deleting a field, adding a field, or changing the length of a field).

You can use the view or the base tables to read data from the database. The SQL views are especially useful when a third-party report writer is used because the names of its tables and columns are easier to use than their internal, numeric representations in the base tables.