Links
Introduction
A link enables data records of two tables to be linked.
Limbas automatically creates an additional ‘Internal Link Table’ for 1:n and n:m links. The name of this ‘Internal Link Table’ is displayed in the table settings ([admin]-> [tables]) , in the ‘Addition’ field. The unique field ‘ID’ of the ‘Table A’ is linked with the field ‘ID’ of the ‘Internal Link Table’ and the field ‘VERKN_ID’ of the ‘Internal Link Table’ is linked with the unique field ‘ID’ of the ‘Table B’. In addition, table status information such as ‘created on’ ‘created by’ is stored.
Create a Link
To create a link, add a field of type “link 1:n” or “link m:n”. You can select the table you want to link in the link-editor.
To create a 1: 1 link, the table to be linked must be selected when creating a table.
Using the link editor ([Admin]->Any Table, see right), you can select the table you want to link.
The content of the new field with the type “Link 1:n” or “Link m:n” is in the background a numeric number which contains the number of the current links of the respective data record. The content is updated via a database driver when the link is modified (add or remove). This method has the advantage that the representation in the table list does not have to resolve each link individually, but can directly display the number of links and is therefore significantly faster. A possible inconsistency over time can be renewed manually using the system tools.
Manage Links
Additional settings for the link can be made, using the three tabs in the link editor.
General
Linked
Select a table field in the linked table. When the data record is displayed this field will represent the linked table in the Edit and Detail View. Chose a field with a relevant content .The field has no relevance for the ‘real link’ and can be changed at any time.
Searchable
Select any number of fields in the linked table. When a search is performed in the actual table, the contents of these fields will be checked, to find data records, where the link contains the search strings.
Search Result
Any number of fields can be selected from the table fields in the linked table. The result of a quick search, in the Edit View of a data record of the actual table, displays the contents of the selected fields in a drop down list, separated by ‘|’. A quick search is only possible when ‘ajax search’ is enabled for the link.
Displayed
Each user can individually select any number of table fields from the linked table, which are then displayed in the Edit and Detail View of the actual table. This setting is then used as default.
Seperator
The user has the option to specify 2 separators:
Referential Integrity
Referential integrity is a property of data which, when satisfied, requires every value of one column of a table to exist as a value of another attribute in a different (or the same) table. If referential integrity is enabled, a corresponding foreign key is created (see also Constraints). .
Link Parametrisation
By using link parametrisation, the user can add values to links. After activating the function and resetting, the user can create new columns in the automatically published table “tablename_params”. The Field Types are however limited.
These values cannot be exported and cannot be accessed through SOAP in this Limbas version.
Example:
Supposed there are the tables “Project” and “Employee”. A project consists of many employees, but an employee can also participate in different projects. You want to save the function of each employee depending on the project. This is possible because of link parametrisation:
After enabling the l. p. and resetting, the “internal link table” is published automatically by Limbas and is accessible under the name “Employee_params” (fig. 1-2).
First you have to create a new column in “Employee_params” (fig. 3).
You can activate and edit this column in detail view of a project’s employees (fig. 4).
The employee “Sepp Unterseer” can thus be project leader in project 1 and also just a worker in project 2 (fig. 5-6).
When looking at an employee in detail view, you obviously cant see his function because it is dependent on each project he is working in.
Hierarchical Link
Hierarchical links are cross-table links. As soon as such a link is possible, the option appears automatically in the link settings.
It is possible if the table to be linked is already linked to a field in the existing table.
It should be noted that fields that are linked in this way can only be read only read , but can otherwise be used as usual.
Example:
The “Project” and “Employees” tables used in the previous example are the starting point. In addition, there is now a table “Customers”. A customer is assigned one or more projects.
Now you would like to know which employees are responsible for a customer, ie which employees supervise the projects of the customer.
The procedure is similar to creating a normal link.
The desired link field “Employees” is created in customers and Then select the “EMPLOYEES” table in the link window.
In this window there is now a list of possible hierarchical links, For this example, a checkmark must be set for the following line:
PROJECTS–>EMPLOYEES
After the confirmation, the field which is to be linked is selected as usual, In this example “Name”.
Advanced Filters
(see also Example Link)
When creating a link to an existing data record, without an advanced filter, all data records of the linked table will be listed. To eliminate irrelevant results conditions can be set in the advanced filter. Where these conditions are fulfilled the data records will be listed. There are two possible ways:
- Set the variable corresponding to the SQL clause “$where []” and “$from []”. Click on the arrow to get a drop down list with all relevant tables for the current link where the links are displayed in pairs. By clicking on one of these pairs, a query is proposed.
- Set filter settings using the “$gsr”-Parameter (see the description of the function “get_gresult”).
- Set filter by using your own function “return myExt_function($gtabid,$utyp,$filter,$gsr,$verkn,$onlyfield=null,$single=null,$extension=null)”. By “return”ing, the standard function get_sqlquery() will be aborted and replaced by your own function. You are advised to call the original function to get to know the format of the data.
Generator
To generate links for tables, imported from an external database. First select from each of the 2 tables a table field. If the selected table fields have the same content, Limbas creates a link for these 2 data records. The generator automatically generates and fills the “Internal Link Table”.
Example:
Table CUSTOMER with a table field ‘KEY-ID‘ Table CONTACT with a table field ‘CUSTOMER-ID’ If CUSTOMER ‘KEY-ID’ is equal to CONTACT ‘CUSTOMER-ID’ the data records are linked.
After importing both tables, the table settings have to be adjusted. ([admin]-> [tables]->[edit table ]). In the table CUSTOMER, add a new table field with a name ‘Customer contact’ and chose the field type ‘Link 1:n’. Then select ‘CONTACTS’ as the ‘Linked Table’ for this field. Edit the new field, click on ‘LINK Set up’ and select the ‘Linked’ field. This newly created ‘Link‘ is still empty.
Use the tab ‘Generator‘ to select the table field ‘KEY-ID‘ (left selection list for CUSTOMER) and the table field ‘CUSTOMER-ID’ (right selection list for CONTACT). Click the ‘Create New Link‘ button. LIMBAS creates for each parity of the 2 fields a link.
The fields ‘KEY-ID‘ and ‘CUSTOMER-ID’ can be deleted, if they are not otherwise required. The process can be repeated arbitrarily. Previously existing link entries will be deleted. All links are entered in the associated ‘Internal Link Table‘ ‘VERKN_ *’, which is defined in the ‘Link Settings’ of the newly created link.
Back-Link
A Back-Link can show a link in both directions.
To make that possible, Limbas creates a “View Table”, where “ID” and “VERKN_ID” are swapped.
Example:
Supposed there is a link from field “CONTACTS” in the Customer-table to the Contact-table. In order to create a back-link, you have to create a new n:m-link in Contact-table linked to “CONTACTS” in Customer-table.
The back-link is independent of the type of the link. You can create links from n: m to n:m, as well as 1:n to n:m. A special case is the ‘Unique’ setting, which can change an n:m-link into a 1:n-link.
Linking the file management system to a table
It is possible to link the file management system (FMS) to a table in order to insert files (such as documents and images) into a table and display them there.
Add FMS link
Adding a FMS link to a table is done in the same way as adding a normal link to a table.
A new field with the type “Link” can be added under [Admin -> Tables -> “Table name”]. Depending on your requirements, you can choose between “1:n Link”, “1:n direct Link” or “n:m Link”. To link the table to the FMS, select [General -> Tables to be linked -> Limbassys -> Files(ldms_files)] in the link editor (see Fig. fms_1). A window now appears in which you must select which of the variables in the linked file represents the linked file in the edit and detail view. It makes sense to select a field with meaningful content, such as the name of the file. The field has no influence on the real link, but determines the content of the table field of the link. Nothing should be changed in the remaining variables. All variable settings can be changed at any time. Finally, click on “Apply” and close the window.
In addition, “extendedFileManager” must be selected in the Extension field. (If this is not done automatically).
Now there is a new link in the table with special functions and additional information and there is a new folder “Tables” in the FMS with a subfolder with the name of the table in which the link to the FMS is located. Files from other (already existing) folders can also be loaded/linked.
Functions of the FMS in a table
| Detailed view | Double-click on a file to open a detailed view of the file. General data, metadata, duplicates and dependencies are noted there. |
| mini file manager | A window opens with all folders and files that belong to this link. All fields can also be searched there. |
| Search data record | You can search/filter for a specific value in each field. |
| Create new folder | A new subfolder can be created. |
| Create new file | A file can be uploaded from the computer to LIMBAS. |
| Delete files/folders | Selected files and folders can be deleted. They are also irrevocably removed from the FMS. |
| Remove link | Selected files can be unlinked. The files are still available in the FMS, but they are no longer linked to the table and are therefore no longer visible in the table. |
| Show fields | You can select which fields are to be displayed. All fields that are displayed are marked with a tick. To add or remove a selection, click on the corresponding name. |
| Show all files | All files from each subfolder are displayed. |
| Simple display | Files and folders are displayed in a list. |
| Picture show | Files are displayed as small images. Subfolders and the files they contain are not displayed. |
| Picture gallery | The files are displayed individually as large images and you can view each file using arrows. Subfolders and the files they contain are not displayed. |
Display mode
| Dropdown (detailed view) | The type of display can be selected under [Admin -> Tables -> “Table name” -> Link settings -> Parameters -> “Display mode”]. The default display is the detailed display. |
| Minimized (minimized view) | The minimized display for the FMS can be selected in a form. |
Types of Links
1:n Link
The ‘1: n link’ is the most common type of link. Only ONE customer can be linked with several contacts. But a contact can be linked only to ONE customer Additional information such as ‘created on’ and ‘by whom’ are saved in a 3rd table, the “Internal Link Table”.
1:n direct Link
A direct 1:n link is identical to the 1:n link, but no additional link table is created. The join information is stored in tables A and B in one field. This simplifies SQL arguments, but additional information, such as the link was “created on” and “created by”, cannot be saved.
m:n Link
Several orders can be linked to several articles, an article can be linked to several orders.
This is only possible by defining a third table (which is called the “Internal Link Table”) whose primary key consists of two fields: The foreign keys from tables A and B. An “n:m link” actually consists of two 1:n-links with a third table.
1:1 Link
ONE customer can be linked with ONE contact, ONE contact can be linked with ONE customer.
This type of link is rarely used, because most of the information linked in this way, is within a single table. Limbas uses those links
Display
The display of a link can be changed in the table field settings.
Links can be displayed in different ways:
- Standard
In the standard display, the links are listed in detail in a table. Links can be extracted, searched, sorted and edited. If ‘Ajax search (Tables) is enabled, an input field is displayed and data records in the linked table can be quickly searched and linked.
- Selection Search
If ‘Selection search’ is enabled, the link is displayed as a simple list or as a select field if ‘unique’ is activated.
- Ajax Search
If ‘Ajax search’ is enabled in addition to ‘Selection search’, you can see all possible datasets by typing “*” into the ajax text field.
If “unique” is activated, only one dataset can be selected.
In addition you can select whether a linked dataset in list view should be displayed in detail or in shortform, or only the first or last dataset.
Parameters
If a link is displayed in a separate form, additional settings are possible via the context menu and the item “Parameter”:
Display defined fields (if rights are granted)
$filter["ext_RelationFields"][$vuniqueid] = array(10,7,15,13,14,16,19,23);Edit defined fields (if rights are granted)
$filter["ext_RelationFields"]["edit"][$vuniqueid] = array(16,19,23);Define Column width (in pixel)
$gfield[$vgtabid]["rowsize"][16] = 20;Limit Results (Search Parameter)
$gsr[$vgtabid][22][0] = $ID;
$filter["ext_RelationFields"]["searchval"][$vuniqueid] = $gsr;Sort
$filter["ext_RelationFields"]["order"][$vuniqueid][0] = array(4,13,"ASC");
$filter["ext_RelationFields"]["order"][$vuniqueid][1] = array($vgtabid,4,'DESC');Hide Functions
return array('no_search' => 1,'no_add' => 1,'no_edit' => 1,'no_delete' => 1,'no_link' => 1,'no_sort' => 1,'no_openlist' => 1,'no_fieldselect' => 1);Show all results
$filter["ext_RelationFields"]["showall"][$vuniqueid] = 1;Show content of ‘Long fields‘
$filter["ext_RelationFields"]["getlongval"][$vuniqueid] = 1;