Field Names

[This is preliminary documentation and is subject to change.]

Some form item types generates fields (columns) in the dynamic database tables - some (used for layout etc.) do not.

The form items types, that have a DataName property, generate one field in the database table (there are exceptions that generate two or more fields).

This topic lists a few things to be aware of when choosing values of the DataName.

DataName rules

TOPICA is designed to work on SQL Server databases. In SQL Server it is technically possible to use almost any character in table and column names, but if table or column name contain certain special characters, those names must be specified in brackets ([...]) when referred in SQL.

TOPICA does NOT use brackets when referring to tables and columns. This is the reason for the two first of the following requirements for DataName:

Allowed characters are: letters A-Z, digits 0-9 and underscore _ (see below!).
First character must be a letter.
Do NOT use field names that are reserved words in SQL Server:
Copy
```
SELECT
IDENTITY
TABLE
COLUMN
VARCHAR
etc.
```
Do NOT use field names that are match names of the fields, that are automatically generated in all tables:
Copy
```
ID
CREATED_BY
UPDATED_DY
DATE_CREATE
DATE_UPDATE
DATE_START
ORGUNIT_TYPE
ORGUNIT_CODE
PATIENT_ID
```
Note
In data model version 4.31 and later (and "logical deletion" is enbled), additional fields are generated: DELETED_BY and DATE_DELETE.
TOPICA databases are built with the option, that table and column names are NOT case sensitive. I.e. DataName values "myfield" and "MyField" are considered equal.
Normally, TOPICA generates HTML identifiers in generated input forms equal to DataName. But when using the ArrayTable element, TOPICA autogenerates HTML ids built according to this pattern: <DataName>_<NN> (where <NN> is a row index). TOPICA uses this pattern to keep track of which values are entered in which row in the ArrayTable. If the DataName already contains an underscore, this process may fail. So just as a precaution, do not use underscore in DataName (at least in fields in ArrayTable elements) - even if it is not technically forbidden.

Note
In data model version 4.31 and later (and "logical deletion" is enbled), additional fields are generated: DELETED_BY and DATE_DELETE.

DataName values (field names) must be unique within a table.

Note
This naturally goes AFTER "resolving" included forms. That is, if a form contains a field X, and includes an "include" form that also contains field X, an error will occur.

Note
If this rule is violated, a warning message appears when updating the data dictionary.

Same field name in different forms

The general rule is, that one form template generates one database table, and each data field in the form generates one column in the database table.

In SQL databases, column names in one table are indepent from column names in other tables. I.e. it is perfectly legal to have table A containing column X of one type, and table B containing column X of a completely different type.

Therefore, one might think that the same goes for fields in TOPICA form templates. But this is NOT the case. If form template A has a field X, and form template B has a field X, the two X fields MUST be same type!

To understand why this is the case, it is important to understand one of the built in features in TOPICA: the ability to "inherit" previously entered values.

An application contains 2 or more different forms, in which the end user must have the possibility to enter the same information. For example: both form A and form B have fields "Height" and "Weight" (used to calculate BMI).
"Height" does not change much between the points in time, where the forms are filled in. Therefore it is convenient, that this field has a default value equal to the last previously entered value - irrespective of in which form it was entered.
This might be configured by a suitable default computation (using TOPICA Basic). But if property DefaultLastPrevious is set (i.e. has value true), this default value is automatically computed - values of all fields with the same DataName (across all tables / forma) are considered in the process.
Obviously collecting the previously entered values across all tables requires, that all fields with a given name (across all tables) have exactly the SAME data type.

Important
This rule (that all fields with a given name (across all tables) must have exactly the SAME data type) is enforced even if the DefaultLastPrevious functionality is not used!

Important
If this rule is violated, a warning message appears when updating the data dictionary. If these warnings are ignored, the application might seem to be working, but TOPICA might be confused about the data type of the DataName. In the data dictionary each field has one specifeidc data type. If two different forms specify different types, one of these types (the one encountered first) is saved in the data dictionary.

Important

If this rule is violated, a warning message appears when updating the data dictionary. If these warnings are ignored, the application might seem to be working, but TOPICA might be confused about the data type of the DataName. In the data dictionary each field has one specifeidc data type. If two different forms specify different types, one of these types (the one encountered first) is saved in the data dictionary.