Field types

All fieldtypes of LIMBAS are stored here to enable an easy and clear overview.

If admin_mode is enabled, modifications of the fieldtypes are stored in the core instead of an extension. If an item in the core is overwritten by an extension, it is highlighted in color.

LIMBAS stores the fieldtypes in the tables ‘lmb_field_types’ and ‘lmb_field_types_depend’.

LIMBAS Predefined Field types

Standard Field types

Numbers

Number

Number, no decimal point, can be positive or negative, max size depends on data base.

Number with floating point

Number with decimal point, Database depended accuracy and size. LIMBAS is trying to hide the rounding error and thus simulate an exact accuracy. A sufficient field size is a prerequisite. Applies to MAXDB: ‘precision p (0<p<=38)’. This field type is the type ‘FLOAT or REAL’. This field type is well suited for calculations because of its speed. Because of the inaccuracy it is not really suitable for currencies.

Number with decimal point

There is no rounding with this field type. If the content of the field cannot be saved with the required accuracy, the least significant digits will be deleted. Applies to MAXDB: ‘fixed point number (p,s)’. This field type corresponds with the type ‘FIXED, NUMERIC or DECIMAL’. An exact number of digits before and after the decimal point can be defined. E.g. the size [ 10,3 ] specifies a field with 10 digits, 3 of the 10 are after the decimal point. (7 digits before the decimal point and 3 digits after the decimal point).

Percent – Number with comma

Is the same as number but with a decimal point. Percent sign will be displayed.

Numbers General

Formatting

LIMBAS uses the PHP function ‘number_format’ to format numeric field types (see number_format). The parameters for ‘number_format’ are set in Table field settings. The parameters are entered as they are transmitted to the function.

Exponential format

If a number cannot be displayed with the number of decimal places registered under ‘Power threshold’, it appears in the Exponential format (scientific notation). The described formatting is not considered in this case. The display and enter is made for e.g. in ‘1. 58e-11’ format for forms and in the ‘15, 8-11’ format for the Record View. (see Table field settings)

Numeric uncertainty

The data type ‘NUMERIC’ behaves differently in different databases. PostgreSQL numeric fields are rounded to integers which are defined without decimal places (e.g. numeric, 10). To use decimal places they must be defined (e.g. 10.3). MaxDB however, displays a number with no decimal places (e.g. DECIMAL 10) as a dynamic point number.

Float problem

The data type ‘FLOAT’ behaves differently in different databases. PostgreSQL rounds 4 or 8 bytes (FOAT4 / FLOAT8) large ‘Float’ numbers with greater or lesser accuracy. MaxDB defines the field type FLOAT ‘Floating Point Number’ with selectable precision p (0 <p <= 38). In a migration of both databases this should be considered.

Text

Single line text with up to 250 characters. The display is a line of text. The entry of additional characters will be blocked.

Textblock

Block of text with up to 2000 characters. It is prepared as a block of text. If you enter more than 2000 characters it will be truncated without warning. Line break consists of 2 characters. Line feed and carriage return (chr(13) and chr(10)).

Long

Block of text with any number of characters. Unlimited otherwise as text block. By selecting the option WYSIWYG text can be formatted. The indexing option has an impact on the search function in the Record List. (search for contents of a field):

• Indexing option not enabled – whether a relevant search is possible depends on the database you are using. (PostgreSQL+, MAXDb-, MSSQL-)
• Indexing option enabled – a full-text indexing for the contents of the table field is performed. This enables the full text search for all data bases and improves the performance of a search. See also system jobs.

The Long Field is of type ‘TEXT, BLOCK or LONG‘. This field type is using less resources in the table definition (approximately 6 byte), but because of its performance and special treatment or restrictions with most data bases its use has to be considered carefully.

Time / Date / Date_Time

The formatting of the Time-Field type depends on the User Settings in the field ‘Date Format’. (see [admin]->[User/Groups]->[General Settings])

Data entered in the edit view must be made in this format. If a different format is used, LIMBAS tries to convert the data to the correct format. You can only enter plausible data otherwise LIMBAS will block the entry (e.g. 14.23.2012 13:91:00 will be blocked)

The format of the ‘Time Field’ type for Record List and Record View can be set individually. Define the Format in table settings ([admin]->[Tabellen]), in the field ‘Addition’.

The formats that can be used and the range of values depends on the value of the Environment Variable “use_datetimeclass” ([admin]->[Setup]->[envvar]):

use_datetimeclass = 0:

Format information according to the PHP function ‘strftime’ (see also strftime).

The valid range for Time/Date is Friday 13.12.1901 20:45:54 GMT until Tuesday 19.01.2038 03:14:07 GMT. Windows operations system limits this range to 01.01.1970 untill 19.01.2038

use_datetimeclass=1:

Format information according to the PHP function ‘DateTime’ (see datetime_format)

The range of values includes all data with 4-digit year number (i.e. the year 1 has to be entered with 0001).

Boolean

Field type for TRUE/FALSE or YES/NO – information. Will be displayed as a tick and entered via checkbox .

Special Fieldtypes

Auto-ID

Allows positive integer numbers with up to 18 decimal places to uniquely identify a record. The value is generated by LIMBAS and cannot be changed by the user.

Post-User / Edit-User

Name of the user that created the data record or last modified it. The value is assigned by LIMBAS and cannot be changed by the user.

Post-Date / Edit-Date

Date of data record creation or last modification. The value is assigned by LIMBAS and cannot be changed by the user.

User/Group-List

With the “User / group list” Limbas users and groups can be selected.

In the list view, the search field can be searched for both the user / group name and the ID. The search can be limited to users or groups by the prefix #u# or #g#.

Currency

Combination of Number and Currency. (Depending on the data base it is either a number with a decimal point or a number with floating point and the currency information).

In the field ‘Addition’ ,the format of the amount can be defined like a numeric field type. In Edit Mode you can select the currency from a drop down list. The currencies for selection are defined in the LIMBAS System table ‘lmb_currency’. Changing the currency converts also the amount. The default currency is determined in the table settings ([admin]->[table]) field ‘Addition’ and will be applied, if no currency is entered.

Inconsistency: The behavior of the data type ‘FLOAT’ is varying in different databases.
Approach: PostgreSQL rounds the 4 or 8 byte (FLOAT4 / FLOAT8) floating decimal point number with greater or lesser accuracy. For currency fields this field type is useless. MaxDB however, defines the field type FLOAT ‘Floating Point Number’ with selectable precision p (0<p<=38). The migration of both databases is therefore for this field type problematic.. For PostgreSQL choose the field type ‘Numeric’ instead of ‘FLOAT’ for currency fields. This is the default- setting and can be adjusted in the LIMBAS – ADMIN Functions (Setup –> Field types).

Color Selection

Select a color as an RGB value or, in Edit Mode through a color palette.

Telephony

Text field for telephone numbers in international format. Invalid characters, as well as an invalid format will be rejected by LIMBAS.

File Size

Whole number (no decimal point) for saving a file size in bytes. The input can be customary units like KB, MB, GB, or TB and they will be converted to be saved in the data base.

Mime Type

The ‘Mimetype’ can be selected in the editing view from a select-field, the entries of which can be defined in the Mimetype-Editor.

URL

Single line text with up to max 230 characters for an URL. In Edit Mode, if a URL is displayed, the web page will be opened with a single click on the arrow next to it (if it is valid). If there is no absolute URL entered, LIMBAS tries the URL relative to the one in the current Browser Window.

email

Single line text with up to max 128 characters. In Edit Mode, single click on the arrow next to the e-mail address and the e-mail client of the user will open to create an e-mail to be sent to the displayed e-mail address.

Selection

LIMBAS keeps the entries which can be chosen from, in Selection-Pools. New pools can be created, unused pools can be deleted. Existing pools can be modified (change, add, delete entries). Each pool can have one or several default values, which are used when a new data record is created.

The pool used for a ‘Select’ is linked to the data field in the table settings ([admin]->[table]) field ‘Addition’.

There are selections that allow the user to select multiple entries (multiselect, checkbox, ajax) and there are selections that only allow one entry to be selected (Radio, Select and multiselect, checkbox, ajax with enabled unique-option).

In list-view, all selected entries are shown as text. In the table-field-settings the user can select whether to display the entries in short form (fig. 1) or detailed (fig. 2). When clicking on the black arrow, a list of all selected entries appears (fig. 3). See: Table field settings

In Detailview of a Dataset, each entry is shown as text. If more than one entry is selected, all selected entries are shown in a list.

Selection (Radio)

Only one value can be selected. The associated pool should have one default-value.

In editmode, a value can be selected in a dropdownmenu. In detail-editmode an entry can be chosen through radiobuttons.

Selection (single)

Only one value can be selected. The associated pool should have one default-value.

If Ajax-search is disabled, you can select from a drop down list, by clicking on the arrow next to the input field.

If Ajax Search is activated, a line of text appears in the edit view for “Select”: After entering at least two characters or “*”, a list with the selections whose name contains the entered characters works , on.

Selection (multiselect)

Multiple entries can be selected.

In Edit Mode, a window can be opened where you can select entries. In detail-editmode, you can chose entries by clicking in the dropdownmenu. The selected values will be highlighted with a different color.

Selection (checkbox)

Multiple entries can be selected.

In Edit Mode, a window can be opened where you can select entries. In detail-editmode, you can select entries by using checkboxes.

Selection (ajax)

Multiple entries can be selected.

In Edit Mode, a window can be opened where you can select entries. In detail-editmode, a textfield is shown for selecting entries. You can enter either 2 characters or ‘*’ to open a drop down list for selection, which contains the characters entered (in case of ‘*’ all entries are displayed). Selected entries are shown underneath the textfield.

Attribute

The ‘Attribute‘ is an extension to a selection. The user can add additional information to the selected entries. This additional information can be type ‘TEXT’, ‘INT’, ‘FLOAT’, ‘DATE’ (starting 1.1.1902) or ‘BOOLEAN.’

As for the selection, LIMBAS creates a pool with the additional information. These Attribute Pools can be edited as any other pool. Create new pools or delete unused ones. Existing Attribute Pools can be modified, entries can be added or deleted. Each Attribute Pool can have one or multiple default values which will be used for newly created data records.

The pool used for an attribute is defined in the table settings ([admin]->[table]) in the field ‘Addition’.

To select an attribute in Edit Mode enter either 2 characters or ‘*’ to open a drop down list for selection, which will contain the characters entered (in case of ‘*’ all entries are displayed.) Select an attribute with a single click and it will be added. The new attribute is displayed below the input field. There are 2 columns. The left column to give the attribute a name, the right column for additional information. As soon as the attribute is saved, you can add the additional information. You will also see the ‘Trash’ icon next to the attribute name (on the left) to delete an attribute. Single click on the ‘Trash’ icon and the color of the attribute name changes to red. The data record has to be saved, only then will the attribute be deleted. The Record View shows the selected entries with the additional information.

In the Record List the number of the entries is displayed, with the option to expand a list of the entries.

Upload

Upload files from your file system and create a copy in the database of your LIMBAS application.

In Edit Mode, you can choose the amount of files (default = 1, max = 10) you want to upload at once. A corresponding number of input fields will be displayed, each of them with a ‘Browse’ button. Enter the file name (with path) or use the ‘Browse’ button to select a file.

After the data record is saved, the copy of the files are in your LIMBAS data base and will still be available, in the current version, when the file is modified or deleted in the file system of your host. The uploaded files, filename, file date and size are displayed below the input field for uploads. Click on the filename to open the file in your browser. With a click on the arrow next to the file name file properties and Meta data are displayed.

More than 10 files can only be uploaded in several steps. The upper limit is given by the available free space in the database.

Record View displays a list of the uploaded files. In the Record List the amount of the uploaded files is displayed.

Arguments

Field types can also be extended in Limbas with SQL or PHP arguments. In the demo application you can have a closer look at their usage under tutorial examples.

PHP-Argument

‘PHP argument‘ allows to store the result of a ‘PHP expression’ in the data base. When creating a table element with the type ‘PHP argument‘ the result must also be of the field type ‘PHP argument‘ (default Text 50).

The ‘PHP expression’ to be evaluated, embedded in a return () command, is specified in the table settings ([admin]->[table]) field ‘Addition’. Environment Variables and entries from other table fields can be included in the PHP expression. The correct syntax and the correct result type must be ensured by the user.

In the table settings of the field types, there is the argument 32 under ID 32, which expects a PHP argument as type. Here also works a click on the icon (see right). A window opens up, where you can enter a PHP argument.

In the window shown, you will see two selection boxes that allow you to insert environment variables and fields into the PHP argument. In the example the username, the creation date and the number are inserted from which for example the following sentence is created: Hello admin! The data set was created on 11.09.2014. The following amount is to be paid: 23 €

In Edit Mode, Record View and Record List, the calculated value is displayed, depending on the result field type, either as a ‘Checkbox’, a ‘Text Field’ or ‘Text Block’.

Parameter are:

• $ID (Data ID)
• $gresult (resultset Array)
• $gtabid(Tabelle ID)
• $fieldid ( Field ID)
• $bzm(Tabellestring key)

Addition

If the table settings ([admin]->[table]) field ‘Addition’, specify that the field can be edited, the calculated value can be modified. Saving the data record, overwrites the calculated value with the value entered, as long as the entered value corresponds with the ‘PHP result’ field type. If the input field is blank when the data record is saved, a new evaluation of the ‘PHP expression’ is executed.

Encrypted

The content of a field of type encrypted is encrypted in the database and is not displayed clearly legible. Different types, such as text or number, can be selected for the content of the field.

Example

Output the field with ID 5 where $ gtabid is the current table ID and $ bzm is the current line. For a form representation of a record, $ bzm = 0

return $gresult[$gtabid][5][$bzm];

Output of my own function

fuction myExt_getHelloName($ID,$gtabid,$fieldid,$bzm,$gresult){
   return 'hallo '.$gresult[$gtabid][5][$bzm];
}

return myExt_getHello();

SQL-Argument

‘SQL argument‘ allows you to integrate the result of a user-defined ‘SQL query’ in a table. When creating the ‘SQL argument‘, the field type of the result of the ‘SQL query’ must be specified (Default, Text 50). The ‘SQL query’ is defined in the table settings ([admin]->[table]) field ‘Addition’. The required keyword ‘SELECT’ is implicitly added by LIMBAS, and therefore cannot be entered manually. When using ‘SQL functions’, it must be ensured that they are supported by the database used. If a possible result of a query is an empty string or the value NULL, the behavior of the database has to be verified. The value determined cannot be modified by the user.

According to the PHP argument, in the table settings of the field types under ID 31 there is the argument1, which expects a SQL argument as type. Click on the icon (see right). A window opens in which an SQL argument can be entered.

The depicted window shows a simple example where a number is multiplied by 2. Another example is the usage of CASE arguments where the shipping fee depends on the number:

CASE WHEN number < 20 THEN 'shipping 4,95€'
     WHEN number > 20 and number < 50 THEN 'shipping 2,95€'
     ELSE 'no shipping'

Example

This example shows how to calculate the mean of three numbers using PHP or SQL. The following table shows the average value calculated with PHP, then the three numbers, and then the mean value calculated with SQL:

PHP-Argument calls the function “Mittelwert” (Average).

This function is located in the extensions in a file called ext_gtab.inc (see also Extend_Script).

The file could look like this:

<?php
function Mittelwert($v1, $v2, $v3){
    return round(($v1 + $v2 + $v3) / 3,2);
}
?> 

The average can also be calculated directly using SQL:

Inherited

The field type ‘Inherited’ (see example inheritance), supports the user , by taking (inheriting) data from another data record , when entering data content. The user nevertheless has the possibility to enter other values, that don’t match any record of the table inherited.

When you create an “Inherited” table field, the table and the table field from which you want to inherit should be specified. This results in the actual field type. Not all fields can be used in a meaningful way. This is the responsibility of the user.

If you click on the ‘Edit‘ icon of an ‘inherited‘ table field you can assign it to an ‘Inherit’ –group. Depending on the actual field type the option ‘search field’ is available as well to set a ‘Filter’.

Record View – Edit mode with ‘search field‘ option enabled. ‘Inherit’ field: Enter one or more digits. A drop down list is displayed, with data from the ‘Inherit from‘ field, containing the characters entered and the entered search criteria. Click on one of these values and it will be ‘adopted’ in the field. If there are more fields in the table, which inherit fields from the same table and ‘Inherit Group’, these are also filled with the appropriate data from the associated data record.

The ‘Filter‘ settings are set by the “$gsr”-Parameters (see function description “get_gresult”).

If ‘search field’ option is disabled the ‘Filter’ settings are irrelevant.

Links

see Links

Extension-Field types

Version Remarks

This field type is only available in versioned tables.

When versioning a data record, a remark (reason for a new version, or version number) can be added. If there are one or more fields of the field type ‘Version Remarks’ in the versioned data record, the entered remark will be added automatically in this field in the new version of the data record.

Internally LIMBAS adds the column ‘vdesc’ to all versioned tables. All table fields of the type ‘Version Remarks will be mapped to this column. Please note, if a data record has several table fields of the type ‘Version Remarks’, they will all have the same value.

Document Content

The field type ‘Document Content’ is only a place holder and has no data. The use makes only sense in tables with indexed files. The file type enables LIMBAS to identify files that contain a keyword in the Record List.

Grouping

Grouping has only a place holder function and has no data. It allows to group table fields for the Record View. The groups are not visible in the Record List.

The table fields belonging to the same group are selected in the table settings ([admin]->[table]) field ‘Addition’. You also define the position of the table field within the group.

able fields that belong to a group, will be displayed in this group, even if they are then placed in a different ‘Division‘.

Assigning table fields to several groups is indeed possible but not useful. A corresponding table field is shown in this case in both groups. If different values are entered and the data record is saved it might lead to an unintended result.

A nesting of groups is not possible.

Grouping Tab

The table fields of a ‘Grouping Tab‘ are selected through a Tab (rather than displayed vertically). Therefore, in the Record View you can see only one table field of this type of group.

Grouping Row

The table fields of a ‘Grouping Row‘ are displayed horizontally, not vertically.

Division

The field typ ‘Division’ has only a place holder function and has no data. It is not visible in the Record List. In the Record View the ‘Division’ is used for an optical breakdown and structuring of the data record. There are 2 ways the data record is displayed:

– Sequential display of all table fields

The order of listing the table fields corresponds to the order in the table settings ([admin]->[table]). For the table field ‘Division’, the contents of the field ‘Subject’ are displayed over the entire width of the list. The text is centered and highlighted.

– Display of the table fields in Tabs.

The ‘Divisions’ are displayed as Tabs. The text displayed on the Tab is the content of the field ‘subject’, field type ‘Division’ (table settings ([admin]->[table])).

The grouping of the table fields follows the order in the table settings ([admin]->[table]). All table fields before the first table field of ‘Division’ type are assigned to each Division, so are visible in each tab. The table fields, which follow a table field of ‘Division’ type, are assigned only to this division, for as long as another table field of the ‘Division’ type is defined. These table fields are only visible in the Record View (user view), when the tab of this division is selected.

Tags

Tags are optional additions to fields of the type attribute. The best way to explain tags is with examples.

Creating Tag-Pools

Create a new field of the type attribute for the table “Kunden” (“admin->Tabellen->Beispiel-CRM->Kunden”).

Diesem Feld sollen nun Tags hinzugefügt werden. Selbstverständlich können Sie Tags auch auf bestehende Felder anwenden. Bearbeiten Sie die Feldoptionen indem Sie auf das Stift-Symbol klicken. Klicken Sie auf den Reiter “Attribut” im Kontextmenü und erstellen Sie einen neuen Auswahl-Pool der bestimmte Tags enthalten soll.

Now we add tags to this field. You can also apply tags to already existing fields, of course. Edit the field settings by clicking on the pen symbol. Click on the tab attribute in the context menu and create a new selection-pool that will contain certain tags.

Select the newly created selection-pool.

Activate the “tag-mode” in the context menu. Selection-pools with active “tag-mode” will be referred to as tag-pools in this article. The function “multiple-mode” allows using the same tag on the same record multiple times. Now you can create the tags in the tag-pool. Specify the type of the tag-attribute under attribute (e.g. “text” or “int”, select “ohne”/ “none” if the tag shouldn’t contain an attribute).

After creating a tag, the colours and other functions can be set. Here’s an overview of the functions:

• Default: Newly created records are automatically assigned this default-tag
• Pflicht/ mandatory: The attribute of this tag must be filled in
• Ausblenden/ hide: The attribute of this tag isn’t displayed
• Inaktiv/ inactive: The tag can’t be used as long as it’s inactive

All created tag-pools can be reused globally, in other fields and tables, just like normal selection-pools.

Usage of Tags

A reset is needed before using the new tags. Afterwards, navigate to ‘Arbeitsplatz->Tabellen->Beispiel-CRM->Kunden’ and double-click on any entry. Then, navigate to the form-symbol and select the “Standard-Formular”/ “Default-Form” in the context menu.

In the default-form, you can now select the entry’s tags (in the field “TAG-FELD”) from the tag-pool “tag_pool” and specify their attributes (if available).

Here’s a possible selection of tags:

After the tags are set, they are displayed in the “Kunden”-table in the field “TAG-FELD”.