SQL Maestro Group / Products / MS SQL Server / MS SQL PHP Generator / Documentation

MS SQL PHP Generator online Help

Introduction

Lookups are used in data grids and in data input forms. For read-only views (List, Single Record View, Print, etc) lookups are used to display records of another dataset corresponding to and instead of values stored in the webpage base data source. For Insert and Edit forms 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: Dynamic Combobox (default option), Radio Group, Combobox, Cascading Combobox, and Dynamic Cascading 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 Dynamic Combobox and Combobox editors can be used.

•	for lookup data sources containing 20+ values, the Dynamic Combobox editor is recommended.

•	to allow users select the desired value step-by-step (for example, user selects a country and then a city in this country), Cascading Combobox or Dynamic Cascading Combobox can be used.

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

If you create a PHP script for taking orders, the 'Orders' table generally includes a field hosting a number indicating the customer who made the order. Working directly with the customer numbers 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 the data to be appeared in the lookup editor as Display field;

By default, MS SQL PHP Generator uses the Dynamic Combobox 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).

Add new item on the fly

Turn this option ON in order to allow adding new items directly in Insert and Edit forms. When this option is checked, a plus button is displayed on the right of the corresponding control (Live demo).

Click the ellipsis button to customize the modal dialog to be displayed when adding a new item. For example, you might want to hide some fields and/or customize the form layout.

Display lookup record

Turn this option ON in order to allow users to see the whole lookup record clicking on the corresponding cell in the data grid (live demo).

Click the ellipsis button to customize the modal dialog to be displayed when viewing the record. For example, you might want to hide some fields and/or customize the form layout.

Using Filter condition

Filter condition allows you to reduce the list of values represented in the lookup editor with a specified criteria. 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 predefined 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:

•	Select Combobox or Radio group in the Edit Properties line in the Insert/Edit group;

•	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).

MS SQL PHP Generator online Help

Lookup settings