End-User Recipe: Add Time Card

Most likely, the Add Time Card page will be among the most frequently used aspects of the Time Card application. This page enables the employee to attribute a number of hours to a particular project while performing a specific task. The projects are already selected when the Add Time Card link is chosen from the Project Summary page; both the employee name and the type of job are selected from a recordset-filled drop-down list. Once the rest of the information is entered such as the date, job description, and number of hours an Insert Record server behavior is used to store the new record.

Step 1: Implement Add Time Card Design

The layout for the Add Time Card design requires a form and various elements. In addition to the two drop-down lists previously noted, the design should include text fields for the date and hours as well as a text area for the task description and a submit button. A hidden form element is also needed to make sure the current project is properly identified when the record is inserted.

1. In the server model of your choice, create a dynamic page.

   In the TimeCards folder, locate the folder for your server model and open the add_timecard page from there.

2. Add a table to the content region of your page to contain the form elements for the application.

   From the Snippets panel, drag the Recipes > TimeCards > Wireframes > Add Time Card - Wireframe snippet into the Content editable region.

3. Insert the form and, within it, a two-column table. The table should include six form elements: two lists (Employee and Job Type), two text fields (Date and Hours), a text area (Task Description), a submit button (Add Task), and a hidden form element (ID).

   Place your cursor in the row below the words ADD TIME CARD and insert the Recipes > TimeCards > Forms > Add Time Card - Form snippet.

4. Save the file [r6-6].

   
   

   Figure r6-6.

   

   
   

Step 2: Add Database Components

You want to make sure hours are logged quickly and accurately. To help, our recipe calls for two recordsets to populate the drop-down lists, Employees and Job Type. When applied, these recordsets make sure that the time card record includes both an active employee and job type.

Whereas both data components are fairly simple to implement, the Employees recordset has a bit of a twist. Rather than just return the last name from the Employee table, this recordset returns a combination of both the first name and last name fields with the field alias, EmployeeName. A field alias follows the AS keyword in a SQL statement, like this for ASP and ColdFusion:

 SELECT EmployeeFirst & ' '& EmployeeLast AS EmployeeName 

In MySQL, the equivalent SQL is as follows:

 SELECT CONCAT(EmployeeFirst,' ',EmployeeLast) AS EmployeeName 

This SELECT statement concatenates data from the two fields with a space between and stores the result in the alias EmployeeName.

Let's start with the simpler of the two recordset declarations:

1. From the Bindings panel, choose Add (+) and select Recordset (Query).

2. In the simple Recordset dialog, enter an appropriate name.

   Enter JobTypes in the Name field.

3. Select the desired data source connection.

   Choose Recipes from the Connection (Data Source) list.

4. Choose the needed table.

   From the Tables list, select JobTypes (jobtypes for PHP).

5. Set the Columns option to Selected and choose only the necessary fields.

   Press Shift and select JobTypeID and JobType from the Columns list.

6. Make sure the Filter is set to None.

7. Keep the Sort option set to None and click OK to close the dialog.

8. Save the page after the recordset is inserted.

Now we're ready to add the Employees recordset to the page.

1. From the Bindings panel, choose Add (+) and choose Recordset (Query) from the list.

2. In the Recordset dialog, select Advanced.

3. Enter an appropriate name for your recordset.

   In the Name field, enter Employees.

4. Choose the connection for your data source.

   Select Recipes from the Connection list.

5. In the SQL area, enter the following code:

   

    SELECT EmployeeFirst & ' '& EmployeeLast AS EmployeeName FROM Employees ORDER BY Employees.EmployeeLast, Employees.EmployeeFirst 

   In MySQL, use:

   

    SELECT CONCAT(EmployeeFirst,' ',EmployeeLast) AS EmployeeName FROM employees ORDER BY employees.EmployeeLast, employees.EmployeeFirst 

6. Click OK to close the dialog and add the recordset.

Step 3: Data Binding Process

The next task is to bind the two recordsets created in the preceding step to the form list elements on the page; the procedure is the same for both. In addition, we'll also need to give our hidden form element a dynamically assigned value: the project ID passed in from the selecting URL.

We'll work on the Employee list element first.

1. Select the TaskEmployee list element.

2. From the Property inspector, select Dynamic.

3. In the Dynamic List/Menu dialog, select the Employee recordset from the Options From Recordset list.

4. In the Values field, choose EmployeeName.

5. In the Labels field, select EmployeeName.

   In this case, both the Values and Labels are set to the same data field.

6. Leave the Select Value Equal To Field blank.

7. Click OK once to close the Dynamic Data dialog and then again to close the Dynamic List/Menu dialog.

Next we'll bind the proper dynamic data to complete the Job Code list.

1. Select the ProjectJobCode list element.

2. From the Property inspector, select Dynamic.

3. In the Dynamic List/Menu dialog, select the JobTypes recordset from the Options From Recordset list.

4. In the Values field, choose JobTypeID.

5. In the Labels field, select JobType.

6. Leave the Select Value Equal To Field blank.

7. Click OK once to close the Dynamic Data dialog and then again to close the Dynamic List/Menu dialog.

The final bit of data binding to add involves the hidden form element. To make sure that, when inserted, the new time card record is associated with the correct project, we'll assign the ID value from the URL to the hidden element's value. This will enable us to pass that value on to the Insert Record server behavior in the next step.

1. Select the hidden form element.

   Choose the hidden form element named ID next to the JobType list.

2. In the Property inspector, enter the following code in the Value field [r6-7]:

   
   

   Figure r6-7.

   

   
   

   	<%= Request("ID") %>
   	<%= Request("ID") %>
   	#URL.ID#
   	<?php echo $_GET["ID"]; ?>

3. Save your page before continuing.

Step 4: Apply the Insert Record Server Behavior

One final operation remains to complete this page: adding the Insert Record server behavior. Although the server behavior setup is fairly straightforward, there are a couple minor variations. First, we'll insert the value stored in the hidden field (the Project ID) into the new record. Second, we'll pass that same value to the linking page so that a complete breakdown of the hours including the just-added ones is displayed for the current project.

Note

Another way for ASP coders to approach the same coding is to choose Request Variable from the Add button of the Bindings panel and enter ID in the dialog. Then, in the Property inspector, select the lightning bolt symbol and choose ID under the Request element in the Dynamic Data dialog.

For ASP

1. From the Server Behaviors panel, choose Add (+) and select Insert Record from the list.

2. In the Insert Record dialog, choose your data source.

   Choose Recipes from the Connection list.

3. Select the table you want to insert the record into.

   From the Insert Into Table list, choose TimeCard.

4. Set the page that you want to appear after the record is inserted with the ID argument previously passed from the URL.

   Enter the report_hoursbreakdown.asp?ID") + " path from your server model's folder for the After Inserting, Go To field.

5. Select the form on the current page from which to gather the record values.

   From the Get Values From list, make sure AddTimeCard is selected.

6. In the Form Elements area, match the form elements on the page to the fields in the data source table.

   Set TaskEmployee to insert into the CardEmployee column as Text type.

   Set TaskDate to insert into the CardDate column as Text type.

   Set TaskDescription to insert into the CardDescription column as Text type.

   Set TaskHours to insert into the CardHours column as Numeric type.

   Set ProjectJobCode to insert into the CardJobTypeID column as Numeric type.

   Set ID to insert into the CardProject column as Numeric type (Integer type for MySQL).

7. When you're sure your choices are correct, click OK to close the dialog and add the behavior.

For ColdFusion and PHP

1. From the Server Behaviors panel, choose Add (+) and select Insert Record.

2. In the Insert Record dialog, choose the current form.

   Select AddTimeCard from the Submit Values From list.

3. Select your data source from the list.

   Choose Recipes from the Data Source list.

4. Enter your username and password, if needed.

5. Select the table in the data source to insert into from the list.

   Choose TimeCard (timecard for PHP) from the Insert Into Table list.

6. Set the data source fields to their corresponding form elements.

   Make sure the CardID data column is set to be an unused Primary Key.

   Set CardEmployee to insert into the FORM.TaskEmployee form element as Text type.

   Set CardLogged to not get a value.

   Set CardDate to the FORM.TaskDate form element as Date MS Access type in ColdFusion and Date type in PHP.

   Set CardJobTypeID to the FORM.ProjectJobCode form element as Numeric type in ColdFusion and Integer type in PHP

   Set CardProject to the FORM.ID form element as Numeric type in ColdFusion and Integer type in PHP.

   Set CardHours to the FORM.TaskHours form element as Numeric type in ColdFusion and Double type in PHP).

   Set CardDescription to the FORM.TaskDescription form element as Text type.

7. In the After Inserting, Go To field, enter the following code:

   	report_hoursbreakdown.cfm?ID=#FORM.ID#
   	report_hoursbreakdown.php?ID"] . "

8. When you're done, click OK to close the dialog.

9. Save the page.