Partners
SQLite PHP Generator online Help
| Prev | Return to chapter overview | Next |
Setting a lookup editor
Lookup editors are used in case you want to select for a field a value that corresponds to a pre-defined value e.g. a record of another dataset. SQLite PHP Generator provides you with two types of lookup editors: Radio group or Combo box. Select Radio group as the column's Edit properties if the number of the column values is quite little.
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, SQLite PHP Generator enables a combo box lookup 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).
To create a lookup editor represented data of several columns, 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.
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). |
