Tables

Table Groups

Table groups give a structure enabling a better overview, especially with a large number of tables. In LIMBAS each table is located in a table group. A table group can be a subgroup of another group of the table. A deeper nesting is not implemented.

When calling this menu item, existing table groups are displayed also offering the possibility to add new table groups.

• “ID”:Unique identification for the table group, assigned by LIMBAS.
• By clicking on the ‘Edit’ icon, the tables of this table group are displayed. Settings of the tables can be changed or new tables created.
• Pos: To change the order off the displayed table groups, click on the up/down arrow to move the table group a position up or down.
• Delete: Click on the ‘Trash’ icon and confirm in the pop up window to delete the table group and all related tables.
• Subject: This text is displayed for the table group in the Menu. (Navigation Menu, left side of LIMBAS screen). It is the responsibility of the user to choose an appropriate, meaningful name.
• Title: Description of the table group.
• Subgroup of: This field can be set if the corresponding table group is a subgroup of another group of tables.
• Tables: This field shows the number of tables that belong to this table group.

Table Settings

To get to the table settings

• click on the ‘Edit’ icon of the corresponding table group in the list of table groups
• click on the appropriate table group in the admin menu on the left of the screen ([admin]-> [tables]-> [table group name])

The following table settings are displayed: Queries

• ID: Unique identification for the table, assigned by LIMBAS.
• By clicking on the ‘Edit’ icon in tables of the type (see below)
  • Table/calendar/news, a list of table fields is displayed, with the option to change their settings or to add more fields.
  • Query, the ‘Query Builder’ is displayed with the definition of the query (see Queries)
• By clicking on the ‘gear’ icon, a settings-window opens where you can change settings for the table.

• Pos: To change the order off the displayed tables, click on the up/down arrow to move the table a position up or down.
• Delete: Click on the ‘Trash’ icon and confirm in the pop up window to delete the table and all related data records.
• Table Name: An unique name of the table. If an existing table name is entered, LIMBAS adds a unique suffix.
• Subject: The Subject is displayed in the Navigation Pane. Here it can be selected to enable the viewing/input/change/deletion of data.
• 1:1 Link: This field shows whether the table is linked 1:1 to another table. Only one data record of the table (Table A) is linked to one data record to the other table (Table B).
• Type: Table Type:
  • Table: Table without predefined fields
  • Calendar: Table with predefined table fields for a calendar. The table can be accessed with the LIMBAS Calendar module. When you create the calendar table, a corresponding Menu item is added to the multipurpose window (Media Menu) on the right hand side of the browser.
  • News: Table of predefined table fields for messages. The table can be accessed with the LIMBAS News module. When you create the messages table, a corresponding Menu item is added to the multipurpose window (Media Menu) to the right hand side of the browser.
  • Report template: Table for text modules that can be included in a report (see Html-Templates).
  • Query: virtual table without fields (see Queries)
• Fields: This field shows the number of the table fields for this table.
• Color: Using an individual color to highlight tables in tabs or table headers.
• Log’: This option determines whether data changes will be logged in detail. If so, any change with old and new table field content is logged and can be displayed in the history of the user (corresponding log level of the user provided, see Log-Level).
• Lock: When this option is set, a data record from this table cannot be edited by multiple users simultaneously. If the data record is being edited, it is locked for other users. In the environment variable “inusetime” the period of the lock is defined. After the defined time period, in minutes, the data record is automatically checked to see whether it is still being edited or can be released. For performance reasons, this option should be used only if it is really necessary.
• Colorize: If this option is set, the background color of individual rows (data records) can be changed in the Record List. This setting is then valid for all users who belong to the same main group. For performance reasons, this option should only be used if it is really necessary. Setting a background color of individual columns (table fields) in the Record View is always possible and is set individually for each user.
• Rights: This option allows you to define the user rights individually for each data record. An additional internal table is created in LIMBAS, which has a negative influence on the performance if a large number of data records are handled. Another way to limit the authorization to access a data record is through Table Rights. For this option, additional permission settings are offered in the group rights for the corresponding table.
• Ajaxpost: Submission of the table occurs in the Record View through the ‘save’ button and in the Record List through the ‘Save’ command in the File Menu or ‘Enter’ key. This option attempts to immediately send all changes of individual fields of the form via AJAX and saves them without having to make an additional action. This function is very convenient but can create a higher workload for the server.
• Versioning: Setting allows enabling/disabling of the versioning of the table. The values differ only for tables with 1: n links ‘fix’ / ‘recursive’.
  • Fix: When the versioning of a data record is carried out, the ID of the associated record is also versioned. The table field contents of the associated data record can later change so that they no longer correspond to those at the time of the versioning.
  • Recursive: When versioning a data record the contents of the linked data record are also versioned.
• Links: Only used for linked tables.
  • Positive, negative, all: in the Record List linked data records from other tables can be displayed.
    • Positive: The actual table contains a link to another table
    • Negative: Another table contains a link to the actual table
  • This setting enables the user to display linked data records. To display them, the user can click on the ‘Group Selection’ icon (header line).A pop-up window appears with a selection of tables that are linked. After selecting the linked tables, by clicking on an arrow in the 1st column of a row, the linked records of the selected tables are shown, click again to hide them.
    • No: Linked data records cannot be displayed in the Record List.
• Indicator Rule: A function, as described in Indicators, can be assigned to a table. Note, that opening such a function is done from this script ./limbas_src/gtab/gtab.lib. For the (Transfer) parameter, ‘Variables’ from this script should be used.

A possible entry could be:

return indicatorRuleFct($gtabid,$i,$gresult);

• Trigger: This option is only available if there are one or more triggers for this table. (see Trigger). In this case, the trigger can be selected here, that is executed when a user of this group performs a corresponding action on the table.
• Result Number: This option affects how the number of found data records is calculated.
  • Standard: If the attempt ‘odbc_num_rows()’ fails, the number will be calculated by ‘select calculated count(*)’.
  • Reduced number of results: If the attempt ‘odbc_num_rows()’ fails, the number will be calculated by ‘odbc_fetch_row() loop’.
  • No calculation: If the attempt ‘odbc_num_rows()’ fails no calculation is performed. The number of the environment variable ‘resultspace’ will be used.
• Sort Function: As an administrator, the order of individual elements of a table can be easily changed by dragging and dropping.

Table Field Settings

To get to the table field settings

• For tables of the type table/calendar/message, click on the ‘Edit’ icon of the corresponding table in the list of tables
• For all tables and queries, click on the appropriate table in the admin menu on the left of the screen ([admin]-> [tables] -> [table group] -> [table name])

The following table field settings are displayed: (reduced options available for Queries):

• ID: Unique identification for the table field, assigned by LIMBAS. In functions or extensions, this ID is always used as ‘IdentID’ instead of the field name. Therefore, changing a field name has no effect on the operation of LIMBAS. You can partly influence the allocation of the ID (see Field Types)
• Edit:
  • Click on the ‘gear’ icon in the list of table fields, a pop-up window appears with the general table field settings, as well as the field type specific settings (for the moment except for the trigger). All changes made in the pop-up window are immediately active. Close the pop-up window with a click outside of it. To be able to see the changes in the table field list a refresh is needed.
  • Click on the ‘Trash’ icon and confirm in the pop up window to delete the table filed and the contents of it in all related data records.
• Field Name: A unique name of the table field. If an existing table field name is entered, LIMBAS adds a unique suffix.
• Subject: Hover with the mouse over the name of the table field in the Record View and this text appears, giving a short description.
• Title: A unique name of the table field. If an existing table field name is entered, LIMBAS adds a unique suffix.
• Type: Field type of this table field (see Field Types).
• Size: Size of the field. Depending on the database, different sizes can be used. Text fields are defined generally in number of digits. Number fields can be defined, depending on the field type, with pre and post decimal places. For example, ‘20.3’ for a decimal field means 17 digits before and 3 digits after the decimal point.
• Default Value: Default value of a table field when a new data record is created.
• Number Format: Display of numbers in the ‘number format()’ Format: E.g. 2,’,’,’.’. Alternatively, a custom function can be used by “return”. The function must also exist in an extension script like “ext_gtab.inc”. Please note that the field can only hold 30 characters.

Example:

2,',','.'.

return myExt_strPad($value);

• Power Treshold: When the size of the specified number exceeds this threshold the number is displayed as exponential value.
• Additional: Dependent on the field type (see Field Types) allows additional configuration of this table field.
• Convert: The presence of this option depends on the field type. It is used for the conversion of the field type. LIMBAS performs a type conversion with minimum data loss (ideally none) for existing data records.
• Extension: LIMBAS allows individual extensions(modifications) for the display of a table field (see Field Types).
• View Rule: PHP function (see Create Display / Edit Rule) with ‘true’ / ‘false’ result (don’t forget ‘return’ and ‘;’!). In the case of ‘true’, the table field is not displayed in the Record View.
• Edit Rule: PHP function (see Create Display / Edit Rule) with ‘true’ / ‘false’ result (don’t forget ‘return’ and ‘;’!). In the case of “true”, the content of the table field cannot be edited.
• Trigger: This option is only available if there are one or more triggers for this table. (see Trigger). In this case, the trigger can be selected here, and is executed when an appropriate action for the table field is performed.
• Identifier: This check box can be used only for one table field in the table. The content of this table field is then used to identify a data record, for versioning or the history.
• Index: Select this option to create a database index for this table field. This index leads to improve performance in finding or retrieving data.
• Unique: Select this option, when the content of this table field is unique and cannot be found in another data record. This setting affects also the appearance of links (see Links)
• Ajax Search: This option allows a ‘Quick search’ within some field types. In Edit mode an input field for the search string is displayed. Enter at least two characters or ‘*’. A pop-up window appears with a selection list. The entries displayed contain the entered characters. This setting affects also the appearance of links.(see Links)
• Ajax Post: When this option is enabled, after a new value is entered the modified table field is updated immediately in the database. The browser page does not have to be re-loaded. When the option is disabled, the table field contents are updated in the database by clicking the ‘Save’ button. Changes to field types, where this option is not available, will be updated in the database immediately after the entry. It has the same behavior as in the table settings, only that it relates to a single field. If the option is already enabled in the table settings, it will override the setting of this field.
• Selection Search: If this option is enabled, the input field for the search in the Record List is a SELECT field. In a drop down list, all existing data records for this field are shown for selection. This setting affects also the appearance of links. (see Links)
• Quick Search: When ‘SELECT search’ is enabled, this setting has no meaning. Otherwise it affects the search of table field content in the Record List (user view).

Enter 2 or more digits and a drop down list opens showing data records that contain the search string. With Shift and click on one of the entries the selected data record can be edited in Record View.

• Full-Text Search: For a full-text search, instead of just individual columns, the entire table can be searched.

Under Admin -> Table ->

individual columns of the table can be activated or deactivated for the full-text search.

• groupable: If the option is set, it is possible in the user view for the table list to display the data records sorted according to the contents of this table field. Records that have the same content for this table field are grouped together. In the user view, to the left of the first column header, an icon appears, to which the keyword “group selections” appears when the mouse pointer is left on for a short time. By clicking on this symbol, a selection of the groupable table fields is displayed. After making the selection and confirming with the Enter key, the display is updated accordingly.
• Expand Shortcut: Enables the display of linked records in the list view of the table, without this having to be selected by the user.
• Stack Change: If this option is selected, it is possible to edit this field in the table view via a batch change function.
• Agregate Functions: If the field is of type Number, several aggregate functions can be selected here. Available are SUM, AVG, Min, Max, COUNT. These calculations are displayed at the end of a table list of the respective field and can be activated via the menu item View-> Show total. The calculation refers to all found records without consideration of the Resultcaches-Limits.

Create Table

A new table can be created inside a folder (in the demo for example the folder CRM). On click on a folder the already existing tables can be seen as well as a creation mask for a new table.

Here the following data can be inserted and after wards a new table can be created by “add”.

• table name
• description of the table
• a possible 1:1 link
• a table that shall be copied to the new table
• the type of the table that shall be created (description below)
• various flages: create permissions, user serial id, user sequence table. The last one is selected per default.
• Type: When creating a new table, the following table types are available:
  • Table: Table without predefined fields
  • Calendar: Table with predefined table fields for a calender. The table can be accessed with the LIMBAS Calendar module. When you create the calendar table, a corresponding Menu item is added to the multipurpose window (Media Menu) on the right hand side of the browser.
  • Kanban: Creates a new Kanban process. After creating, it appears in the right multi-purpose window.
  • News: Table of predefined table fields for messages. The table can be accessed with the LIMBAS News module. When you create the messages table, a corresponding Menu item is added to the multipurpose window (Media Menu) to the right hand side of the browser.
  • Query: virtual table without fields (see Queries)

Options

• Create Permissions: If this option is enabled, all entries in the LIMBAS system table for table rights management of a newly created table field, are added. For performance reasons, you might want to do this subsequently if it is for a large number of user groups (see tables and field rights to match). In the above case, disable the option.
• Use[serial]id: The primary key ID field is created as an auto increment field, otherwise, it is defined as an 18-digit number field.This function can be useful to insert queries.
• Use [sequence] table: To calculate the value for the primary key ID field, the sequence functionality of the database is used, where supported. This setting is not affected if the ID was created as a serial field. This option is currently set, using the setting in the database control files (lib/db/db_datenbank.lib) as the constant [LMB_DBFUNC_SEQUENCE].

With the ODBC driver it is possible to create an auto id field but when new records are created, their primary ids are not returned at the same time. Limbas gives the choice between two options for setting the next ID (primary key):

• max(ID)+1: Before the creation of a record, the next highest ID is queried (max(ID)+1). As this procedure is implemented using transactions, concurrency issues leading to the same IDs of different records do not occur.
• Sequence tables: Before the creation of a record, the next highest ID is queried using a sequence table. Sequence tables can be generated using the system tools. If new records are inserted via import or direct ODBC connection, the sequence table has to be updated.