Main page

Using a Multi-Column Primary Key

Description

This example shows how to implement a sample record form based on a table that uses a multi-column primary key.

Usage

To assign projects to an existing employee, select a project from the list to the left then specify the project and the employee and enter a percentage allocation value. Click Submit when finished.

To create a new record, click Add New, select the Project and Employee, enter the Percentage allocation value then click the Add Button.

Steps to recreate

  1. Create a HTML table with two columns.
  2. In the left cell of the HTML table, use the Grid Builder to create the Projects Grid and the corresponding Search form.
    In Step 2 of the Grid Builder, click on the Build Query button and select the following tables: projects, employees and project_employees. Select [*] (all fields) in the projects_employees table, the project_name field in the projects table and emp_name field in the employees table. Set the Order by property to project_name.
    In step 3 of the Grid Builder,check the Create Search/Filter checkbox and select two fields: project_id and emp_id.
    In Step 4 of the Grid Builder select No Sorting.
  3. In the Search form, convert the s_project_id and s_emp_id Text Boxes into List Boxes.
    For the s_project_id List Box, set the Connection property to IntranetDB and select projects in the Data Source property, project_id in the Bound Column property, project_name in the Text Column Property and integer in the Data Type property.
    For the s_emp_id List Box, set the Connection property to IntranetDB and select employees in the Data Source property, emp_id in the Bound Column property, emp_name in the Text Column property and integer in the Data Type property.
  4. In the Grid form, convert the project_name Label to a Link. Set the Href Source property of the Link to the current page and add a Link Parameter with the following properties: Data Source Type = DataSource Column, Parameter Source = emp_id and Parameter name = emp_id. Then add another Link Parameter with the following properties: Data Source Type = DataSource Column, Parameter Source = project_id and Parameter name = project_id.
  5. In the bottom row of the Grid, add a Add New Link control and set it's Href Source property to the current page.
  6. In the right cell of the HTML table, use the Record Builder to create a 'projects_emp_rec' Record form with the following two fields from the projects_employees database table: project_id and Percent Allocation.
    In Step 2 of the Record Builder select List Box in the Control Type field for the project_id field.
    In Step 3 of the Record Builder, check the following options: Allow Insert, Allow Update, Allow Delete and Allow Cancel.
  7. In the Data Source property of the Record form, add a Where parameter with the condition: project_id equals(=) project_id and set the Parameter Source Type to URL.
  8. For the project_id List Box, set the Control Source property to project_id, the Connection property to IntranetDB and select projects in the Data Source property, project_id in the Bound Column property, project_name in the Text Column property, integer in the Data Type property and Yes in the Required property.
  9. Insert a new row into the record form (select "Insert row" from the context menu which appears for the mouse right-click) below the row with the project_id control.
  10. In the left cell of the new row, enter the text 'Employee' then in the right cell, add a ListBox control called emp_id.
  11. For the emp_id List Box, set the Control Source property to emp_id, the Connection property to IntranetDB and select employees in the Data Source property, emp_id in the Bound Column property, emp_name in the Text Column Property, integer in the Data Type property and Yes in the Required property.
  12. Add a Validate Maximum Value action to the On Validate event of the Percent_ allocation Text Box. Set the Control Name property of the action to Percent_allocation, the Maximum Value property to 100 and enter the following value into the Error message property: The value in the 'Percent Allocation' field cannot be greater than 100.
  13. Add the appropriate programming code into On Validate event of the Record form as shown in the example.
  14. 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 OnValidate event is used to make sure that an employee is not assigned to the same project twice.

The OnValidate event of the Percent_allocation Text Box is used to ensure that the value entered in not greater than 100.