Lookup Table Builder

 

In this topic ... Quick Tips Specifying Inputs Populating Controls on a Form Using Resource Bundle Data Using Lookup Tables with Other Builders Related Topics ... Data Field Modifier Select Builder Radio Button Group

Use this Builder to perform a database query that returns results we can include in a page control, such as a select control or a radio button group. This Builder can also be used to integrate arbitrary name/value pairs into a ResultSet. This enables you to populate a list with a choice not included in the results returned from the database. You might want to do this to include an "any" or a blank option in the list of choices.

This Builder is particularly useful when you are building a search form that will query a database. We can use this Builder, in conjunction with the Data Field Modifier Builder, to create a form that presents the user with list choices based on elements in the database the form is querying.

 

Quick Tips

• Stub out the Database -- During development, use an XML list structure to represent the query results you expect this Builder to return. This will allow you to develop forms without requiring a live connection to the database.

• Avoid Escape Characters - Do not use an escape character in front of quotes in SQL statements. For example, do not use:

select count(*) \"Count\" from mytable

Instead use:

select count(*) "Count" from mytable

 

Specifying Lookup Table Builder Inputs

The Lookup Table Builder can access data from three types of sources:

• Database Query - Any SQL-compliant database that is correctly configured and available to the Factory

• XML Data - Data in XML format stored in a variable or some other repository

• New XML Data - Data in XML format that has been entered directly into the Lookup Table Builder

The inputs Builder inputs required depend on the data type you choose. The following tables provide information about Builder inputs based on data type for Database Query, XML Data, and New XML Data.

 

Specifying Inputs for a Database Query

The Lookup Table Builder takes the inputs described in the table below when you select "Database Query" as the Data Type. For help on inputs common to many or all Builders such as those in the Properties and HTML Attributes input groups, see "Using the Builder Call Editor."

Input name	Description
Name	Enter a name for this builder call. The designer tool displays this name in the builder call list. The name must begin with a letter or an underscore and contain only letters, digits, and underscores. This restriction allows the Builder to use the "Name" input to build additional WebApp artifacts that implement the core of this Builder s runtime functionality.
Data Type	Select the type of data element you want to query. You can select: Database Query - Choose this to perform a SQL call on a database table that contains name/value pairs.
Fetch DataSource Names	Press this button to fetch a list of JDBC DataSources defined on the application server supporting the Designer. The list will be inserted as choices for the "SQL DataSource" input. If no DataSource names are listed in the "SQL DataSource" input, then the Designer may not be properly configured to query an application  server, the server might not be running, or there are no DataSources defined by the application server. Any valid datasource name can be typed into the SQL Datasource builder input. The Fetch Datasource Names button, however, will only list those datasources whose full names begin with jdbc/, jdbc/v4/, or jdbc/v5/. If you know that there is a valid datasource with a name that does not match any of these patterns, type the name in. If the name is correct, it should work. Depending on the performance of the application server, it might take several seconds to load the list of datasource names. This button is not displayed if the Builder is configured to use an override SQL DataSource. See the section entitled "Using Override Builders" for more information.
SQL DataSource	Open this list and select the JDBC DataSource to be used by the SQL Call when it executes the SQL statement you will define later in this Builder This input will be displayed as a read-only prompt indicating the name of the override Builder if the SQL Call Builder has been configured to use a custom SQL DataSource Builder.
Database Explorer
Catalog Pattern	Enter a name of the catalog to search for tables. Leave blank to match all catalogs. The following wildcards are supported: _   - (underscore) Matches a single character %  - (percent) Matches zero or more characters Depending on the database and JDBC driver associated with the DataSource, this entry might be case sensitive.
Schema Pattern	Enter an SQL schema pattern to use while searching for tables. Typically this is the ID of the database user that owns the table you are trying to find. Leave this input  blank to match all schemas. The following wildcards are supported: _   - (underscore) Matches a single character %  - (percent) Matches zero or more characters Depending upon the database and JDBC driver associated with the DataSource this entry may be case sensitive.
table Pattern	Enter a table name  to use while searching for tables. Leave blank to match all tables. The following wildcards are supported: _   - (underscore) Matches a single character %  - (percent) Matches zero or more characters Depending upon the database and JDBC driver associated with the DataSource this entry may be case sensitive.
Fetch Table Names	Press to fetch all of the table names that match the catalog, schema, and table patterns you specified. The names will be inserted as choices for the "Table Name" input. If no table names are listed in the "Table Name" input, the Designer may not be properly configured to query an application server, the server may not be running, or there are no table names that match the patterns you defined. Tip - Review the "Explorer Status" panel for any warning or error messages.
Table Name	Select the name of the table from the drop down list. The selecte dtable will be used by the Builder when you press the "Generate Sample SQL" button.
Value Column	Select the database table column that will provide the values of the Lookup Table
Label Column	Select the column name  in the database table that provides the labels that match the values specified in the Value Column input.
Generate Sample SQL	Press to have the Builder generate a sample SQL statement in the "Sample SQL" input field.
Sample SQL	The sample SQL statement generated by the Builder for the table selected by the "Table Name" input. The SQL displayed in this panel is editable. For example, we can edit a  SELECT statement to return only the columns we need instead of all the columns defined for a table, view or alias.
Apply Sample SQL	Press to copy the sample SQL statement and parameters to the Builder s actual inputs for these values.
SQL Statement	Enter an SQL statement. Note that we cannot use positional parameters in this statement. In most cases, a "select distinct" is the appropriate query as it will return name/value pairs that conform to a 1-to-1 mapping.  For example: select distinct   lookup_table.ID, lookup_table.NAME from   PRODUCT lookup_table Alternately, we can use this Builder's Database Explorer to locate and generate some sample SQL. We can then edit that SQL as needed, and apply the edited SQL to this input.
Variable Type	This input allows you to use data in a resource bundle. Click here to learn more about using resource bundle data. We can choose: Value Tag and Label Tag - To use standard model data in this form.   Resource Bundle Style - To use data in the form that the Localized Resource Builder produces.
Value Tag	Enter the column name (or in the case of an XML variable the node name) of the column you want to use to represent the value element. If your query returns more than two columns, use this input to explicitly name the Value column.
Label Tag	Enter the column name (or in the case of an XML variable the node name) of the column you want to use to represent the label element. If your query returns more than two columns, use this input to explicitly name the label column.
Concurrency	This input is usually not required and should be left blank. In some cases a database will be configured to require concurrency. When that is the case, select a concurrency mode for the Result Set generated by this statement. Modes are: Read Only - Underlying database result set is read-only Updatable - Underlying database result set will support data changes Some databases may not support updatable result sets. Check the documentation for your target database for detailed information about the types of result sets supported
Scroll Type	This input is usually not required and should be left blank. In some cases a database will be configured to require a scroll type. When that is the case, select the type of scrolling used by the result set. Types include: Forward -Traditional result set type. Forward cursor movement only. Insensitive - Result set not sensitive to changes in underlying database Sensitive - Result set is sensitive to changes in underlying database Some databases may not support all of these scroll types. Check the documentation for your target database for detailed information about the types of result sets supported.
Refresh Interval (secs)	Enter the time in seconds that Lookup Table data will be retained in the cache. Entering a value here will enable caching and improve performance. This is generally a good idea, providing that a large enough value is used to prevent unnecessary cache refreshes. A value of 300 seconds is usually appropriate in most applications.
Additional Data Definition
Get Data From	Identifies the source of data. We can choose: Builder Input - To get data from Additional Pairs table. XML Data - To get data from an XML type variable. This choice is useful if there is a default set of labels/values that should appear in a number of select lists
Add a blank selection	Select this option to provide a blank selection for optional fields in a form where the user does not need to provide a specific value provided by the data set.
Additional XML Data	This input is available when the Get data From input is XML Data. Use this input to specify an indirect reference to the location where the XML data is located.
Additional Pairs	Specify any additional label/value pairs to be included in the lookup table results. (You might want to do this to allow development to proceed while the  database is under construction.) Label - A label to be included in the resultset Value - A value to be included in the resultset Collision Resolution - This choice allows you to handle the situation that can occur if a value/label pair you specify is identical to one already contained in the data. When this occurs, select  Use Original Value to use the pair contained in the original data. Select Use this Value to use the pair you specified in the table.
Table Position	Specifies where additional pairs are in the XML output of this Builder. The choices are: In Front - The default. Additional data pairs placed in front of existing pairs in the XML stream. In Back - Additional data pairs placed in back of existing pairs in the XML stream.
Parent Tag	Specified the name of an XML element used to contain additional pairs in the table. Default element name is: <UserAddition>
Filtering
Label/Value Filter Mode	Use this input to specify how XML nodes will be filtered. You might want to do this if the rows in your XML are not identical in terms of label/value content. We can choose: Use elements with both label and value elements - To use elements with sub-elements that match the chosen Label and Value inputs None: use all elements -  Use all the data.

 

Specifying Inputs for the XML Data Type

The Lookup Table Builder takes the inputs described in the table below when you select "XML Data" as the data type. For help on inputs common to many or all Builders such as those in the Properties and HTML Attributes input groups, see "Using the Builder Call Editor."

Input Name	Description
Name	Enter a name for this Builder call. The Designer displays this name in the Builder Call List. The name must begin with a letter or an underscore and contain only letters, digits, and underscores. This restriction allows the Builder to use the "Name" input to build additional WebApp artifacts that implement the core of this Builder s runtime functionality.
Data Type	Select the type of data element you want to query. You can select: XML Data - Choose this to perform an Xpath search of a WebApp variable containing an XML structure that represents name/value pairs. (This XML structure must contain two sub-elements named "Value"and "Label.").
XML Data	Use an indirect reference to point to a method call or a variable that returns the resultset to be used for the lookup table. The resultset must include elements named "Value" and "Label"  that have child elements representing label/value pairs.
Variable Type	This input allows you to use data in a resource bundle. Click here to learn more about using resource bundle data. We can choose: Value Tag and Label Tag - To use standard model data in this form.   Resource Bundle Style - To use data in the form that the Localized Resource Builder produces.
Value Tag	Enter the column name (or in the case of an XML variable the node name) of the column you want to use to represent the value element. If your query returns more than two columns, use this input to explicitly name the Value column.
Label Tag	Enter the column name (or in the case of an XML variable the node name) of the column you want to use to represent the label element. If your query returns more than two columns, use this input to explicitly name the label column.
Refresh Interval (secs)	Enter the time in seconds that Lookup Table data will be retained in the cache. Entering a value here will enable caching and improve performance. This is generally a good idea, providing that a large enough value is used to prevent unnecessary cache refreshes. A value of 300 seconds is usually appropriate in most applications.
Additional Data Definition
Get Data From	Identifies the source of data. We can choose: Builder Input - To get data from Additional Pairs table. XML Data - To get data from an XML type variable. This choice is useful if there is a default set of labels/values that should appear in a number of select lists
Additional XML Data	This input is available when the Get data From input is XML Data. Use this input to specify an indirect reference to the location where the XML data is located.
Additional Pairs	Specify any additional label/value pairs to be included in the lookup table results. (You might want to do this to allow development to proceed while the  database is under construction.) Label - A label to be included in the resultset Value - A value to be included in the resultset Collision Resolution - This choice allows you to handle the situation that can occur if a value/label pair you specify is identical to one already contained in the data. When this occurs, select  Use Original Value to use the pair contained in the original data. Select Use this Value to use the pair you specified in the table.
Table Position	Specifies where additional pairs are in the XML output of this Builder. The choices are: In Front - The default. Additional data pairs placed in front of existing pairs in the XML stream. In Back - Additional data pairs placed in back of existing pairs in the XML stream.
Parent Tag	Specified the name of an XML element used to contain additional pairs in the table. Default element name is: <UserAddition>
Filtering
Label/Value Filter Mode	Use this input to specify how XML nodes will be filtered. You might want to do this if the rows in your XML are not identical in terms of label/value content. We can choose: Use elements with both label and value elements - To use elements with sub-elements that match the chosen Label and Value inputs None: use all elements -  Use all the data.

 

Specifying Inputs for the New XML Data Type

The Lookup Table Builder takes the inputs described in the table below when you select the data type " New XML Data." For help on inputs common to many or all Builders such as those in the Properties and HTML Attributes input groups, see "Using the Builder Call Editor."

Input Name	Description
Name	Enter a name for this Builder call. The Designer displays this name in the Builder Call List. The name must begin with a letter or an underscore and contain only letters, digits, and underscores. This restriction allows the Builder to use the "Name" input to build additional WebApp artifacts that implement the core of this Builder s runtime functionality.
Data Type	Select the type of data element you want to query. You can select: New XML Data - Choose this to specify Lookup table data explicitly as a Builder input.
New XML Data	This input is displayed when the New XML data type is enabled. Enter in this text entry area the XML code that defines the Lookup table data.
Variable Type	This input allows you to use data in a resource bundle.  Click here to learn more about using resource bundle data. We can choose: Value Tag and Label Tag - To use standard model data in this form.   Resource Bundle Style - To use data in the form that the Localized Resource Builder produces.
Value Set	This input is available when the Variable type is Resource Bundle Style. Use this input to specify a reference to an XML variable, a comma-delimited String, or a hard-coded comma delimited string. This will be a set of values only. The lookup table will be built from this set of values. The resulting table will be in the order of the values specified.
Value Tag	Enter the column name (or in the case of an XML variable the node name) of the column you want to use to represent the value element. If your query returns more than two columns, use this input to explicitly name the Value column.
Label Tag	Enter the column name (or in the case of an XML variable the node name) of the column you want to use to represent the label element. If your query returns more than two columns, use this input to explicitly name the label column.
Refresh Interval (secs)	Enter the time in seconds that Lookup Table data will be retained in the cache. Entering a value here will enable caching and improve performance. This is generally a good idea, providing that a large enough value is used to prevent unnecessary cache refreshes. A value of 300 seconds is usually appropriate in most applications.
Additional Data Definition
Get Data From	Identifies the source of data. We can choose: Builder Input - To get data from Additional Pairs table. XML Data - To get data from an XML type variable. This choice is useful if there is a default set of labels/values that should appear in a number of select lists
Additional XML Data	This input is available when the Get data From input is XML Data. Use this input to specify an indirect reference to the location where the XML data is located.
Additional Pairs	Specify any additional label/value pairs to be included in the lookup table results. (You might want to do this to allow development to proceed while the  database is under construction.) Label - A label to be included in the resultset Value - A value to be included in the resultset Collision Resolution - This choice allows you to handle the situation that can occur if a value/label pair you specify is identical to one already contained in the data. When this occurs, select  Use Original Value to use the pair contained in the original data. Select Use this Value to use the pair you specified in the table.
Table Position	Specifies where additional pairs are in the XML output of this Builder. The choices are: In Front - The default. Additional data pairs placed in front of existing pairs in the XML stream. In Back - Additional data pairs placed in back of existing pairs in the XML stream.
Parent Tag	Specified the name of an XML element used to contain additional pairs in the table. Default element name is: <UserAddition>
Filtering
Label/Value Filter Mode	Use this input to specify how XML nodes will be filtered. You might want to do this if the rows in your XML are not identical in terms of label/value content. We can choose: Use elements with both label and value elements - To use elements with sub-elements that match the chosen Label and Value inputs None: use all elements -  Use all the data.

 

Using the Database Explorer

Use the Database Explorer if we need assistance in creating an SQL statement. We can browse the database and construct the SQL statement on-the-fly. This is particularly useful if you are not an expert in using SQL. As you specify various inputs to the Explorer, you build a statement that can then be edited or directly applied to the SQL Statement input.

 

Populating Controls on a Query Form

Follow these steps to create a form that includes a select list populated with relevant database choices:

1. Use the Lookup Table Builder to create a database query (or inspect an XML variable) that returns the items (label and value fields) you want included in the select control.

2. (Optional) Use the Additional Data Definition inputs to add to the query results any label/value pairs you want to include in the select list (blank, etc.).

3. Use the object created by the Lookup Table Builder is an input for a Select List Builder or a Data Field Modifier Builder. Each of these Builders will use the label/value choices returned by the Lookup Table Builder.

 

Using Lookup Tables with Other Builders

The output of the Lookup Table Builder can be used by several other Builders. including:  Radio Button Group, Select, Text, and Data Field Modifier.

The Lookup Table Builder creates a lookup table. This table is used to  translate between some computer-readable ID and a human-readable name, often the result of a database query.  The table also caches the results across different users, including the opportunity to prevent data from becoming stale.

There are two ways that the lookup table can be used:

• In Select and Radio Button Group Builders -- Table can be used to provide the set of choices that will be offered. In each of these Builders, the user will see the human-readable form of the choices, but the actual value selected will be the computer-readable form

• In Text Builders -- Table can be used to translate a value for display. In this case, the internal value is the computer-readable form, and displayed value is the human-readable one.

In each of these Builders, you will not see any Lookup table-related inputs unless there is a Lookup Table in your WebApp.  If there is, then a new Builder input called "LookupTable" will appear, with the prompt "Lookup Table Used."  It s default is "None."  If you select some choice other than "None," in the case of the Select and Radio Button Group Builders, the Builder inputs options, Text Tag, and Value Tag will be hidden. These inputs will be filled in automatically to correspond to the Lookup Table Builder.

The Text Builder operates in a similar manner.  There will be nothing different about it unless there is a lookup table in the WebApp.  If there is, a new Builder input called "LookupTable" will appear, with the prompt "Lookup Table Used." .  If you choose something other than "None," no other changes occur in the UI of the Builder.  However, the result of the Builder is that the value is "translated" through the lookup table.

A typical scenario where a lookup table is useful is a Sales Application, where there is a database Table of sales people, and another for customers. In the customer record is a SalesPersonID, which is a key into the SalesPerson Table.

When the user of the resulting WebApp is filling out a new Customer Record, he will want to assign a SalesPerson to that record. You do not want him to have to type in the ID of a SalesPerson record, you want him to be able to choose from a list of all the available Sales People, where he can see the names but the value actually entered is the ID. Similarly, when the user is viewing a Customer record (or a list of them) you do not want him to see ID s, even though that is what the value is in the record. You want the values "translated" through the SalesPerson Table so that the salesperson s name is displayed. (Note that, in a query, you could accomplish this with a database join. However, in a Customer record that has just been created but not yet stored in the database, the confirmation page will surely want to use this technique.)

We can accomplish this entire process with three steps.

1. Use the Lookup Table Builder, targeted at the SalesPerson database, to create the Lookup Table.  (Note that we can specify how often the Lookup Table information should be refreshed, enabling you to keep the data current without creating a performance problem.)

2.  In the Customer Edit page, for the field "SalesPersonID"  use either a Select or a Radio Button Group Builder, and choose the Lookup Table in the Lookup Table Used field. This has the effect of specifying the correct values for Options, Value Tag, and Text Tag.

3. On the confirmation page (or anywhere else you are displaying a Customer record) in the Text Builder for the SalesPersonID field, you still choose, as the data, that field in the record, but then also choose the name of the Lookup Table in the Lookup Table Used Builder input. This will cause the value, which is an ID, to be translated through the table and presented as the readable name of the salesperson.

If you are using a Data Page Builder (or any of the Builders that use it, such as the Data View Builder), we can also access the Lookup Table. Create a Data Field Modifier Builder and point it to the field or fields in question. If there is a Lookup Table in the WebApp, then a similar Builder input, Lookup Table Used, will be available for you to choose. The Data Field Modifier will use the correct process depending on whether the field being modified is a "Data Entry" or a "View Only" field.  When a Lookup Table is selected, if the field is Data Entry, then either a Select or a Radio Button Group will be used (depending on the other settings). If the field is a View Only field, then the translated text will be used. Note that we can change multiple fields with a single Data Field Modifier Builder, even if some of the fields are Data Entry and some are View Only.

 

Using Resource Bundle Data

There are two techniques we can use to incorporate resource data as a Lookup Table. The technique you choose depends on your data formats and on the availability in the model of a Localized Resource Builder.  We can choose

• No Localized Resource Builder in model - If you have a resource bundle (or any similarly-shaped data) in your model, we can use a Lookup Table Builder, tell it to reference that variable, and select "Resource Bundle Style."

• Localized Resource Builder Available - In the Localized Resource Builder there is a check box input,: "Create Lookup Table."  If you enable this input,  a Lookup Table Builder with the name <name of Localized Resource Builder>LookupTable is added to the model with all the correct settings to make a lookup table out of this data.  

In essence, you use a resource bundle's keys and values as the "data" and "text" in a Lookup Table. For example, if the resource bundle contains the following:

P1=East

P2=West

P3=North

P4=South

the Lookup table can use as data values P1, P2, P3 and P4, and the corresponding text Table values will be Ease, West, North, South.

The Builder input, "Value Set," is available if you choose "Variable type Resource Style." This input provides an indirect picker, we can use eto specify a reference to an XML variable, a reference to a comma-delimited String, or a hard-coded comma delimited string. This input will contain a set of values only, and the Lookup Table will be built from your set of values. The resulting table will be in the order of the values you specified.

This has the added value used to only a single resource bundle and we can build several Lookup Tables from it.

For example, if you have a resource bundle with the following keys and text:

jan=January

feb=February

mar=March

.

.

.

nov=November

dec=December

and

P1=East

P2=West

P3=North

P4=South

You could have a variable with the following XML. Note the order of the values is different from in the bundle

<top>

   <option>P1</option>

   <option>P3</option>

   <option>P2</option>

   <option>P4</option>

</top>

First you would add a Localized Resource Builder to the resource Bundle.

Then you could add one Lookup Table Builder using the localized resource and pointing at the XML above. This would create a lookup table with values P1, P3, P2, P4 and the corresponding text would be East, North, West, South..

Then you could add another Lookup Table Builder using the same localized resource and, in the "Value Set" input,  you specify the comma-delimited  text entry: jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec

This will create another table with those twelve values and the labels, again, taken from the bundle.

Note that you never put any labels in the model. You put only the values in the model, Labels come from the resource bundle.   

 

Data Formats

Value Tag and Label tag Data Format

This data is in the standard Factory format and can be used in the absence of a Localized Resource Builder:

<Flights>

   <Flight>

      <label>American Airlines</label>

      <value>AA</value>

   </Flight>

   <Flight>

      <label>Lufthansa</label>

      <value>LX</value>

   </Flight>

   <Flight>

      <label>Air France</label>

      <value>AF</value>

   </Flight>

</Flights>

Resource Bundle Style Data Format

This data is in the format produced by the  Localized Resource Builder:

<Data>

  <AA>American Airlines</AA>

  <LX>Lufthansa</LX>

  <AF>Air France</AF>

</Data>

 

Methods Created by this Builder

This Builder creates the following methods in the Webapp:

• Builder_nameToLabel -  Returns the corresponding label of a given value

• Builder_nameToValue - Returns the corresponding value of a given label

The following methods are also created by the Builder but are hidden. These methods might be of value if you want to integrate this Builder into another, high-level, Builder:

• Builder_callGetLookupTable - Returns an IXml object that contains the data from the original XML data or SQL call and any additional Name/Value pairs you defined in the Lookup Table Builder call's Additional Data section.

• Builder_callGetLabelTagName - Returns the name of the element in the data that the Lookup Table uses for display in either a Select or Radio Box Builder call.

• Builder_callGetValueTagName - Returns the name of the element whose value the Lookup Table uses as the value for either a Select or Radio Box Builder call.

• Builder_callGetParentTagName - Returns the name of the element that contains the specified label and value elements.

The nameToLabel and nameToValue methods have the same signature, parameters and names. The methods change internally according to the data type (database or XML variable) being accessed .