Main page
Pop-up List & Smart Lookup
Description
This example shows an implementation of a pop-up list for selecting values
and a smart lookup for quick data entry. The example is based on two tables:
departments and employees where users assign a manager to
each department. Although a simpler solution may be to let users select a manager
using a List Box, this becomes impractical when a large number of records is
involved. For example we may not want users to scroll through 100 managers in
a listbox. Instead, the pop-up list can filter the records displayed by using
a keyword search as a faster way to find the appropriate manager. The lookup
further enhances the process by allowing the user to specify partial information
when searching for a manager i.e. the managers initials, first or last name
or a partial name.
Usage
- Click on the Add New Link to add a new department.
- Enter a department name into the Department field.
- Enter Ken Price or K P or Ken or Price
into the Manager field and click on the Add
Button. If exactly one matching user is found then the record will be inserted,
otherwise an error message will be shown.
- Alternatively, click on the Employees List link
to open the pop-up window where you can select from or search a list of employees.
After selecting an employee, click on the Add button
to save the new record.
Steps to recreate
Page 1 (PopUpList):
- Create a new page and create a table with 2 columns.
- In the left column of the table, use the Grid Builder to create the
Grid based on the departments and employees tables which are located in the
Intranet database and their two columns: department_name and emp_name.
On Step 2 of the Builder use the Build Query option to select 2 tables:
departments and employees, delete a link between
departments.department_id and employees.department_id fields
and set the Left Join relation between department_manager_id and emp_id fields,
then select the department_id and department_name fields from the departments table plus
the emp_name field from the employees table.
On Step 4 of the Grid Builder select "No Sorting".
- Convert the department_name field to a Link, set the Href Source property to the current page
and add a link parameter with the following properties: Data Source Type = DataSource Column,
Parameter Source = departments_department_id
and Parameter name = department_id.
- Below of the Grid, add a Add New Link control and set its
Href Source property to the current page and enter the
department_id text into the Remove Parameters property.
- In the right cell of the HTML table, use the Record Builder to create the
'departments' record based on the departments table and its two columns: department_name and department_manager_id.
- Enter department_id in the Remove Parameters property of
the Record form.
- In the Record form, set the Data Type property of the department_manager_id
field to Text.
- In the bottom row of the Record form, add the Hidden control with the name
is_change.
- Also add the Employees List Link next to the department_manager_id
field.
- Select the PopUpList_Window page in the Href
Source property of the Link and set the Preserve Parameters
property to None.
- Click on the Format tab of the Properties
window and enter EmployeesList in the id property.
- Switch to HTML mode and add the OpenPop_UpList() JavaScript function as shown in the
example.
- Under the Format tab of the Properties window,
add the On Click event for the Employee List link to: OpenPop_UpList();return
false;
Note that the On Click event appears under the Events section of the
Format tab.
- Add the Custom code to the On Change client event of the
department_manager_id field as shown in the example.
- Add the appropriate programming code in the Before Show and
OnValidate events of the departments
Record form as shown in the example.
- Make cosmetic changes as needed, by modifying the text and captions within
the HTML.
Page 2 (PopUpList_Window):
- Use the Grid Builder to create the 'employees' Grid and Search forms based on
the employees and departments tables and their three columns:
emp_name, department_name and title.
On Step 2 of the Builder use the Build Query option to select 2 tables:
employees and departments.
On step 3 of the Grid Builder, check the Create
Search/Filter checkbox and select two fields: emp_name and departments.department_id.
On Step 4 of the Grid Builder select "No Sorting".
- Convert the s_department_id TextBox to a ListBox. For the ListBox set
the Connection property to IntranetDB and select departments
in the Data Source property. Select department_id in the Bound
Column property, department_name in the Text Column Property
and Integer in the Data Type property.
- In the Data Source property of the grid, remove a Where parameter: emp_name like(%s_emp_name%).
- At the top of the page, add the Close Window Link
control.
- Under the Format tab of the Properties window,
add the On Click event for the link to: window.close();
- Switch to HTML mode and add the SetOpenerValue()
JavaScript function as shown in the example.
- In the Grid form, change the emp_name Label control
to Link.
- Under the Format tab of the Properties window,
add the On Click event for the emp_name link
to: SetOpenerValue(this);return false;
- Add the appropriate programming code in the Before Show and
Before Build Select events of the Grid as shown in the example.
- Make cosmetic changes as needed, by modifying the text and captions within
the HTML.
Database Tables used
Database: Intranet
Tables: employees, departments
Programming Notes
The Record form's Before Show event is used to lookup a manager's
name based on the manager id.
The Record form' On Validate event is used to find a manager
based on search criteria or display an error message if no record or more than
one record is found.
The Grid form's Before Show event is used to hide the Navigator
control is there are less than two pages of records to navigate.
The Grid form's Before Build Select event is used to modify
the Where clause of the executed SQL statement to include any specified search
criteria.