Tables

Application configuration > Data model editor > Tables

A table is a category that defines the type of data and other characteristics possessed by a group of data. In other words, a table is used as a container for data of the same kind. For instance, the table called Contact stores all information related to our business contacts.

In a new application, the Contact table exists by default. It is meant to store the details of contact persons. Initially, it can only store e-mail addresses, but it can be expanded to store other fields like the first name, last name, and telephone number of the contact person.

The data stored in the table is arranged in records, which are also arranged in fields. In practice, each record can be regarded as a row, or horizontal line, of data contained in the table, while fields correspond to columns (read the comparison of an IS Tools table with an Excel spreadsheet and a database table, for a better understanding).

Once a table is selected, click the OpenTableRecords Open table records icon to start creating and managing records without the need for an application form. This capability makes it a great tool for administrators.

The Data model editor allows the application administrator to perform the following actions related to tables:

Create a table

Click the BlackPlusIcon icon in the Tables tab. By default, a new table is named "New table" (subsequent tables are named "New table n", where n is a sequential number). Type a new name for the table. Note that a plus icon appears to the left of the table name, indicating that an unsaved change is pending for this table. Click the Save button. Note also that, in addition to the name, you may see the Id column. Optionally, configure the table with additional settings, by clicking the Details tab:

Name. Enter a unique name for the table.
Description. Optionally, enter text to explain the purpose of the table.
Default record group. Specifies the default record group to which new records will belong. If the drop-down is left blank and more than one record group exists, a record group must be selected when the records are created in application forms. In desktop mode, this is done via this button or via a Record group field manually added to the form. Note that this button is not supported in mobile mode.

Record name policy:

Mixed case. The record name is stored exactly as typed by the user, preserving the original capitalization.
Only uppercase. The record name entered by the user is automatically converted to UPPERCASE when saved.
Only lowercase. The record name entered by the user is automatically converted to lowercase when saved.

Generated series. The record names will be automatically generated by the system as a sequential number, starting with the number specified in the Start name at text box; otherwise, the default value is "1", i.e., the first record name will be 1. This setting only allows numbers, which are displayed as read-only in forms.

Even though the Record name policy can be changed at any time during the application’s lifecycle, it is best practice to set the correct configuration when creating the table to avoid inconsistencies. Review the documentation notes before importing and editing records.

Max record name length. If the Record name policy was set to Mixed case, Only uppercase or Only lowercase, the maximum length of the record name can be constrained in this text box.
Log changes. Select this setting to register and show changes made to records in the Logged Data Search system form, or in an application form’s fields via the Show history in tooltip checkbox. Note that this setting is applied per table.

After configuring the table settings, proceed to create its fields.

Edit a table

To rename a table, double-click the current name, type a new name, and click Save.

To edit a table setting, select the table. You can modify its settings in the Details tab at any time.

Changing the Record name policy setting only affects new records created after saving the change.

If you wish to convert the case of existing records, e.g., from lowercase (the original case) to uppercase (the newly selected case), do the following:

Export the records to Excel. You can do this via Reports or Quick Excel report.
In Excel, use the formula to convert the Record name to the desired case, e.g., =UPPER(A2)
Once the text is converted, use the Record rename import for <selected table> in the Prepare import run form to update the name of the records.

If records already exist in the table after the Generated series option is set:

Users cannot edit existing record names. The Record name field automatically becomes read-only.
Text-based names are ignored. The next new record will have the number specified in the Start name at box as its record name, regardless of whether the existing list of records have names as text, e.g. "John Smith".
Numeric names determine the sequence. If existing records contain numerical names, the new record will be named using the next consecutive number after the highest existing number.
- Example 1. An existing record name is "1003", and the Start name at number is just set to "10". The next new record will be named as "1004".
- Example 2. An existing record name is "8", and the Start name at number is just set to "10". The next new record will be named as "11".

Read the description of these and other settings, in the Create a table topic.

When a table is edited in any way (including its name, settings, or fields), an asterisk (*) appears to the left of the table name. This provides visual feedback indicating that there are pending changes that need to be saved. At this point, navigating away from the current form will discard any pending changes. Click the Discard changes button to undo edits. Once the changes have been saved, they cannot be undone (except through manual editing).

Delete a table

A table can be deleted by selecting it, clicking the DeleteForm icon, and clicking Save.

A table can only be successfully deleted if all the following conditions are met:

The table contains no fields. All fields within the table must be deleted first.
The table is not used in any application form. Any components associated with this table must be deleted, or edited by removing its fields, or have their Datasource changed.
The table is not used in any report or import configuration. Delete any templates based on this table or remove its relationships.

Relationships between tables

A table can be related to other tables through fields of the Relation type. The table where the Relation field is created is known as the child table, which is subordinated to another table, known as parent table or target table. This connection is called a many-to-one relationship, i.e., many children pointing to one parent. When the relation between tables is observed from the child table towards its parent table, the relation is known as normal or forward relationship.

In the opposite direction, if the relation between tables is observed from the parent table towards its child table, the relationship is called reverse. This setup represents a one-to-many relationship, i.e., one parent with many children.

This means that the definition of the relationship depends on the perspective of the table under consideration. For instance, given a relationship of B→A, where "B" is the child table and "A" is the parent table (meaning that "B" belongs to "A"), it can be said that "B" has a forward relation with "A" (many B records point to one A record), while "A" has a reverse relation with "B" (one A record points to many B records).

In IS Tools, a relationship is always configured as a field in the child table that points to the parent table.