{Header}

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

  1. Click on the Add New Link to add a new department.
  2. Enter a department name into the Department field.
  3. 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.
  4. 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):

  1. Create a new page and create a table with 2 columns.
  2. 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".
  3. 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.
  4. 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.
  5. 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.
  6. Enter department_id in the Remove Parameters property of the Record form.
  7. In the Record form, set the Data Type property of the department_manager_id field to Text.
  8. In the bottom row of the Record form, add the Hidden control with the name is_change.
  9. Also add the Employees List Link next to the department_manager_id field.
  10. Select the PopUpList_Window page in the Href Source property of the Link and set the Preserve Parameters property to None.
  11. Click on the Format tab of the Properties window and enter EmployeesList in the id property.
  12. Switch to HTML mode and add the OpenPop_UpList() JavaScript function as shown in the example.
  13. 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.
  14. Add the Custom code to the On Change client event of the department_manager_id field as shown in the example.
  15. Add the appropriate programming code in the Before Show and OnValidate events of the departments Record form as shown in the example.
  16. Make cosmetic changes as needed, by modifying the text and captions within the HTML.

Page 2 (PopUpList_Window):

  1. 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".
  2. 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.
  3. In the Data Source property of the grid, remove a Where parameter: emp_name like(%s_emp_name%).
  4. At the top of the page, add the Close Window Link control.
  5. Under the Format tab of the Properties window, add  the On Click event for the link to: window.close();
  6. Switch to HTML mode and add the SetOpenerValue() JavaScript function as shown in the example.
  7. In the Grid form, change the emp_name Label control to Link.
  8. Under the Format tab of the Properties window, add the On Click event for the emp_name link to: SetOpenerValue('{emp_name}');return false;
    For .NET instead of the previous line add the following SetOpenerValue(this);return false;
  9. Add the appropriate programming code in the Before Show and Before Build Select events of the Grid as shown in the example.
  10. 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.