With Evolutility field types are not only data types but also "behavioral types" or "UI types".
For example fields for a URL, an email, or a phone number are of different types for the user as they behave differently, while they have the same data type in the database (varchar or nvarchar).
Most fields types map to a single database column in the driving table; "lov" (list of values) maps to a foreign key column and another table (using additional attributes); and "formula" may be a SQL formula like a simple concatenation of different column values, or a full a sub-query.
Changing a field type usually requires also changing the field type of the database column associated to the field. It also determines field validation rules.
As seen below, each field type looks and behaves differently in different views.
Boolean fields are Yes/No values displayed as checkboxes. A Boolean field is stored as a numeric value (Yes=1, No=0 or null). The most efficient database column type for it is bit.
Dates are displayed as an input box with a date picker in edit mode, and as a formatted string in other modes. The Javascript for the date picker is an external JS file which can be customized. Possible database column types are datetime or smalldatetime.
These types are used for numeric values. Decimal can be stored as data type money or decimal. Integer can be smallint, int, bigint…
Documents are displayed as a link for download in view mode, as a text box with a browse button for upload in edit mode, as a checkbox in the search and advanced search modes. Like images, documents are stored on the file server and only the filename is stored in the database.
Text value displayed as a text box in edit mode and hyperlink in other modes. These can be stored as varchar, or nvarchar.
SQL formula or sub-query. The calculation SQL is entered in the dbcolumn attribute of the field. Fields of type formula cannot be edited by users.
Example of formula field:
<field type="formula" readonly="1" label="Photos" format="0 'photos'" dbcolumnread="NBphotos" dbcolumn="SELECT COUNT(*) FROM EVOL_Photo P WHERE P.albumid=T.id" ... >
Images are displayed as such in view mode, as a box with a browse button for upload in edit mode, as a checkbox in the search and advanced search modes. Images are stored on the file server, only the filename is stored in the database, as a varchar or nvarchar.
The "html" field type is used to display Rich Text Format (RTF) or HTML. It uses TinyMCE widget for WYSIWYG edition in the browser.
Lists of values are choices of values displayed as drop-down lists in edit mode or as the string of the selected value in view mode. They correspond to joins to secondary tables in the database and are stored in the driving table as a number which is the primary key of the value in the secondary table.
Using certain attributes of the field it can become a many-to-many relationship instead of a one-to-many.
This type is the most commonly used one. It is displayed as a text box in edit mode. It is a string stored as varchar or nvarchar.
Fields of these types are displayed as big text boxes (HTML "textarea") and can spread over several rows. They can be stored as text, varchar, or nvarchar.