Lookup Table

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

 

Note From v12, Lookup Table always use Ajax and the "Use Ajax" option in previous versions has been removed.
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 C# 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 your C# expression:

(ew_NotEmpty(AField.CurrentValue)) ? "`ALookupTableField` = " + AField.CurrentValue : ""

Notes: (examples in C#)

  1. 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.

  2. 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 needs to be escaped,

    (ew_NotEmpty(AField.CurrentValue)) ? "`ALookupTableField` = " + ew_QuotedValue(AField.CurrentValue, EW_DATATYPE_STRING) : ""


  3. 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'" : ""

  4. 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.

    MyLookupFilterFunction()

    You can also pass some variables to your function as arguments, e.g.

    MyLookupFilterFunction(AField.CurrentValue)

    Your function should return a valid WHERE clause, e.g.

    public static sring MyLookupFilterFunction(object value) {
        string Filter;
        if (ew_NotEmpty(value)) {
            Filter = "`ALookupTableField` = " + Convert.ToString(value); // Assume ALookupTableField is integer field
        } else {
            Filter = "";
        }
    }
    return Filter;

    You can place your function in Global Code section under Server Events/Client Scripts. (See Server Events and Client Scripts.)
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.
Allow add

Optional. If enabled, the user will be allowed to add an option to the selection list.

Notes
  1. 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.
  2. 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 ASP.NET Maker).
  3. This feature is implemented using Ajax.
  4. 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 DHTML 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.
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:

  1. The field has Lookup Table and Link field,
  2. The field is setup as Radio or Select with Multiple disabled (i.e. "select-one" only),
  3. Auto fill is enabled,
  4. 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 Remember that the actual 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).

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 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. ASP.NET Maker makes it possible by adding a subquery to the SQL to create a virtual field in the main table.

Limitations
  1. No multiple selection. Select Edit Tag with Multiple enabled and Checkbox Edit Tag are not supported.
  2. 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.
  3. May not work with all databases. With subqueries the SQL become more complex than usual, especially for Custom View, the SQL may not be supported by your database. (This is another reason why you should always use database query/view whenever possible, see Using Custom View.)
  4. 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.
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",

MyField.DisplayValueSeparator = "-"; // Use hyphen as separator

MyField.DisplayValueSeparator = new List<string>() {",", "|", "-"}; // Array of separators (max. 3) for display field #2 to #4

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:

<span class="text-info">{{:df1}}</span> <small class="text-muted">({{:df2}})</small>

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 in the format of JsRender 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 You must always put the additional data in the last (n) display field(s). There are 3 possible settings:

MyField.DisplayValueSeparator = new List<string>(); // Display field #2 to #4 hidden

MyField.DisplayValueSeparator = new List<string>() {", "}; // Display field #3 to #4 hidden

MyField.DisplayValueSeparator = new List<string>() {",", "|"}; // Display field #4 hidden

 

Ajax by Server Events 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 Server Events 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 synchronously

Write Page_Load server event for Add Page:

ew_SetClientVar("MyCustomSql", ew_Encrypt("SELECT UnitPrice FROM products WHERE ProductID = {query_value}")); // Pass a server side variable (encrypted SQL) to client side

Note that the WHERE clause of your SQL must contain the tag {query_value} which will be replaced by the input value. In above example we assume ProductID is an integer field so there is no need to quote the {query_value}. If the ProductID is of string type, you should quote the {query_value}, e.g.

ew_SetClientVar("MyCustomSql", ew_Encrypt("SELECT UnitPrice FROM products WHERE ProductID = '{query_value}'")); // Pass a server side variable (encrypted SQL) to client side

The variable MyCustomSql will be passed to client side as property of the ewVar object so you can get it back by ewVar.MyCustomSql or ewVar["MyCustomSql"].

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

$("#x_ProductID").change(function() {
    var result = ew_Ajax(ewVar.MyCustomSql, $(this).val());
// Send the encrypted SQL and client side input value to server side by Ajax for execution and get the result
    $("#x_UnitPrice").val(result);
// Set the result (manipulate it first if necessary) to the target field
});

Note The function ew_Ajax() reuses the generated script for lookup tables (ewlookup*.cs) so there is no need to write server side handler. For synchronous requests, if the result is a recordset, it is in the format of array of object. If the result is a row, it is in the format of object. If the result is a single value, it is in the format of string, remember to convert it to the proper data type before manipulation. The function also supports asynchronous request, just pass a callback function as the third argument, but the result from the server side is not processed and therefore is always in the format of array of object. See the source code of ew_Ajax() in the generated ewx*.js for details.



Example 2: Auto-Fill asynchronously

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

$("#x_ProductID").change(function() {
    $.post(ew_CurrentPage(), { "myajax": 1, "token": EW_TOKEN, "value": $(this).val() }, function(result) {
// Post back your custom data (with the synchronizer token)
        $("#x_UnitPrice").val(result); // Set the result (manipulate it first if necessary) to the target field
    });
});

Write a server side handler, check your custom data "myajax" in Page_Load server event for Add Page and return the required value. In this example, only a single value is required so ew_ExecuteScalar() (see Server Events and Client Scripts) is used, e.g.

if (ew_Post("myajax") == 1 && ew_Post("value") != "") { // Check if it is your custom Ajax and if the query value is present
    int val;     val = ew_ExecuteScalar("SELECT UnitPrice FROM products WHERE ProductID = " + ew_Post("value"));
// Get the desired value (assume ProductID is integer so no need to quote the value)
    ew_Write(val);
// Return the value (manipulate it first if necessary)
    Page_Terminate();
// Terminate the page
}

If you want to return a whole row (as JavaScript object) so you can fill multiple fields, you may want to use ew_ExecuteRow() (see Server Events and Client Scripts) and json-encode the row before returning it so that you do not need to convert before using it in your callback function on the client side, e.g.

val = ew_ArrayToJson(ew_ExecuteRow("SELECT * FROM products WHERE ProductID = " + ew_Post("value"))); // Get the desired value (assume ProductID is integer so no need to quote the value)


 
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.

 

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.

 

 ©2002-2017 e.World Technology Ltd. All rights reserved.