Main page

Simple Master-Detail for Employees/Projects

Description

This example shows how to implement a master-detail page consisting of grid, record and editable grid forms. The grid form lists all the 'master' records while the record form and editable grid display detail information for a selected master record.

Usage

To add a new employee:

  1. Click the 'Add New' link at the bottom of the employee list.
  2. Enter the employee name, title and select a department then click the 'Add' button.
  3. In the 'Employee Projects' grid, select the projects to be assigned to the employee and enter the % allocation for up to three projects.
  4. Click 'Submit' to save the information.

To modify an employee's information:

  1. Select an employee by clicking on his name in the employee list.
  2. Modify the employee information and click the top 'Submit' button, or change the project assignment and click the bottom 'Submit' button.

Steps to recreate

  1. Create a HTML table with two columns.
  2. In the left column cell of the HTML table, use the Grid Builder to create the 'Employee' Grid based on the employees table with two columns: emp_id and emp_name.
  3. Convert the emp_name field to a link that points to the current page and passes an emp_id output parameter. Use the Href Source property to configure the link and the output parameter.
  4. In the bottom row of the Grid, add a Add New Link control and set it's Href Source property to the current page and add the text emp_id into the Remove Parameters property.
  5. In the right column cell of the HTML table, use the Record Builder to create a record maintenance form based on the employees table with the following three fields: emp_name, title and department_id.
  6. Convert the 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.
  7. In the right column cell of the HTML table and below the record form, use the Editable Grid Builder to create the editable Grid for record maintenance based on the projects_employees table with the following three fields: emp_id, project_id and percent_allocation.
  8. In the Data Source property of the editable grid, add a Where parameter of the form: em_id equals(=) emp_id with the Type of the Parameter source set to URL and the Default Value set to -1.
  9. Convert the emp_id TextBox to a Hidden control.
  10. Convert the project_id TextBox to a ListBox. For the ListBox set the Connection property to IntranetDB and select projects in the Data Source property. Select project_id in the Bound Column property, project_name in the Text Column Property and Integer in the Data Type property.
  11. Add the appropriate programming code into the  Before Show, After Insert and Before Delete events of the Record form, the Before Build Insert event of the editable grid and the OnValidate event of the project_id field in the editable grid, as shown in the example.
  12. Make cosmetic changes as needed, by modifying the text and captions within the HTML.

Database Tables used

Database: Intranet

Tables: projects, employees, projects_employees

Programming Notes

The record form's Before Show event is used to hide the editable grid if the form is not used for editing an existing record.

The record form's After Insert event is used to retrieve the last inserted key (emp_id) after a new record is added. The key is then sent over the URL by appending it to the 'redirect' variable as a URL parameter.

The record form's Before Delete event is used to update the 'project_employees' table after an employee record has been deleted.

The project_id field's On Validate event is used to ensure that the selected projects are unique.

The editable grid's Before Build Insert event is used to update the hidden 'emp_id' field with the current employee id received over the URL.