Field types – what a field can hold¶
Every field of an entity has a field type. It decides which values the field accepts, how they are stored in the database and what can be done with them in expressions, queries and forms. This page describes each field type with its settings and limits.
It is intended for administrators who design entities. How entities and fields are created and changed is described in Entities and fields.
1. Field types at a glance¶
Fields are added in the entity editor, tab "Columns", with "Add Column". The menu names the field types as follows:
| Menu entry | Shown as "Field Type" | Holds | Typical use |
|---|---|---|---|
| "String Field" | String | Text | Names, titles, descriptions, mail addresses, status values |
| "Double Field" | Double | Numbers with decimal places | Amounts, percentages, measurements |
| "Int64 Field" | Int64 | Whole numbers | Quantities, counts, running numbers |
| "Date Field" | Date | A calendar date without a time | Due dates, birth dates |
| "DateTime Field" | DateTime | A date with a time of day | Time stamps, appointments |
| "Boolean Field" | Boolean | Yes or no | Flags such as "Closed" or "Approved" |
| "Guid Field" | Guid | The id of a record | The key of a relationship, a reference to a user |
Summary¶
- The field type is chosen once, when the field is added. It cannot be changed afterwards – the setting "Field Type" is read-only. To change a type, add a new field (see section 10).
- Every field type except Guid has the switch "Null allowed". Only String fields have a further setting, "Max Length".
- There are no field types for files, pictures or choice lists. Files are attached to a record with the files block of a form (see Forms). Choices are modelled as a String field or as a relationship to an entity that holds the possible values.
2. Settings every field has¶
These settings are the same for all field types. They are described in detail in Entities and fields, section 4.
| Setting | Meaning |
|---|---|
| "Display Name" | The name shown in the editors |
| "Storage Name" | The name of the column in the database – required |
| "Description", "Tags" | For documentation and for finding the field |
| "Field Type" | Read-only, see section 1 |
| "Null allowed" | Whether a record may have no value in this field (not for Guid fields) |
| "Use in AI Search", "Use in Full Text Search" | Search settings, see Entities and fields, section 3 |
| "Virtual Field" | The field has no column and only exists while a record is open |
| "Run Business Rules on load" | Calculation rules triggered by this field run again whenever a record is opened |
| "Flags" | Special roles of the field, see Entities and fields, section 9 |
"Null allowed" and the empty value¶
New fields start with "Null allowed" switched on. When it is off, the database does not accept a missing value. nexxcite then stores the empty value of the field type instead:
| Field type | Empty value |
|---|---|
| String | Empty text |
| Double, Int64 | 0 |
| Boolean | No |
| Date, DateTime | 1 January 1753 – shown as an empty value in grids, labels and texts |
| Guid | The empty id 00000000-0000-0000-0000-000000000000 |
The empty value is written when a new record is saved without a value in the field, and when "Null allowed" is switched off for a field that already has records without a value.
"Null allowed" is a database setting. It does not make a field mandatory in a form. Use a business rule to require a value from the user (see Business rules).
3. String¶
At a glance¶
| Purpose | Text of any kind |
| Typical use | Names, titles, descriptions, mail addresses, codes, status values |
| Related | Full-text search (section 3 of Entities and fields) |
Settings¶
| Setting | Meaning | Default |
|---|---|---|
| "Max Length" – "Specific Length" | The maximum number of characters, entered in the field next to it. Allowed are 1 to 4000. A larger number is turned into "Max Length". | "Specific Length", 50 characters |
| "Max Length" – "Max Length" | The largest length the database supports. Use it for long texts such as descriptions or mail bodies. | – |
Behaviour¶
- Only String fields can be part of the full-text search.
- Making a field longer is always possible. Making it shorter fails while records hold longer values: the database refuses the change and the entity is not published. Shorten the values first, for example with an action.
Examples¶
- "Ticket title": "Specific Length" 200.
- "Description": "Max Length".
- "Country code": "Specific Length" 2.
4. Double¶
At a glance¶
| Purpose | Numbers with decimal places |
| Typical use | Amounts, prices, percentages, weights |
| Related | Mathematic expressions, aggregates over relationships |
Settings¶
No settings besides "Null allowed".
Behaviour¶
The database stores numbers of this field type with five decimal places and up to fourteen digits before the decimal point. A value with more decimal places is rounded when it is stored.
Example¶
"Net amount" holds 1499.9, "Discount" holds 0.125.
5. Int64¶
At a glance¶
| Purpose | Whole numbers |
| Typical use | Quantities, counts, running numbers drawn from a counter |
| Related | Counters |
Settings¶
No settings besides "Null allowed".
Behaviour¶
In calculations, the values of this field type are whole numbers. The database column, however, holds whole numbers only from −2,147,483,648 to 2,147,483,647. A larger value cannot be saved.
Example¶
"Quantity" holds 12. "Ticket number" receives the next number of a counter when a ticket is
created.
6. Date¶
At a glance¶
| Purpose | A calendar date without a time of day |
| Typical use | Due dates, birth dates, contract start |
| Related | Date and time calculations in expressions |
Settings¶
No settings besides "Null allowed".
Behaviour¶
Only year, month and day are stored. Choose this type whenever the time of day has no meaning.
7. DateTime¶
At a glance¶
| Purpose | A date with a time of day |
| Typical use | Time stamps, appointments, the time a status changed |
| Related | "Use UTC for system timestamps" in the Global settings |
Settings¶
No settings besides "Null allowed".
Behaviour¶
- The database stores dates from 1 January 1753 with a precision of a few milliseconds.
- The fields "Created At" and "Last Modified At" that every entity has are DateTime fields. Which server clock fills them – local time or UTC – is set in the global settings.
8. Boolean¶
At a glance¶
| Purpose | Yes or no |
| Typical use | "Closed", "Approved", "Newsletter wanted" |
| Related | Logical expressions |
Settings¶
No settings besides "Null allowed".
Behaviour¶
With "Null allowed" on, a Boolean field knows three states: yes, no and no value. Switch it off when "not set" should simply mean "no".
9. Guid¶
At a glance¶
| Purpose | The id of a record |
| Typical use | The key field of a relationship, a reference to a user, the record id itself |
| Related | Relationships |
Settings¶
No further settings. A Guid field has no "Null allowed" switch – it never accepts a missing value.
Behaviour¶
- An empty reference is stored as the empty id
00000000-0000-0000-0000-000000000000. - Every entity has a Guid field "RecId" that identifies each record, and two Guid fields "Created By RecId" and "Last Modified By RecId" (see Entities and fields, section 5).
- A Guid field alone only stores an id. Showing the referenced record in a form, querying across it or counting related records needs a relationship that uses the field as its key – see Relationships.
Example¶
The entity "Ticket" has a Guid field "Customer RecId". The relationship "Customer" of the ticket uses it as "Foreign Key in Parent" and makes the customer of a ticket available in forms and expressions.
10. Changing the type of a field¶
The field type cannot be changed in place. Use these steps instead:
- Add a new field with the new type, for example "Amount (number)" next to the String field "Amount".
- Publish the sketch.
- Copy the values with an action that runs over all records and converts them.
- Switch forms, grids, queries and expressions to the new field.
- Remove the old field once nothing uses it any more. Removing it deletes its values in the database.
11. Limitations¶
- Int64 fields hold only values from −2,147,483,648 to 2,147,483,647 in the database, although the type name suggests a larger range.
- Double fields store five decimal places.
- Date and DateTime fields cannot hold dates before 1 January 1753.
- The field type of an existing field cannot be changed.