Lookup Table

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

The Lookup Table panel has the following options.

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.
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 By default, 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.
Order by	Optional. Specify a field in the lookup table for sorting the options.
ASC/DESC	Optional. Sorting order. For use with Order By.
Group by	Optional. Specify a field in the lookup table for grouping the options by <optgroup> (HTML Option Group element), e.g. Note This is a field for creating <optgroup> elements in the selection list only, NOT a field for adding GROUP BY clause.
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: 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. 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, This setting is an one-liner. If your logic is complex and cannot be implemented in one line, you can write a function and enter a function call, e.g. You can also pass some variables to your function as arguments, e.g. Your function should return a valid WHERE clause, e.g. You can place your function in Global Code section under Server Events/Client Scripts. (See Server Events and Client Scripts.)
Distinct	Optional. Specify adding DISTINCT option to the SELECT statement for the lookup table.
Auto fill	Optional. If enabled, the script fills the target fields for you automatically. For example, when you select a product number (which is a lookup field using the product table as its lookup table), it will fill product price textbox for you. Note Before using Auto-Fill, review your database design, you should consider database normalization, in many cases you do NOT need to and you should NOT copy the field values from one table to another. You can view the other field values by creating a query/view joining the current table with the lookup table using the parent field as linked field. The required conditions are: The field has Lookup Table and Link field, The field is setup as Text with Force selection enabled (i.e. Auto-Suggest) or Radio or Select with Multiple disabled (i.e. "select-one" only), Auto fill is enabled, Source Fields and Target Fields are set up. If properly set up, when the user changes the selected value of the field, the scripts will try to use other field values (specified by [Source Field]) of the selected record (from the lookup table) to fill the target fields of the current table (specified by [Target Field]) automatically. Click the [...] button and select the source fields and target fields: Note If the Auto-fill original value setting (in Advanced Setting) is enabled, field values of the Source Field and Target Field as stored in the database will be used. The data types of the Source Field and the Target Field must match. If the Source Field has Lookup Table, its actual field value (NOT its Display Field value) is used. Similarly, if the Target Field has Lookup Table, you should fill it with a Source Field value that matches its actual field value (NOT its Display Field value). If the Auto-fill original value setting is disabled, the display/formatted value will be used to auto fill. In this example, when you select a product from combobox, the script know the product ID from the option value, so it can use the ID to locate the product from the same lookup table (your product table) and retrieve other field values such as the product unit price and fill the target fields. Note Do NOT setup fields to autofill each other. For example, if you set up field A to autofill field B (A -> B) and B -> A, it will be an infinite loop. Similarly, if you setup A -> B and B -> C and C -> A, it will not work either.
Allow add	Optional. If enabled, the user will be allowed to add an option to the selection list. Notes 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 PHPMaker). This feature is implemented using Ajax. Adding fields other than the link field, display fields and filter field is allowed. Click the [...] button and select additional fields in the lookup table. However, please note that this feature is designed for adding a lookup value on-the-fly only, it is NOT supposed to replace the full-featured Add page of the lookup table. Although file upload and JavaScript features such as popup calendar and HTML editor are also allowed (v9+), there may be chances that they do not work properly in the popup form. You should choose as few fields as possible. Also, note that the add option form for each lookup table is shared among all fields (possibly in different tables) using the same lookup table. If you change the fields to be added to the lookup table, the shared add option form will affect other fields as well.
Allow sort/search	Enable sorting and searching of the display values instead of the field 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. PHPMaker makes it possible by adding a subquery to the SQL to create a virtual field in the main table. Limitations Multiple selection. Select Edit Tag with Multiple enabled and Checkbox Edit Tag are supported from 2025+. 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. NOT compatible with Dynamic Selection Lists. When this option is enabled, the form element value (and the submitted value) is always the text input (not the Link field value) for searching to work. Therefore, if the field is a parent field in Dynamic Selection Lists (see below), the child fields may not work in the search forms.
Text input for search	Enable text input for the field in the search forms. For use with Select (select-one) or Radio or Text Edit Tag with Allow sort/search enabled. If Edit Tag is not Text (i.e. Select or Radio) and you have enabled Allow sort/search, you may want to search with a textbox instead of combobox or radio buttons. If so, enable this setting. Note that if Edit Tag is Text and you have enabled Allow sort/search, the input is textbox, this setting is enabled automatically even you have not checked this setting to enable it explicitly. Note NOT compatible with Dynamic Selection Lists. When this option is enabled, the form element value (and the submitted value) is always the text input (not the Link field value) for searching to work. Therefore, if the field is a parent field in Dynamic Selection Lists (see below), the child fields may not work in the search forms.

Option Template

By default, the options are displayed as comma separated values of the display field values. If you just want to change the display value separator from comma to other string, you can use server events such as Page_Load (see Server Events and Client Scripts) to set the field object's DisplayValueSeparator property, e.g. if the field name is "MyField",

However, 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:

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

Option Template supports the following tags:

{{:lf}}	Link field value
{{:df1}}	Display field #1 value
{{:df2}}	Display field #2 value
{{:df3}}	Display field #3 value
{{:df4}}	Display field #4 value

Note Option template is JsRender template, you are not limited to {{:...}}, there are other tags for you to build an advanced Option Template, refer to JsRender API for more details.

If the options needs to show some additional data from the lookup table, you can get the additional data as Display fields so you can use them in Option Template. If you use Text Edit tag (see Field Setup), sometimes you may not need the additional data in the input textbox, then again you can set the DisplayValueSeparator property by server event. If the separator for a display field is not specified, the display field value will not be placed in the input textbox. There are 3 possible settings:

Note If you enable Use native SELECT tag for select-one fields (see Tools -> Advanced Settings), note that the <option> HTML elements of native <select> HTML elements do not support HTML, using HTML tags in Option Template (see Lookup Table) will NOT work.

Ajax by API and Client Scripts

Sometimes you may want to access the lookup table by Ajax yourself. For example, after the user entering a value for a field, you may want to auto-fill another field in your own way, then you can use API and Client Scripts to do it (in such cases do not enable the built-in Auto-Fill in the Lookup Table panel). Say, if you want to fill the product price when you select a product number (using products table as lookup table) when inserting a new record, you can auto-fill the product price in orderdetails table either asynchronously or synchronously with your code.

Example 1 - Auto-Fill asynchronously by "view" action of API

Write a Startup Script (JavaScript) for Add Page (e.g. orderdetails) to attach onchange event and get data from other table (e.g. products) by "view" action of API.

Notes

This example uses the built-in API so there is no need to write server side code.
If action is "view", you get a record (object) by passing the primary key in URL. For composite keys, separate the key values by the composite key separator (default is ","). The result is one record only.
If action is "list", you get records (array of objects) by passing search parameters in URL (see URL in List page after searching), the result is an array of records.
If User ID and/or User Level Security is enabled, the records and/or pages are still protected. For example, if the user does not have permission to the View page, the "view" action is not allowed.
The values returned from API are database values. You may format them with your code for your specific needs.

Example 2 - Auto-Fill synchronously by ew.ajax()

Write a Startup Script (JavaScript) for Add Page to attach onchange event.

Example 3 - Auto-Fill asynchronously by ew.ajax()

Write a Startup Script (JavaScript) for Add Page to attach onchange event. Also see note in Example 2.

Example 4 - Auto-Fill asynchronously by custom API action and client script

Add a custom API action, say "getUnitPriceByProductID", by Api_Action server event (see Server Events and Client Scripts) to return the required value. In this example, only a single value is required so ExecuteScalar() is used, e.g.

If you want to return a whole row as JSON, you can use $response->withJson() in combination with ExecuteRow() (see Server Events and Client Scripts), e.g.

Write a Startup Script (JavaScript) for Add Page to attach onchange event:

Use Modal Dialog for Lookup

For Edit Tags with lookup table (i.e. TEXT/SELECT/RADIO/CHEKCBOX), you can choose to enable Use Modal Dialog for Lookup to replace the Edit Tag with a modal dialog. The modal dialog does not only allow user select a record from the lookup table, but also supports searching and paging (infinite scrolling).

Note The modal dialog is same for TEXT/SELECT/RADIO/CHEKCBOX, while it has the same functionality of allowing user to select a record from the lookup table, it is NOT the original inputs (i.e. selection list, radio buttons, checkboxes, or dropdown list) anymore.