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). Selected it under the Tables tab. 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 
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:
-
General
-
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.
-
-
Classic forms
-
Default form. table can be given a default form. When this option is selected, an icon will be placed next to Relation fields that point to the table in question in classic application forms. Also, reports (configured with the Enable links setting and generated in HTML) will show Record names and Relation fields as links. Clicking this icon or link, causes the default form to open, usually in a new browser pop-up window or tab (read about the Open links in same window setting in Form Builder for classic forms).
-
Style field. Only drop-down fields are displayed in this setting box. You may select one of them to change the appearance of the record name displayed in the Record selector and within Relation fields in application forms, but note that the desired style needs to be previously defined (in the Application stylesheets form) for a specific value of the Drop-down in consideration, in order to see the effects of this setting. For instance, if an objectStyleXY with Green color as Background, is defined for the value "London" of the "City" drop-down field, and then the Style field setting of the table is set to "City", the effect will be that every time a record of this table is saved with "London" set in "City", the record name background will change to green.
-
Style field. Only drop-down fields are displayed in this setting box. You may select one of them to change the appearance of the record name displayed in the Record selector and within Relation fields in application forms, but note that the desired style needs to be previously defined (in the Application stylesheets form) for a specific value of the Drop-down in consideration, in order to see the effects of this setting. For instance, if an objectStyleXY with Green color as Background, is defined for the value "London" of the "City" drop-down field, and then the Style field setting of the table is set to "City", the effect will be that every time a record of this table is saved with "London" set in "City", the record name background will change to green.
-
Show field in record selector. Valid only for classic forms, i.e., for forms built with Form builder (for forms built with Form designer, see Display field values in the Record Selector). Normally, lists of records of this table will identify each record by its Name. However, it is possible to display, in addition to the Name, the value of a Text field of the same record, in order to better identify the record to application users. For instance, a list of Contact records may identify each record by an automatically created number, and to make it easier for an application user to identify each Contact in a list, it is reasonable to display, in addition to this number, the full personal name of the Contact, as stored in a text field called Full name, e.g., "10034-John Smith". This setting can be used in combination with the Use "show field value" filter functionality of the Form builder.
-
Before record name. If this checkbox is ticked, the value of the field selected in Show field value in record selector (see above) is displayed at the left of the record Name. The default choice is to display the field value to the right of the record Name
-
Check rights. This checkbox is ticked by default. Unticking it makes the table not available to choose as Main table in Form builder, meaning that no classic application form can be created with the current table’s records accessible through the Record selector of the form. Instead, such records will be only accessible through relations with parent tables, specifically in Master-detail and Multi record matrix forms, where the Main table is the parent table, and the Detail/List sections show the current table’s records.
The Check rights setting does not work retroactively; i.e., classic application forms where the current table is the Main table created before this setting was unticked will not stop users from accessing this table’s records through such forms, causing unexpected results. To avoid this, we recommend you to either:
- manually delete those existing forms or
- remove their Insert and Update rights in order to concentrate the creation and edition of this table through the Master-detail form and/or edition through the Multi record matrix. -
Sort ascending. Valid only for classic forms, i.e., for forms built with Form builder. This setting selects the sorting order of lists of records in forms. It affects the sorting order of the record list in the drop-down list of the box on the title bar of application forms, as well as the vertical record list alternatively displayed in the body of the form. This setting does not affect the sorting order of other list of records in the body of the form, e.g., Multiple record view or record search forms.
-
Click the Save button after changes made on this form.
After this, you can proceed to create fields for this table.
Click on the  "Open table records" icon located in the Tables section in order to open a form containing all records and fields of the selected table. This is useful when, for some reason, you need to quickly look at (or edit or delete) certain records of a table, without having to open an application form, or run a report. Note that this form is similar to a table section, where filters can be applied, either by column or via the record filter, to be able to find the desired records.
|
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.
|
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 
icon. Then save.
|
A table can be successfully deleted only if all the following conditions are met:
|
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 direct.
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 direct 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. Read more about:
|
|
Take a look at the graphical representation of the tables and their relations under the Diagram tab. |