Choose your database:
AnySQL
MySQL
MS SQL Server
PostgreSQL
SQLite
Firebird
Oracle
SQL Anywhere
DB2
MaxDB

Subscribe to our news:
Partners

Firebird PHP Generator online Help

Prev Return to chapter overview Next

Lookup settings

Lookups are used in grids and on Insert and Edit forms as well. On List and View presentations lookups are used to display records of another dataset corresponding to and instead of values stored in the webpage base data source. On Insert and Edit presentations lookup editors are used to simplify input by selecting a value storing in another dataset and corresponding to a pre-defined value from the base one.

 

In data input forms the following  editors can be used for lookup controls: Autocomplete (default option), Radio Group, and Combobox. Here are some recommendations:

 

for lookup data sources containing a few (usually no more than 5-7) values, all editors can be used.
for lookup data sources containing no more than 15-20 values, both Autocomplete and Combobox editors can be used.
for lookup data sources containing 20+ values, the Autocomplete editor is recommended.

 

Setting a lookup editor bound to a data source (table, view, query)

If you create a PHP script for taking orders, the 'Orders' table will generally have a field hosting a number indicating the customer who made the order. Working directly with the customer number is not the most natural way; most users will prefer to work with customer names. However, in the database the customers' names are stored in a different table to avoid duplicating the customer data for each order by the same customer. To get around such a situation, you can enable a lookup editor:

 

check the Use lookup box;
select the foreign table/view/query as Data Source;
specify the field with the same data as Link field;
set the field with data to appear in the lookup editor as Display field;

 

By default, Firebird PHP Generator enables an Autocomplete editor for a column linked by a foreign key with a single column from another table (if Setup lookups by foreign key option is enabled).

 

Using Filter condition

Filter condition allows you to reduce the list of values represented in the lookup editor with a specified criterion. This condition corresponds to the WHERE clause applied to the data source (you must not add the WHERE keyword to beginning of the condition). The following operators can be used in this clause: =,<> (!=), >, <, >=, <=, BETWEEN, LIKE, IN.  It is also possible to use predefined variables like %CURRENT_USER_NAME%.

 

Example 1

To enable a lookup editor with a list of USA cities (the corresponding value of "country_id" is 103) named like Da*, specify the following condition: country_id = 103 AND city LIKE 'Da%'

 

Example 2

Suppose we have a table that contains a column 'owner' with owner information. To set a lookup editor with the list of values owned by the current user, specify the filter condition as follows: owner = %CURRENT_USER_NAME%

 

NB. The Filter Condition property cannot be used to implement dependent lookups or another similar logic. Only pre-defined variables and constant expressions are allowed.

 

Setting a lookup editor represented data of multiple columns

To create such lookup editor, create a query with all the necessary data concatenated into a single column and specify the query as Data Source. A complete example can be found below.

 

Example

Suppose we have three tables: 'employee' with a list of office employees, 'job' with employees' job titles and salaries, and 'department' with a list of office departments.

 

See definitions here

 

CREATE TABLE employee (

  EMP_NO       integer NOT NULL PRIMARY KEY,

  FIRST_NAME   varchar(15) NOT NULL,

  LAST_NAME    varchar(20) NOT NULL,

  JOB_CODE     integer NOT NULL,

  DEPT_NO      integer NOT NULL

); 

 

CREATE TABLE job (

  JOB_CODE     integer NOT NULL PRIMARY KEY,

  JOB_TITLE    varchar(25) NOT NULL,

  SALARY       real NOT NULL

  );

 

CREATE TABLE department (

  DEPT_NO     integer NOT NULL PRIMARY KEY,

  DEPARTMENT  varchar(20) NOT NULL,

  HEAD_DEPT   integer,

  MNGR_NO     integer,

  BUDGET      real,

  LOCATION    integer,

  PHONE_NO    char(20)

); 

 

 

To enable a lookup editor for the 'HEAD_DEPT' field of the 'department' table representing first name, last name, and job title of the employee, follow the steps above with the following query text:

 

SELECT

  e.EMP_NO as ID, 

  CONCAT(e.FIRST_NAME, ' ', e.LAST_NAME, ', ', j.JOB_TITLE) as FULL_NAME

FROM employee e,

     job j

WHERE e.JOB_CODE = j.JOB_CODE

 

 

Setting a lookup editor bound to a custom value list

To create a lookup editor bound to a custom value list i.e. to a list of values that are not stored in a database table and cannot be retrieved by a query, process as follows:

 

Click the ellipsis button next to the "Edit properties";
Enter the list of the allowed values in pairs an_allowed_value=value_to_be_represented separated by a comma (Example: 1=One,2=Two).

 



Prev Return to chapter overview Next