How to use Dynamic Lists

Creating and maintaining lists manually. 2

Creating mappings 4

When are Dynamic Lists built at runtime? 6

Dynamic Lists in tables 6

How are Dynamic Lists built at runtime? 6

Creating and using a simple list - a worked example. 7

Debugging Dynamic Lists 7

Restrictions 8

Advanced uses 8

Using Native SQL 8

Creating 'linked' lists 8

Using Dynamic SQL 12

Adding variables to Dynamic Lists 13

What is a Dynamic List?

A Dynamic List is a list of values extracted dynamically from a database at runtime. A simple example might be a list of customer names. Additional criteria can also be applied to a list, e.g. a list of customer names in a certain region or with turnover greater than a specified amount. The region and turnover could be other fields in the form where the user has entered information.

A Dynamic List is linked to a field or table column using the field’s list properties and the field/column can then be added to a page to display the list.

Each list contains two sections: a database section, and a list of fields. The database section contains information on the SQL statement that will be issued and the Database Connection to which it will be targeted. The List fields section contains a list of all the columns in the database which are included in the list: this includes both columns that will be retrieved from the database and columns that will be used to restrict the rows returned (i.e. will be part of the SELECT statement where clause).

When a list is associated with a form field, the fields of the list are linked with the form fields via Dynamic List Mappings. These mappings support configuration of:

· the list field to be displayed to the user

· the list field returned (this is retained as the field’s value)

· any input fields required to build the list i.e. form part of the SQL where clause

· any additional fields in the form to be populated with values from the list when the user makes a selection. For example, when the user selects a customer name, we might also want to retrieve and display other information about the customer.

When a list is associated with a form field, the mappings between the list fields and the form fields are generated automatically, but they can also be edited by clicking on the Dynamic List Mappings field property.

How to create and use a list

To create the list manually, use the Dynamic List editor.

You can also create a Dynamic List from a Database Resource using the build list wizard in the Database Resource Editor. This can be very useful as the Database Resource Editor also contains a wizard to import tables and columns from a database schema; by combining these two wizards you can create a Dynamic List with a few mouse clicks.

Creating and maintaining lists manually

A Dynamic List is created and maintained using the Dynamic List editor within the Ebase Xi Designer. A new list is created by right clicking in the designer tree and selecting New > Dynamic List.

Database Section

The top section is the database section that is used to set the target database and to build the SQL statement. The Database Connection dropdown is a list of the defined Database Connections to which the Ebase system has access. The special entry **Dynamic can be chosen to indicate that the database name will be supplied dynamically at runtime (See Using dynamic databases for more information)

The Debug checkbox will log all SQL statements issued to the database to the execution log.

There are two options for the creation of SQL statements: Assisted SQL (the default) and Native SQL:

· Assisted SQL refers to the dynamic generation of SQL select statements by the system using the four boxes select columns, select from tables, where clause, additional SQL clauses. All four boxes can contain variables to be substituted from a form.

· Native SQL allows the use of more complex SQL statements and is for more advanced use.

Note the syntax used in the where clause in the example above: HIRE_DATE = &&HIRE_DATE

The &&HIRE_DATE indicates to the system that this value is to be dynamically substituted at runtime from the form field that is mapped to the HIRE_DATE list field. A form field variable can be specified using two possible syntaxes &&VAR1 or &&{VAR1}. As the SQL statement for this list cannot be executed unless a value exists for HIRE_DATE, the Required checkbox for the HIRE_DATE list field must be checked as shown above.

The Build Fields icon is a labor-saving device. If you have completed the four SQL statement boxes, you can click this button and the system will create the list field definitions for you. You will then need to set the field types correctly. Note that this function will only work correctly if the select columns box consists of a simple comma delimited list of columns.

The Verify icon on the toolbar can be used to check that the list fields have been correctly defined.

Fields Section

The lower section is the fields section. You must create one field definition for each field that you want to map to a form field - in practice this will be all the fields in the select columns box plus any substitutable fields in the where clause box. The name of each field must exactly match the corresponding column name or alias in the select columns or where clause boxes including case. If you are using table joins and have table prefixes on the column names or if you are using database functions, then assign an alias to the column and use the alias name for the name of the list field.

The Build fields from select columns icon on the toolbar will create a list field for each column in the Select columns box. It also deletes all existing list fields so should be used with caution. This icon creates all fields with type VARCHAR, so the types should be adjusted manually.

The field Type should be set to match the database column type. The values available in the dropdown lists are the types from the JDBC standard. See your database JDBC driver documentation for how these map to your database types. The system makes use of these types when converting the value received from the database to the mapped field within the form. If an illegal mapping is detected, you will receive an error message.

The Required checkbox indicates that the list cannot be built unless this field has a non null value. A list mapping is required for all fields flagged as Required.

The Description can be used to enter meaningful information about the field. This information is visible within Ebase Designer when building the list mappings.

The Persistent checkbox indicates that a column of the same name exists in the database. The system will attempt to retrieve all columns from the database which are flagged as persistent and are not flagged as required. Uncheck this option to stop the system retrieving the column from the database.

The Dynamic Sql checkbox indicates that this field contains a substitutable part of a SQL statement. Selecting this option automatically sets a number of other options: Persistent is unchecked, Required is set, Type is set to CHAR. See Using Dynamic SQL for more information.

Creating mappings

Mappings are used to link the Dynamic List fields to the fields of a form and they are initially created when a Dynamic List is associated with a form field. At this time, the system will automatically create a mapping between list fields and form fields that have the same name. This feature is designed to minimize the effort needed to create and implement a Dynamic List - see the example below.

The mappings can be edited by clicking the Dynamic list mappings property in the Lists group of the field properties; this dialog is also displayed when a Dynamic List is associated with a field and the system cannot create all default required mappings.

List display field: this is mandatory and allows you to select the list field which should be displayed to the user. The selection dropdown will include a list of all fields in the Dynamic List.

List return field: this is optional and specifies the value returned when a selection is made by the user. Specifying a return field allows you to display one database column to the user (the List display field) while retaining another column as the field’s value (the List return field). For example, if displaying a page containing employee details, department_id (containing a numeric id) might be the return field and department_name (containing the descriptive department name) might be the display field as shown below. If not specified, the display field is used as the return field.

Additional list fields: shows a list of all remaining fields from the Dynamic List. These can be mapped to form fields as required. Any fields shown as Required Mapping must be mapped.

· Required Mapping:	A read-only checkbox that indicates that the list field is marked as Required, meaning that the list cannot be built unless a value is provided (usually this will be because the field appears in the list’s WHERE clause)
· List Field:	All fields in the list omitting the fields selected as the display field and return field appear here
· Form field:	Click here to select a form field from the dropdown list – this creates a mapping
· List Field Description:	Shows the description of the list field as configured in the Dynamic List

The system will automatically create mappings for form fields and list fields with the same names, but these can be changed as required.

Please note that these mappings - more accurately, Dynamic List field mappings - should not be confused with the field mappings which are used to associate form fields with external resource fields, such as Database Resources, and are accessed using the Field Mappings icon in a form’s Resources View.

When are Dynamic Lists built at runtime?

When lists are built at runtime (i.e. loaded from database) is controlled by the When list is built property of the field with which the Dynamic List is associated. There are three options:

1. First display: the list is built when the field is first displayed. If the list contents do not change as a result of form processing, this option should be used. This is the default option.

2. Each display: the list is built each time the page is displayed, but with some exceptions – the list is not built when an error message is displayed or when a page is navigated to using the previous page button.

3. Never: the list is not built automatically by the system. The list should be built programmatically.

Regardless of which option is chosen, a list can be built programmatically using the API buildList() method (e.g. fields.LIST1FIELD1.list.buildList() ) or the FPL buildlist() or buildlistfromtable() functions.

For the first two options, the system will build the list after all event processing has been completed, and just before the page is displayed to the user.

If the list contents do not change as a result of form processing, option first display should be used. When the list contents do change as a result of form processing e.g. the form inserts or deletes database records or the list is dependent on another form field, there are two practical choices:

1. Use option first display, then use the buildlist() function when the list should be rebuilt. This will give the best performance as the list is only rebuilt when necessary.

2. Use option each display. This removes the need to use the buildlist() function so less program code is needed. However, the list will probably be rebuilt in circumstances where this is not strictly necessary, so this represents a performance overhead.

Dynamic Lists in tables

When a Dynamic List is associated with a table column, the system will normally only build the list once (subject to the rules above) and then share the list values between all rows in the tables. If you want the list to be built individually for each table row then the list must be dependent on another column in the same table e.g. if we are displaying an employees table with the following columns:

employee-name

employee-department

employee-section

The employee-section column might have a list showing the sections within the employee’s department. This list would contain a list field department and this is added to the list’s where clause and the field is marked as Required - meaning that it is required to build the list. This department list field is then mapped to the employee-department column. The list will then be built for each row.

How are Dynamic Lists built at runtime?

A Dynamic List is built at runtime as follows:

1. Any form field variables represented by && are substituted (See Adding variables to Dynamic Lists).

2. Checks if any form field variables required to build the list have no value – in this case the list is not built.

3. The SQL select statement is generated.

4. The select statement is issued to the database.

5. All “output fields” are extracted from the result set using the names specified for the list fields. An output field is a list field that is not marked as required, is marked as persistent and has a mapping to the list field in the form.

Creating and using a simple list - a worked example

Here are the steps to create a simple Dynamic List from scratch. The only assumption we have made here is that you have already created a database definition to access your database. This example uses all the labor-saving devices provided by the system:

Import a Database Resource from the database by:

1. From the menu at the top of the designer select Tools > Resource Wizards > Database Resource Wizard

2. Navigate to the table you want to use and select it

Build a Dynamic List by selecting the Database Resource you have just created:

3. In the Database Resource Editor, click the Build Dynamic List icon on the toolbar and complete the dialogs to create the Dynamic List

4. Create a new form

5. Drag the Database Resource into the Resources View in the form editor

6. Select the Fields View in the form editor, click the Import fields from External Resource icon , select the new Database Resource from the dropdown, then select all fields

7. Click on the form field where you want to display the list to display its properties – typically this field is the name or id of whatever is being displayed e.g. customer name, department name etc.

8. In the Presentation group of the field properties, set the Display Type property to Drop Down

9. In the Lists group of the field properties, set the Dynamic List property by selecting the list from the dropdown

When the field is placed on a page and the form is run, you will see the database table values displayed as a dropdown list.

Debugging Dynamic Lists

If your Dynamic List is not behaving as you expect, check the following:

1. Is the form field of type radio button, dropdown or checkbox? If not, the list won’t be shown.

2. Click Verify on the Dynamic List editor toolbar

3. Check the execution log (View > Execution log) to see if there are any errors. The system logs the situation where it is unable to build the list because of missing required fields or other reasons.

4. Check the debug option in the Dynamic List. This will log the SQL used to build the list to the execution log.

Restrictions

Please note that at present any LONG or BINARY database column types are not supported in Dynamic Lists.

Advanced uses

Using Native SQL

Native SQL is used when the Native SQL radio button is selected. When this option is selected, a select SQL statement can be entered manually.

The Build from assisted SQL button generates basic SQL from the Assisted SQL boxes. Note that this button will replace any existing SQL without warning.

Substitution of form field variables into the SQL follows the same rules as for Assisted SQL.

Creating 'linked' lists

In this section, we will show a worked example of two lists where the second list is dependent on the value selected in the first list. The two fields to be displayed to the end-user are COUNTRY and CITY. The COUNTRY field will show a list of countries and the CITY field will show a list of cities within the chosen country. As the end-user changes the selection of country, the cities list will automatically be re-built.

To support these lists, the database contains two tables COUNTRIES and CITIES. The cities table has 2 columns, COUNTRY and CITY.

Step 1: create the two Dynamic Lists

Here is the COUNTRIES list:

and here is the CITIES list:

Note that the WHERE clause includes a reference to the substitutable COUNTRY field.

Step 2: map the lists to a form

Shown below is an extremely simple form containing one page and just two fields COUNTRY and CITY; these fields are both configured with a display type of Drop Down.

The Immediate Validation property has been set for the COUNTRY field control on PAGE_1. This is a critical part of this exercise as it causes control to be passed to the Ebase Server when a selection is made from the COUNTRY list; the Ebase Server then builds the CITY list using the new COUNTRY value.

Here are the list mappings for the COUNTRY field:

and for the CITY field:

For the CITY list, the mappings have been built automatically by the system as the list field names are the same as the form field names.

That completes the building process. When we run the form, we see the COUNTRY field with a dropdown list of countries. The CITY field is initially blank - this is because at this stage, the system was unable to construct the CITIES list as the required field COUNTRY has no value. When we make a selection from the list of countries, the immediate validation on the COUNTRY field fires and the system tries again to build the CITY list, and this time is successful. The end-user will now see the country that has been selected and, below that, a dropdown list of the cities within that country. The same process is repeated when we select a different country from the first list.

Using Dynamic SQL

There may be occasions when you want to build up a SQL statement dynamically. For example, to implement a search application, you might set the where clause to a variable such as &&WHERE and then build this up programmatically in a script e.g.

FPL:

API based language (Javascript):

if [CITY != null]

set WHERE = WHERE + ' AND CITY = &&CITY';

endif

if [POSTCODE != null]

set WHERE = WHERE + ' AND POSTCODE = &&POSTCODE';

endif

fields.WHERE.value = "";

if (fields.CITY.value)

{

fields.WHERE.value += "AND CITY = &&CITY";

}

if (fields.POSTCODE.value)

{

fields.WHERE.value += "AND POSTCODE = &&POSTCODE";

}

To achieve this, the Dynamic SQL option on the WHERE list field is checked. When this option is checked, the system performs two substitution phases as follows:

1. Dynamic SQL statements are substituted. In the example above, “&&WHERE” is substituted with “… AND CITY = &&CITY AND POSTCODE = &&POSTCODE”

2. && variables are then substituted. In the example above, “… AND CITY = &&CITY AND POSTCODE = &&POSTCODE” is then substituted with “… AND CITY = LONDON AND POSTCODE = EC1A9ZZ”

WARNING!

Building a SQL statement dynamically can open the system to SQL Injection attack. To ensure that this is not possible, please follow these guidelines:

1. Always set any list fields that will contain dynamic SQL to use the Dynamic SQL option.

2. In the script that builds the SQL dynamically, include references to form field variables by referring to them as variables prefixed with && (as illustrated in the example above).

Do NOT write your code like this: (this might be susceptible to a SQL Injection attack)

FPL:

API Language (Javascript):

// DO NOT DO THIS!!

if [CITY != null]

set WHERE = WHERE + ' AND CITY = \'' + CITY + '\'';

endif

if [POSTCODE != null]

set WHERE = WHERE + ' AND POSTCODE = \'' + POSTCODE + \'';

endif

// DO NOT DO THIS!!

if (fields.CITY.value)

{

fields.WHERE.value += " AND CITY = '" + fields.CITY.value + "'";

}

if (fields.POSTCODE.value)

{

fields.WHERE.value += " AND POSTCODE = '" + fields.POSTCODE.value + "'";

}

Adding variables to Dynamic Lists

Values from a form can be dynamically substituted into any of the boxes used to create the SQL statement: select columns, select from tables, where clause, additional SQL clauses. A variable can be specified using two possible syntaxes &&VAR1 or &&{VAR1}.

&&VAR1 syntax: when using this syntax, replacement values for all character field types are enclosed in single quotes when constructing the SQL statement. e.g. : where EMPLOYEE_NAME = &&EMPLOYEE_NAME will be resolved as something like where EMPLOYEE_NAME = ‘SMITH’, whereas for numeric types where EMPLOYEE_ID = &&EMPLOYEE_ID will be resolved as something like where EMPLOYEE_ID = 12345 assuming that the resource field for EMPLOYEE_ID is a numeric type.

&&{VAR1} syntax: when using this syntax, replacement values are never enclosed in single quotes. This syntax can be used to concatenate two variables together, e.g. &&{VAR1}&&{VAR2} or when the quoting of variables is not required, e.g. LIKE ‘%&&{VAR1}%’ might be used in a WHERE clause to perform a search.

In both cases, the field variable name is interpreted as meaning a resource field name, and the value is obtained from the form field mapped to that resource field.