Modules Links Estimated reading: 13 minutes 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 Fig.: link editor 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. Fig.: You can search for the first name, even though only the last name is displayed 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. Fig.: The name and first name are displayed 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. Fig.: The Ajax search displays the name and first name Seperator The user has the option to specify 2 separators: Fig.: The first separator separates the values that appear in the quick search. Fig.: The second separator separates the values displayed in the table view. 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. Fig.: 1 Fig.: 2 Fig.: 3 Fig.: 4 Fig.: 5 Fig.: 6 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. Fig. fms_1 Fig. fms_2 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 Fig. functions of the FMS Detailed viewDouble-click on a file to open a detailed view of the file. General data, metadata, duplicates and dependencies are noted there.mini file managerA window opens with all folders and files that belong to this link. All fields can also be searched there.Search data recordYou can search/filter for a specific value in each field.Create new folderA new subfolder can be created.Create new fileA file can be uploaded from the computer to LIMBAS.Delete files/foldersSelected files and folders can be deleted. They are also irrevocably removed from the FMS.Remove linkSelected 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 fieldsYou 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 filesAll files from each subfolder are displayed.Simple displayFiles and folders are displayed in a list.Picture showFiles are displayed as small images. Subfolders and the files they contain are not displayed.Picture galleryThe 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”. 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 Fig.: table field settings Links can be displayed in different ways: Standard Fig.: standard-view Fig.: standard-view unique 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 Fig.: Selection Search Fig.: selection-search unique If ‘Selection search’ is enabled, the link is displayed as a simple list or as a select field if ‘unique’ is activated. Ajax Search Fig.: Ajax Search Fig.: Ajax Search unique 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): Fig.: 1 Fig.: 2 Fig.: 3 Fig.: 4 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;
