| Most likely, the Add Time Card page will be among the most frequently used aspects of the Time Card application. This page lets an employee 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 enteredsuch as the date, job description, and number of hoursan 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 [r5-6].
r5-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 returning 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 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. Because this SQL statement is a bit complex, we've included it as a snippet. To copy the SQL code to the clipboard, first place your cursor in a text string in Design view. Then, right-click (Control-click) the Recipes > TimeCards > SQL > Timecard - Employees snippet (PHP users should choose Timecard - Employees - PHP) and choose Copy Snippet from the context menu. Now, you're ready to return to the application page and insert the recordset. 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:
In MySQL, use:
| [View full width] 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 the new time card record is inserted, it 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 [r5-7]:
| <%= Request("ID") %>
|
| <%= Request("ID") %>
|
| <cfoutput>#URL.ID#</cfoutput>
|
| <?php echo $_GET["ID"]; ?>
|
r5-7. 
| 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 is one minor variation: The value stored in the hidden field (the Project ID) is inserted into the new record. 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, click the lightning bolt icon 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.
In the After Inserting, Go To field, select Browse and, from the Select File dialog, choose the report_hoursbreakdown page for your server model.
| 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. |
| 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 CardLogged to insert into the FORM.curDate form element as Date type. | | | 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 inColdFusion and Double type in PHP. | | | Set CardDescription to the FORM.TaskDescription form element as Text type. |
| 7. | In the After Inserting, Go To field, select Browse and, from the Select File dialog, choose the report_hoursbreakdown page for your server model.
| 8. | When you're done, click OK to close the dialog.
| 9. | Save the page.
|
|
