Lookup Table

Lookup Table is used for lookup field with Text (Auto-Suggest), Radio, and Select Edit Tags. To enable, check Use lookup table under Edit Tag panel after selecting one of the Edit Tags.

Table name	Required. The lookup table to be linked to.
Link field	Required. The field to be used as the value of an option. Note that this is the actual value to be submitted by the form This field is usually the key field of the lookup table.
Display field #1	Required. The field in lookup table to be used as the label of an option.
Display field #2	Optional. The 2nd field in lookup table to be included in the label.
Display field #3	Optional. The 3rd field in lookup table to be included in the label.
Display field #4	Optional. The 4th field in lookup table to be included in the label.
Order By	Optional. Specify a field in the lookup table for sorting the options.
Asc/Desc	Optional. Sorting order. For use with Order By.
Distinct	Optional. Specify adding DISTINCT option to the SELECT statement for the lookup table.
Filter	Optional. Specify the WHERE clause of the SELECT statement for the lookup table. The input should be a valid PHP expression. If it is a string, it should be quoted. If your lookup table has a special field (e.g. named "ALookupTableField") for filtering the records by a field (e.g. named "AField") in the current record (of the current table), you can enter: (strval($this->AField->CurrentValue) != "") ? "`ALookupTableField` = " . $this->AField->CurrentValue : "" Notes Make sure your expression returns a valid string. If some variables in the expression has empty values, the expression will return an incomplete WHERE clause leading to no records returned from the lookup table. So you should always check if the variables has non empty values first. If empty, return an empty string (i.e. no filter) as in above example. If the field in the WHERE clause is of string type, remember to single-quote it. For example, if ALookupTableField in above example is of VARCHAR type, you need to quote the value by single quotes, e.g. a. if the value is fixed, "`ALookupTableField` = 'SomeValue'" b. if the value contains characters that need to be escaped, "`ALookupTableField` = " . QuotedValue("Someone's Value", DATATYPE_STRING) This setting is used in all pages. If you want to used in some pages only, you should add your conditions, e.g. if you just want to use your filter in the Edit page, (CurrentPageID() == "edit") ? "`ALookupTableField` = 'SomeValue'" : ""
Parent field #1	Optional. For use with dynamic selection lists. Specify the parent field (in the current table) for the current selection list. When the parent selection list is changed, the available options in current selection list will be changed accordingly. Each field can have up to 4 parent fields. Note Parent field is solely used with Filter field for dynamic selection lists only. Each Parent field MUST have a corresponding Filter field. The Parent field alone does NOT do any filtering.
Filter field #1	Optional. For use with dynamic selection lists. Specify the filter field (in the lookup table) for filtering. When the parent selection list changes, only options (records from the lookup table) with Filter field value matching the selected value(s) of its corresponding Parent field will be shown. Note Filter field is solely used with Parent field for dynamic selection lists only. Each Filter field MUST have a corresponding Parent field. The Filter field alone does NOT do any filtering.
Parent/Filter #2	Optional. For use with dynamic selection lists. The 2nd pair of parent field and filter field. If setup, the filtering of lookup table records will be based on 2 fields. For example, if you have set up Parent/Filter field #1 and Parent/Filter field #2, and both Parent field #1 and Parent field #2 have selected value, the records will be filtered by: (Filter field #1 value = Parent field #1 selected value) AND (Filter field #2 value = Parent field #2 selected value) Note If either parent field is not selected, above filter will lead to no results, the field will not have any options.
Parent/Filter #3	Optional. For use with dynamic selection lists. The 3rd pair of parent field and filter field. If setup, the filtering of lookup table records will be based on 3 fields.
Parent/Filter #4	Optional. For use with dynamic selection lists. The 4th pair of parent field and filter field. If setup, the filtering of lookup table records will be based on 4 fields.
Allow add	Optional. If enabled, the user will be allowed to add an option to the selection list. Limitations Review your lookup table design before using this option. The option works best if you there is only one display field and the link field (primary key) is an autoincrement field. In that case the user only need to fill in a textbox and the option is added. But if the link field (primary key) is not an auto-increment field, the user will need to enter the link field value which the user may not know. The user will be asked to enter the link field and the display field(s) only. If the lookup table has other NOT NULL fields other than the link field and display field(s), the new option cannot be added. However, you can define default values for these fields in the database (not in DB AppMaker). Adding fields other than the link field or display fields is NOT supported. (Filter fields are NOT supported.) Only fields with textbox are supported. Lookup fields and file upload fields are NOT supported.
Allow sort/search	Enable sorting and searching of the looked-up values. For use with Select (select-one) or Radio or Text Edit Tag only. Since display values are field values in the lookup table (not in the main table), they are retrieved dynamically by code during execution of the script and normally the field cannot be sorted or searched by the display values. DB AppMaker makes it possible by adding a subquery to the SQL to create a virtual field in the main table. Limitations No multiple selection. Select Edit Tag with Multiple enabled are not supported. No lookup table filter or table filter. If the lookup table has filter, the subquery becomes too complex and the SQL will not be supported by the database. The table filter and lookup table filter will be ignored. May not work with all databases. With subqueries the SQL become more complex than usual, the SQL may not be supported by your database. Enable as few fields as possible. Since the SQL become more complex, there is performance penalty, so do not blindly enable this feature for all lookup fields.

Option Template

By default, the options are displayed as comma separated values of the display field values. If you want to display the link field and the display fields in your own HTML, you can use Option Template, just write your HTML code in Option template under Edit Tag panel, e.g. if you have settings like:

and you enter Option Template like:

<ion-text color="primary">{{option.df}}</ion-text> <ion-text color="medium">({{option.df2}})</ion-text>

Then the options for the field will be displayed in your HTML format like:

Option Template supports the following tags:

{{option.lf}}	Link field value
{{option.df}}	Display field #1 value
{{option.df2}}	Display field #2 value
{{option.df3}}	Display field #3 value
{{option.df4}}	Display field #4 value

Note Option template is Angular template, you are not limited to above tags, refer to Template syntax for more details.

If the options need to show some additional field values from the lookup table, you can set the fields as Display fields (#2 to #4) so you can use them in Option Template.