Administrator Recipe: New Employee

The goal of the final page in the Employee Lookup application is to add an employee to the data source. But isn't that just a basic insert record operation? Although our implementation does use Dreamweaver's standard Insert Record server behavior, we've also added a bit of intelligence that ties this page into the rest of the application. As we saw in the New Job page, you can't hire an employee unless there is at least one job opening. If you try to add an employee to the data source and no job opening is available, this page redirects you to the New Job page and displays an error message.

Step 1: Implement Design

Building a static page with all the necessary form elements is the first step.

1. Create a basic dynamic page, either by hand or derived from a template.

   In the EmployeeLookup folder, locate the folder for your server model and open the new_employee page found there.

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

   From the Snippets panel, drag the Recipes > EmployeeLookup > Wireframes > New Employee - Wireframe snippet into the Content editable region.

3. Within the table, insert the form and necessary form elements for the new employee. If you follow our example for this page, you'll need five text fields: first name, last name, email, direct line, cell phone, and cubicle number. You'll also need a list element for the job listings as well as a Submit button.

   Place your cursor in the row below the words NEW EMPLOYEE and insert the Recipes > EmployeeLookup > Forms > New Employee - Form snippet [r2-17].

   
   

   Figure r2-17.

   

   
   

Step 2: Add Database Components

A single recordset is needed for this page to populate the Job Title list element. To develop the list of jobs, we'll use an Access view and nested SQL statement. The SQL statement reads as follows:

 SELECT * FROM JobsWithDepartments WHERE JobID NOT IN (SELECT EmployeeJob AS JobID FROM Employees) 

Essentially, with this SQL statement, the recordset displays all the JobID records (that is, all the jobs) that are not assigned to an employee.

Before you begin this step, you'll need to copy the SQL code from the appropriate snippet by right-clicking on Recipes > EmployeeLookup > SQL > New Employee Jobs RS snippet for your server model and then, from the context menu, choose Copy Snippet.

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

2. In the advanced Recordset view, enter a meaningful name for the query.

   Enter Jobs in the Name field.

3. Select your data source connection.

   Choose Recipes from the Connections (or Data Source) list.

4. ColdFusion users: Enter the username and password for the data source, if necessary.

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

   Paste the copied snippet into the SQL field by pressing Ctrl-V (Command-V).

   	SELECT * FROM JobsWithDepartments WHERE JobID NOT IN (SELECT EmployeeJob AS JobID FROM Employees)
   	SELECT jobswithdepartments.* FROM jobswithdepartments LEFT JOIN employees ON jobswithdepartments.JobID= employees.EmployeeJob WHERE employees.EmployeeJob IS NULL)

6. Verify your code and close the dialog to insert the recordset.

7. Save your page.

Note

The JobsWithDepartment view was previously used on the Update Employee page and is discussed in that section.

Step 3: Data Binding Process

On this page, only one form element the list needs to be populated dynamically.

1. Select the JobTitle list element.

2. From the Property inspector, select Dynamic to open the Dynamic List/Menu dialog.

3. In the Options From Recordset list, choose Jobs.

4. In the Values field, select JobID.

5. In the Labels field, choose JobAndDepartment.

6. Leave the Select Value Equal To field blank and click OK to close the dialog.

By previewing the page in a browser, you can check to see what job titles are currently available [r2-18]. You won't be able to use Live Data Preview because list elements are not populated within Dreamweaver in Preview mode.

Figure r2-18.

Step 4: Insert Record New Employee

Now it's time to apply the Insert Record server behavior.

Note

As noted before, the Insert Record dialog is different for ASP and ColdFusion server models and easiest understood when presented separately.

For ASP

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

2. In the Insert Record dialog, select your connection from the list.

   Choose Recipes from the Connections list.

3. Select the table in the data source to modify from the list.

   Choose Employees from the Insert into Table list.

4. Enter the path to the file you want the user to visit after the record has been updated in the After Inserting, Go To field.

   Choose Browse and locate the employee_results.asp file.

5. Choose the form to use.

   Select NewEmployee from the Get Values From list.

6. With the current form selected in the Get Values From list, set the form elements to their corresponding data source fields.

   Set the FirstName form element to the EmployeeFirst data column and submit as Text.

   Set the LastName form element to the EmployeeLast data column and submit as Text.

   Set the Email form element to the EmployeeEmail data column and submit as Text.

   Set the Phone form element to the EmployeePhone data column and submit as Text.

   Set the Phone2 form element to the EmployeeMobile data column and submit as Text.

   Set the JobTitle form element to the EmployeeJob data column and submit as Numeric.

   Set the CubeNumber form element to the EmployeeCube data column and submit as Numeric.

7. Verify your choices and click OK to close the dialog.

For ColdFusion and PHP

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

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

   Select NewEmployee 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 Employees (employees for PHP) from the Insert into Table list.

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

   Make sure the EmployeeID data column is set to an Unused Primary Key.

   Set EmployeeFirst to the FORM.FirstName form element and submit as Text.

   Set EmployeeLast to the FORM.LastName form element and submit as Text.

   Set EmployeeEmail to the FORM.Email form element and submit as Text.

   Set EmployeePhone to the FORM.Phone form element and submit as Text.

   Set EmployeeMobile to the FORM.Phone2 form element and submit as Text.

   Set EmployeeJob to the FORM.JobTitle form element and submit as Numeric for ColdFusion and Integer for PHP.

   Set EmployeeCube to the FORM.CubeNumber form element and submit as Numeric for ColdFusion and Integer for PHP.

7. Enter the path to the file you want the user to visit after the record has been inserted in the After Inserting, Go To field.

   Choose Browse and locate the employee_results file for your server model.

8. Verify your choices and click OK to close the dialog.

Step 5: Hand Code Redirect When No Job Openings

Our final step on this page and the entire application is to insert some custom code to properly react when no jobs are available. Because this redirect relies on the recordset on the page which returns a list of available jobs you can place the redirect on the page itself, where you can be sure the recordset code has already been defined. As noted earlier, should no jobs be available, the code redirects the visitor to the New Job page and triggers the error message previously embedded.

1. In Code view, place your cursor just above the opening <html> tag.

   Although you could place this code elsewhere for ASP and ColdFusion, PHP requires that the code appear directly after the recordset definition.

2. Insert the following code:

   From the Snippets panel, open the Recipes > EmployeeLookup > Custom Code folder for your server model and insert the No Job Openings - Display Text snippet.

   	<% if (Jobs.EOF) then Response.Redirect("new_job.asp?empty=true") end if %>
   	<% if (Jobs.EOF) Response.Redirect("new_job.asp?empty=true"); %>
   	<cfif Jobs.RecordCount EQ 0> <cflocation url="new_job.cfm?empty=true"> </cfif>
   	<?php if ($totalRows_Jobs<=0) { $url = "new_job.php?empty=true"; Header("Location: $url"); } ?>

3. Save your page.

The only way to test the redirection functionality is to preview the page in a browser when all the available jobs are filled. The recipe data source has a full slate of company employees for you to test your application with.