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.
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”.
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 (Fig1) or in shortform (Fig2) or only the first (Fig3) or last (Fig4):
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;