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" is going to store all possible 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, and initially can only store e-mail addresses (but should probably be expanded to store the first name, last name and telephone number of the contact persons).

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. And fields correspond to columns in the table (read the comparison of an IS Tools table with an Excel spreadsheet and a database table, for a better understanding).

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 (and after that, New table n where n is a progressive number). Type a new name for it. Note that a plus icon appears at the left of the table name, indicating that an unsaved change is pending for this table. Click on the Save button, and the plus icon will disappear. After this, proceed to create its fields. Optionally, configure a table with additional settings, by clicking on the Details tab:

Name. Note that, besides appearing in the Tables column, the Name of a table is also shown in this section.
Description. A text may be typed to explain the purpose of the field.
Default record group. 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 on desktop mode, via this button . For mobile mode, note that this button is not supported, and the Record group field must be added during the configuration of the mobile form.
Record name policy:
- Mixed case. The record name is stored exactly as typed by the user with regard to upper/lowercase.
- 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 consecutive 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 will be displayed as read-only in forms. Even though the Record name onfiguration can be activated at any time during the application lifetime, it is a good idea to set the correct configuration from the beginning of the table creation, in order to avoid inconsistencies (review notes for importing and editing records with this setting) in the application data.
Max record name length. In case the previous property was set as mixed case, uppercase or lowercase, the maximum length of the record name can be constrained in this text box.
Log changes. Tick this setting to register and show changes made to records (i.e., logs of records). Can be activated to store changes for a period of time. The changes are viewed in the Logged Data Search system form, or in application forms' fields via the Show history in tooltip checkbox. Note that this setting is applied per table.

Edit a table

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

To edit a table setting, select the table. Its settings specified in the Details pane can be changed at a subsequent time.

Changing the case in the Record name policy setting only affects new records created after saving this change; i.e., existing records will not be automatically converted to new selected case.

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

Export the records to Excel (via Reports or Quick Excel report).
In Excel, use the formula to convert the Record name to the wished case, e.g., =UPPER(A2)
Once 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:

The user will not be able to edit previous record names, since the Record name field will be automatically set as read-only.
The next new record will have the number specified in the Start name at box as Record name, regardless if the existing list of records have names as text, e.g. "John Smith". However, if the existing list of records contains a number as name, then the new record will be named with the next consecutive number of the highest number (either existing or to auto create). For instance:
- 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".
- 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 (its name, settings and fields), an asterisk (*) appears at the left of the table name. This provides a visual feedback, informing that there are pending changes that need to be saved. At this point, navigating away from the current form will discard the pending changes. Click on the Discard changes button to undo edition. Once the changes have been saved, they cannot be undone (except by manual editing).

Delete a table

A table can be deleted by selecting it and clicking the DeleteForm icon. Then save.

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

The table does not contain records. All records of this table must be manually deleted (e.g., by using an application form that displays records of this table) or by using a massive deletion import.
The table fields are not displayed in any application form. Any forms displaying the table or its fields must be deleted, or edited by removing all fields and selectors of this table.
The table has no fields. Any fields must be deleted first.
The table has no reports nor imports. Delete any of these items related to the table.

Relations between tables

A table can have relation with other tables, through fields of the type Relation. The table where the Relation field is created, is known as child table and is subordinated to (i.e., points to) another table, known as parent table or target table. This relationship is called as many-to-one, i.e., many children with 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.

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

This means that the definition of the relation will depend on the perspective of the table in consideration. For instance, having the relation B→A, where "B" is the child table and "A" 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 record points to many B records).

In IS Tools, the relation is always configured as a field of the child table, pointing to the parent table.