Entities and fields – how nexxcite stores your data¶
This chapter describes entities and their fields: how you create and change them in the entity editor, which fields every entity has, what happens in the database when you add, rename or remove a field, and which special entities nexxcite uses itself.
It is intended for administrators who design the data model of an installation. The individual field types are described in Field types, connections between entities in Relationships.
1. Entities at a glance¶
An entity is a kind of business object, for example "Ticket", "Customer" or "Invoice". It has fields – "Title", "Due date", "Status" – and is stored as a table in the database: every record is a row, every field a column.
| Term | Meaning |
|---|---|
| Entity | A kind of business object with its fields. Most other definitions – forms, grids, queries, business rules, actions – belong to an entity. |
| Field | One value of a record, with a field type such as text, number or date |
| Record | One business object, for example one ticket |
| Storage name | The name of the table (entity) or column (field) in the database |
| Schema migration | The change to the database that nexxcite makes when a changed entity is published |
| Virtual entity, virtual field | An entity without a table, a field without a column. Their values only exist while a record is open. |
Entities are managed in the Admin Studio under Entities. Selecting an entity shows its "Properties" – the entity editor with its fields and settings – and everything configured in its context: relationships, forms, grids, business rules and so on (see Admin Studio).
Summary¶
- Entities differ from all other definitions: publishing a changed entity changes the database. Adding is harmless. Removing a field or an entity deletes its data.
- Every new entity gets a record id, four audit fields and the business rules that fill them.
- The type of a field cannot be changed once it exists.
- An entity is only usable with a default form and a default grid. Create both after the entity and select them in the entity editor.
2. Creating an entity¶
- Open a sketch (see Sketches and publishing).
- Open the management view of the entities – for example with "Search everything" and "Entity Manager" – and choose "Add".
- In the tab "General", enter the "Display Name", for example "Ticket", and the
"Storage Name", for example
Ticket. The storage name is required and is not derived from the display name. - In the tab "Columns", add the fields with "Add Column". Give every field a "Storage Name" as well.
- Save. The entity is collected in the sketch.
- Create a form and a grid for the entity, then open the entity again and select them as "Default Form" and "Default Grid".
- Publish the sketch. nexxcite now creates the table.
Storage names may contain letters and digits only, no spaces. Choose them carefully: they are the names the database and external tools such as reports see.
Example: The entity "Ticket" gets the storage name Ticket, the fields "Title" (Title, String,
200 characters), "Due date" (DueDate, Date) and "Closed" (Closed, Boolean).
3. Settings of an entity¶
The entity editor has the tabs "General", "History", "Searching", "Columns" and "Indexes". The tab "General" starts with the "Display Name", the "Description" and the "Tags" of the entity.
Tab "General"¶
| Setting | Meaning | Default |
|---|---|---|
| "Storage Name" | The name of the table. Required for an entity that is not virtual. Changing it renames the table. | empty |
| "Plural Name" | The plural of the entity name, for example "Tickets". Expressions can read it with the system function EntitySetPluralName. |
empty |
| "Color" | A colour that identifies the entity in the user interface | empty |
| "Default Grid" | The grid used whenever a list of records of this entity is needed without naming a grid. Only grids of this entity are offered. | empty |
| "Default Form" | The form that opens when a record is created or opened without naming a form – from links, the global search or record lists. Only forms of this entity are offered. | empty |
| "Public ID" | The field whose value is the number users know a record by, for example the ticket number. It is shown in the form header and used where a record is named in messages. Without it, the record id is used. | empty |
| "Form/Page Title" | An expression that returns the title of a record, for example first and last name combined. Used wherever a record is named, for example by AI assistants, and available through the system function EntitySetTitle. |
empty |
| "Flags" | Special roles of the entity, see section 9 | none |
| "Virtual" | The entity has no table, see section 8 | off |
Tab "History"¶
| Setting | Meaning | Default |
|---|---|---|
| "History Action" | An action that runs after every save of a record of this entity, typically to write history entries. It runs with the saved record and receives the changes in the special variable "Entity Set Changed Data". | empty |
| "Relationships to reload" | Relationships whose loaded records are read again after the history action ran – for example the relationship that shows the history entries in the form. | none |
Tab "Searching"¶
| Setting | Meaning | Default |
|---|---|---|
| "Enable AI Search" | Allows marking fields with "Use in AI Search". It does not index anything by itself – what an AI assistant searches is configured in its index configuration (see AI assistants). | off |
| "Enable Full Text Search" | Creates a full-text index over the String fields marked "Use in Full Text Search" and makes the entity a source of the global keyword search | off |
| "Global Search Preview Form" | The form that shows one result in the global search. Without it, the entity does not appear in the global search at all. | empty |
| "Global Search Quick Filters" | A query filter whose quick filters and sort options are offered above the search results of this entity | empty |
| "Open Record On Click" | A click on a result opens the record with its default form. Without it, a click only closes the search. | off |
| "Custom On Click Action" | An action that runs for the clicked record. Not available while "Open Record On Click" is on – opening the record wins. | empty |
| "Allow Saving To Collections" | Users with the permission "Save records to collections" get a bookmark button in the header of records of this entity and can save them to their collections (see Collections) | off |
Which entities a user can search in is set per role (tab "Global Search" in the role editor, see Roles and permissions).
The full-text index uses the full-text search of SQL Server. It is created when the entity is published and needs the primary key index of the entity (section 6).
4. Fields¶
The tab "Columns" lists the fields of the entity with "Name", "Display Name", "Storage Name", "Field Type", "Max Length", "Tags" and "Description". Selecting a field shows its settings below the list.
- "Add Column" adds a field of the chosen type (see Field types). It is inserted at the end of the list and selected.
- "Remove Column" removes the selected field. When the entity is published, the column and all its values are deleted.
| Setting | Meaning | Default |
|---|---|---|
| "Display Name" | The name shown in the editors. The technical "Name" of the field is derived from it: "Due date" becomes DueDate. |
"New … Field" |
| "Storage Name" | The name of the column. Required for a field that is not virtual, unique within the entity, letters and digits only. Not derived from the display name. | empty |
| "Description", "Tags" | Documentation; tags can be searched in the list | empty |
| "Field Type" | Read-only. Chosen when the field is added. | – |
| "Max Length" | String fields only: "Specific Length" (1 to 4000 characters) or "Max Length" | 50 characters |
| "Use in AI Search" | Marks the field for the AI search. Only available with "Enable AI Search". | off |
| "Use in Full Text Search" | Adds the field to the full-text index. Only available for String fields and with "Enable Full Text Search". | off |
| "Virtual Field" | The field has no column, see section 8 | off |
| "Run Business Rules on load" | The calculation rules that react to a change of this field run again every time a record is opened, as if the field had just changed. Use it for values that depend on something besides the stored fields, for example today's date. Costs a rule run per opened record. | off |
| "Null allowed" | Whether a record may have no value in the field. Not offered for Guid fields. See Field types, section 2. | on |
| "Flags" | Special roles of the field, see section 9 | none |
Renaming a field¶
- Changing the "Display Name" changes the name shown in the editors and the technical name. Forms, grids, queries and expressions refer to the field itself, not to its name, and keep working. Applications that write records through the REST API address fields (see Fields and values) by their technical name or storage name – check them after renaming.
- Changing the "Storage Name" renames the column in the database when the entity is published.
5. Fields every entity has¶
A new entity starts with these fields. They are added by the editor; you do not create them yourself.
| Field | Type | Filled by | Flag |
|---|---|---|---|
| "RecId" | Guid | A new id when the record is created | SpecialField.RecId |
| "Created At" | DateTime | The time the record was created | SpecialField.CreatedAt |
| "Last Modified At" | DateTime | The time of every save | SpecialField.LastModifiedAt |
| "Created By RecId" | Guid | The user who created the record | SpecialField.CreatedByRecId |
| "Last Modified By RecId" | Guid | The user who saved last | SpecialField.LastModifiedByRecId |
The values are filled by business rules that nexxcite creates together with the entity when the entity is saved for the first time: a default value rule for "RecId", "Created At" and "Created By RecId", and a rule that runs before every save for the two "Last Modified" fields. They appear among the business rules of the entity and can be changed like any other rule.
- "RecId" identifies every record and is the primary key of the table. Never remove it.
- nexxcite finds these fields by their flag, not by their name. A field with the flag
SpecialField.RecIdidentifies the records even if its storage name is not "RecId", for example in an entity built on an existing table whose key column has another name. - The two time stamps use the local server time or UTC, depending on "Use UTC for system timestamps" in the Global settings.
- A process without a user, for example a scheduled action, writes the empty id into the two user fields.
- Records written without going through these rules – for example directly in the database – have no time stamps.
6. Indexes¶
An index makes searching and sorting by certain fields faster. It does not change what is stored. The tab "Indexes" lists the indexes of the entity; "+" adds one, "+" next to "Fields" adds a field to an index.
| Setting | Meaning |
|---|---|
| "Display Name" | The name of the index. The name in the database is derived from it and must be unique within the table. |
| "Primary Key" | The index is the primary key of the table. Every entity has exactly one, on "RecId", named after the storage name of the entity (PK_Ticket). |
| "Clustered" | The records are stored in the order of this index. Only one index per table can be clustered – the primary key is clustered by default. |
| "Is Unique" | The database refuses two records with the same values in the fields of the index. A real constraint, not only a speed-up. |
| "Field" | A field of the index. The order of the fields matters: an index on (A, B) helps a filter on A, not one on B alone. Virtual fields cannot be indexed. |
| "Sorting" | The direction of the field in the index. Only "Ascending" creates an ascending index field; "None" and "Descending" both create a descending one. |
| "Include Column" | The field is carried along in the index but not searched by. For fields that a query reads but does not filter on. |
Add an index only for fields that a query filters or sorts on every time, for example "Status" and "Due date" of a ticket list that always shows open tickets by due date. Each index slows down saving a little.
A changed index is removed and created again when the entity is published. On large tables this can take a while.
7. What happens in the database¶
When an entity is published – with the sketch, or immediately when saving without a sketch – nexxcite changes the table of the entity. The change is made before the entity is saved: if it fails, the entity keeps its previous state.
Summary¶
- Adding is safe. Removing deletes data.
- A change that cannot work is refused when you save in the entity editor, with an error message.
- A change that the database refuses during publishing leaves that entity unpublished; nexxcite names it when the sketch is published.
- Back up the database before publishing changes that remove fields or entities. nexxcite cannot undo them.
Change by change¶
| Change in the editor | Effect in the database on publishing | Remark |
|---|---|---|
| New entity | A new table with the primary key, all fields and indexes | Fails if a table with the storage name already exists |
| New field | A new column | Existing records get no value, or the empty value of the type if "Null allowed" is off |
| "Display Name" of a field changed | Nothing | – |
| "Storage Name" of a field changed | The column is renamed | Values are kept |
| "Storage Name" of the entity changed | The table is renamed | Values are kept. The primary key and the full-text index are created again. |
| "Max Length" increased | The column is enlarged | – |
| "Max Length" reduced | The column is shortened | Fails while records hold longer values |
| "Null allowed" switched off | Records without a value get the empty value of the type, then the column refuses missing values | Can take a while on large tables |
| "Null allowed" switched on | The column accepts missing values | – |
| Field type changed | Not possible – the setting is read-only | See Field types, section 10 |
| Field removed | The column and all its values are deleted | Cannot be undone |
| "Virtual Field" switched on for a stored field | The column and all its values are deleted | Same as removing the field |
| "Virtual" switched on for a stored entity | The table and all records are deleted | The editor warns about this |
| Entity deleted | The table and all records are deleted | Forms, grids, rules and other definitions of the entity stay and have to be deleted separately |
| "Enable Full Text Search" switched on or off | The full-text index is created or removed | – |
| Index added, changed or removed | The index is created, created again or removed | A unique index fails if duplicates exist |
When you save in the entity editor¶
Before saving, the editor checks whether the change can be migrated. It refuses to save, with an error message, when for example
- the entity or a stored field has no "Storage Name",
- the entity has no "RecId" field,
- the type of an existing field differs from the stored one,
- "Enable Full Text Search" is on and the entity has no named primary key index, or a field that is not a String field is marked for the full-text search.
When the database refuses a change¶
Some problems only show when the change reaches the database – a table or column name that already exists, values that are too long for a shortened column, duplicates under a new unique index, a time limit that is exceeded.
- With a sketch: that entity is not published, all other changes of the sketch are. The sketch stays open and names the entity (see Sketches and publishing).
- Without a sketch: the failure is only written to the log. Check the entity afterwards.
The database changes of one entity are made one after another. If a later step fails, the earlier steps of the same entity are already done – for example a column is already renamed while the entity still has the old storage name. Restore the database backup or align the entity with the database before publishing again.
Each step may take up to 10 minutes by default (DataAccess:SchemaMigrationTimeout, see
Installation and configuration).
8. Virtual entities and virtual fields¶
Virtual fields¶
A field with "Virtual Field" has no column. Its value exists only while a record is open, typically calculated by a business rule – for example "Days until due". It is never written to the database, cannot be used in queries and cannot be indexed.
Virtual entities¶
An entity with "Virtual" has no table. It needs no storage name and gets no schema migration. Its records exist only while they are open, for example in a form that collects input for an action. They cannot be saved or queried.
Switching¶
| From | To | Result |
|---|---|---|
| Stored field | Virtual field | The column and its values are deleted |
| Virtual field | Stored field | Not supported – the column is not created. Add a new field instead. |
| Stored entity | Virtual entity | The table and all records are deleted |
| Virtual entity | Stored entity | Not supported – the table is not created. Create a new entity instead. |
9. Special entities and flags¶
Some entities and fields have a special role that nexxcite relies on. The role is set with "Flags" in the tab "General" (entity) or in the settings of a field.
Entity flags¶
| Flag | Role |
|---|---|
SpecialPurpose.UserProfile |
The user profile entity. nexxcite creates it on the first start as "UserProfile". There must be exactly one. |
SpecialPurpose.JoinTable |
A join table that stores the links of many-to-many relationships, see Relationships |
SpecialPurpose.Group |
The entity whose records are user groups. nexxcite creates it on the first start as "Group". It is read by the system functions for the groups of the current user and by the team collections. |
SpecialPurpose.GroupMembers |
The join table between groups and users. nexxcite creates it on the first start as "GroupMember": SpecialField.ParentRecId is the group, SpecialField.ChildRecId the user profile record. |
SpecialPurpose.Collection |
The collections users create for themselves. nexxcite creates it on the first start as "Collection", see Collections |
SpecialPurpose.CollectionItem |
The entries of the collections – saved records and saved queries. nexxcite creates it on the first start as "CollectionItem" |
Field flags¶
| Flag | Role |
|---|---|
SpecialField.RecId |
The record id and primary key (section 5) |
SpecialField.CreatedAt, SpecialField.LastModifiedAt, SpecialField.CreatedByRecId, SpecialField.LastModifiedByRecId |
The audit fields (section 5) |
SpecialField.Email |
User profile: the mail address of the user |
SpecialField.UserDefId |
User profile: the link to the user account |
SpecialField.DisplayName |
User profile: the name shown for the user |
SpecialField.DefaultGroupId |
User profile: the default group of the user |
SpecialField.UserAvatar |
User profile: the picture shown on the user menu button, see Users, section 7 |
SpecialField.GroupName |
Group entity: the name of the group |
SpecialField.CollectionName, SpecialField.CollectionOwnerRecId |
Collection entity: the name and the owner (a user profile record) |
SpecialField.CollectionItemType, SpecialField.CollectionItemTitle, SpecialField.CollectionItemPayload, SpecialField.CollectionItemGridDefId, SpecialField.CollectionItemListDefId |
Collection item entity: the kind of entry (Record or Query), its title, the conditions of a saved query and the grid or list it opens with |
SpecialField.ParentTypeId, SpecialField.ParentRecId, SpecialField.ChildTypeId, SpecialField.ChildRecId |
Join tables: the four fields that store a link |
nexxcite looks for these entities by their flag. An entity you created and flagged yourself before the first start with this version is kept – nexxcite only creates the "Group", "GroupMember", "Collection" and "CollectionItem" entities when no entity carries the flag yet.
Do not remove a flag that nexxcite has set, and do not give the same flag to two fields of an entity.
Without its flags, an entity loses its special role – a user profile without SpecialField.Email, for
example, gives nexxcite no mail address for password reset links.
The user profile entity¶
Personal data of users – name, mail address, phone number – is stored in records of the user profile entity, one record per user. nexxcite creates it on the first start with these fields: "Salutation", "FirstName", "LastName", "UserDefId", "EMail", "Image", "FullName", "Company", "Office", "JobTitle", "Phone", "Cellphone", "Manager" and "TeamsStatus", and a default form.
You can add fields to it like to any other entity, for example "Department" or "Cost center". How profiles are shown and used is described in Users, section 7.
10. Permissions¶
| Permission | Allows |
|---|---|
| "Open Entity Manager" | Seeing the entities |
| "Add Entity" | Creating and cloning entities |
| "Edit Entity" | Changing entities |
| "Delete Entity" | Deleting entities |
All four are part of "System Developer". See Roles and permissions.
11. Limitations¶
- The type of an existing field cannot be changed.
- A virtual field cannot be turned into a stored field, and a virtual entity not into a stored entity.
- A virtual entity cannot be deleted: the deletion tries to remove a table that does not exist and fails.
- "Clone" in the management view copies the entity with the same storage name. The copy cannot be published until it gets its own storage name, and it does not get the business rules that fill "RecId" and the audit fields. Create new entities with "Add" instead.
- Deleting an entity does not delete the forms, grids, relationships and other definitions that belong to it.
- The database changes of one entity are not made as a whole: if one step fails, the earlier ones stay done (section 7).
12. Frequently asked questions¶
Can I undo the removal of a field? No. The values are deleted when the entity is published. Restoring a database backup is the only way back. A rollback sketch adds the field again, but empty.
Why can I not change the field type? Converting existing values can lose data – text that is not a number, numbers that do not fit. Add a new field and copy the values with an action (see Field types, section 10).
Does renaming a field break my forms and expressions? No. They refer to the field itself. Only external applications that address the field by name through the REST API need to be checked.
Why is my entity not found in the global search? It needs a "Global Search Preview Form", and the role of the user has to list the entity in its tab "Global Search".
Can an AI assistant change my data model? It can create entities and add fields and relationships. It cannot remove fields, change field types, make an optional field required or make stored fields or entities virtual – those changes are left to an administrator in the entity editor.