As Published In May/June 2006

DEVELOPER: Browser-Based

Building Express Forms

By Raj Mattamal

Tune up your Oracle Application Express forms with JavaScript.

When developers talk about customizing Oracle Application Express applications, they usually mean server-side rather than client-side customizations—for example, directing users to a specific page based on a value submitted from a form. Oracle Application Express (formerly Oracle HTML DB) does provide many powerful features for customizing applications, but Oracle Application Express applications, like any other Web applications, can benefit from the appropriate use of client-side JavaScript to perform tasks such as conditionally rendering entry fields and validating data.

This column walks through the process of customizing a simple Oracle Application Express form, to demonstrate using JavaScript to accomplish several design goals best suited for client-side code, including 

• Activating (or deactivating) entry fields based on another field's value

• Displaying (or hiding) fields based on a selected option

• Validating data, such as the format of e-mail addresses, before submission to the server

• Populating fields with a default value, based on another field's value.

Step 1: Set Up the Tables

To get started, create (or have your Oracle Application Express administrator create) a new Oracle Application Express workspace. Once you've logged into the workspace, run the setup_simple_hr.sql script to create and populate the EMPLOYEES, DEPARTMENTS, and JOBS tables in your schema. To run the setup script

1. Click SQL Workshop .
2. Click SQL Scripts .
3. Click Upload .
4. Click Browse ; select the setup_simple_hr.sql script, available here ; and click Upload . In a second or two, you'll see the success message and the script's icon.
5. Click the setup_simple_hr.sql icon.
6. Click Run , and then click Run on the confirmation page. (You can ignore the warning on the confirmation page.)

Step 2: Create the Application

With all the database objects in place, generate the application:

1. Click the Application Builder tab.
2. Click Create .
3. Accept the default Create Application option, and click Next .
4. Enter EMPLOYEE MANAGEMENT as the name, and click Next .
5. Select Report and Form as the page type, enter EMPLOYEES as the table to use for the page name, and click Add Page .
6. Click Next to continue. Click Next again on each of the subsequent pages (tabs, shared components, attributes, user interface), accepting the defaults, to complete the wizard. (On the user interface page, select a theme other than the default— Theme 1 , if you like.)
7. Click Create on the last page (confirmation) of the wizard.

Step 3: Take a Trial Run

At this point, you can see the application in action by clicking the Run Application icon displayed on the success page. The new application is a simple report and form on the EMPLOYEES table. Give it a try:

1. Click the Detail icon (in the leftmost column of the report) to display details for that employee in the form page.
2. When you're finished viewing the form, you can return to the development environment by clicking the Edit Page 2 link in the developer toolbar at the bottom of the screen.

Step 4: Add JavaScript Functions

With the basic application now functional, you're ready to get started with client-side JavaScript customizations. Working with client-side JavaScript is generally a two-part process: adding the JavaScript function to the page header and then calling the function from the appropriate fields on the form page. The HTML Header section of the form's attributes page contains built-in tags: 

<script language="JavaScript" 
type="text/javascript">
<!--
...
//-->
</script>

It's a best practice to include these HTML comment tags (<!-- and -->) in the HTML page header section. It's between these tags that you'll add each of the JavaScript functions.

Enable or disable an entry field based on another field. Selectively enabling and disabling entry fields is a great way to enforce business rules and prevent errors from creeping into your data—it's also one of the most common uses for client-side JavaScript. In this application, you ensure that only employees working in the Sales department can earn commissions, by disabling the Commission Pct field for nonsales staff.

Add a JavaScript function that enables the Commission Pct field only if the employee is in the Sales department:

1. In the Page Definition view for page 2 (the application's form), click Edit Attributes to open the attributes page.
2. On the attributes page, scroll to the HTML Header section. Copy the code from Listing 1, and paste it into the HTML Header field between the JavaScript tags, just before the comment and closing script tag ( //--></script>). Support for the JavaScripts referenced in this column is available only via the Oracle Application Express Discussion forum on OTN. Additionally, the JavaScript functions prefixed with "html_" are available in Oracle Application Express 2.x.
3. To make the JavaScript function available when the page first loads, scroll to the On Load section, and enter 

onload="commission_Allowed();"

Code Listing 1: Enable/disable an entry field based on another field value 

function commission_Allowed(){
    //    Checks for nonsales department IDs (any P2_DEPARTMENT_ID not equal 
    //    to 80) and then disables P2_COMMISSION_PCT
    var lCheck = html_SelectValue('P2_DEPARTMENT_ID')!= '80';
    html_disableItem('P2_COMMISSION_PCT',lCheck);
}

into the Page HTML Body Attribute field.
4. Click Apply Changes .

Now call the function from the form's Department Id field:

1. In the Items section, click P2_DEPARTMENT_ID .
2. To enhance the user interface, render the departments as a drop-down (select) list. In the Name section, for Display As select Select List .
3. In the List of Values section, enter 

select department_name, department_id from departments order by 1

into the List of values definition field.
4. To call the commission_Allowed function as users change its value, set the onchange event handler property. Scroll to the Element section, and enter 

onchange="commission_Allowed()"

into the HTML Form Element Attributes field.
5. Click Apply Changes .

If you run the application now, you'll see that the Commission Pct field allows entry only when the department selected is Sales ; otherwise, the field appears dimmed.

Enable or disable a set of fields based on another field value. Now you'll customize the form so that several fields display only under certain conditions. Specifically, you'll display home information fields only if the employee works at home (denoted by Y or Yes in the P2_WORKS_AT_HOME field).

First, add the function to the application and configure it to initialize when the page is loaded:

1. Click Edit Attributes in the Page Definition view for page 2, if necessary, to open the attributes page.
2. On the attributes page, scroll to the HTML Header section. Copy the code from Listing 2, and paste it into the HTML Header field between the JavaScript tags, just before the comment and closing script tag ( //--></script>).
3. To initialize the page with the show_HomeData function when the page loads, scroll to the On Load section, and enter

Code Listing 2: Enable/disable a set of fields based on another field value 

function show_HomeData(){
  var lCheck = html_RadioValue('P2_WORKS_AT_HOME') == 'Y';
  // if employee works at home, show the home fields
  if(lCheck){
     html_ShowItemRow ('P2_HOME_OFFICE_PHONE');
     html_ShowItemRow ('P2_HOME_OFFICE_ADDRESS1');
     html_ShowItemRow ('P2_HOME_OFFICE_ADDRESS2');
     html_ShowItemRow ('P2_HOME_OFFICE_ADDRESS3');
     }
  else {
  // otherwise hide AND clear the home fields
     html_HideItemRow ('P2_HOME_OFFICE_PHONE');
     html_HideItemRow ('P2_HOME_OFFICE_ADDRESS1');
     html_HideItemRow ('P2_HOME_OFFICE_ADDRESS2');
     html_HideItemRow ('P2_HOME_OFFICE_ADDRESS3');
     html_GetElement('P2_HOME_OFFICE_PHONE').value="";
     html_GetElement('P2_HOME_OFFICE_ADDRESS1').value="";
     html_GetElement('P2_HOME_OFFICE_ADDRESS2').value="";
     html_GetElement('P2_HOME_OFFICE_ADDRESS3').value="";
     }
  }

onload="commission_Allowed();
show_HomeData();"

into the Page HTML Body Attribute field.
4. Click Apply Changes .

Now call the function from the field and make the Works At Home field easier to use, by rendering it as a yes/no radio group. When users toggle this field's value, the show_HomeData function gets called to selectively display and hide home-specific data.

To call the function and render Works At Home as a yes/no radio group:

1. Click P2_WORKS_AT_HOME .
2. In the Name section, for Display As select Radiogroup .
3. In the List of Values section, enter 2 into the Columns field.
4. To provide a static list-of-values syntax to allow users to select Yes or No , in the List of Values section, enter 

STATIC:Yes;Y,No;N

into the List of values definition field.
5. In the Default section, enter N in the Default value field so that in cases where this value is not specified in the database, the form will default to NO (and not display the home information fields).
6. Call the function when users change the value of this item. In the Element section, enter 

onchange="show_HomeData()"

in the HTML Form Element Attributes field.
7. Click Apply Changes .

If you run the application at this point, you'll see that the home data fields display only when the Works At Home radio group is set to Yes .

Validate client-side entries. Validating user input before it's sent to the server is one of the most basic tasks for client-side JavaScript. Here you validate new or modified e-mail addresses. To expedite the coding, I did a quick search on Google, found a JavaScript function that approximated what I wanted, and made a few tweaks (see Listing 3).

Code Listing 3: Validate e-mail addresses 

function valid_Email(email) {
        var dot=".";
        var at="@";
        var lat=email.indexOf(at) ;
        var lstr=email.length;
        var ldot=email.indexOf(dot) ;
        if (email.indexOf(at)==-1){
           alert("Email address is incomplete or invalid");
           return false;
        }
        if (email.indexOf(at)==-1 || email.indexOf(at)==0 || email.indexOf(at)==lstr){
           alert("Please provide a complete email address--name@domain.com ");
           return false;
        }
        if (email.indexOf(dot)==-1 || email.indexOf(dot)==0 || email.indexOf(dot)==lstr){
         alert("Please provide a valid email address--name@domain.com ");
            return false;
        }
         if (email.indexOf(at,(lat+1))!=-1){
            alert("Please provide domain for the email address entered.") ;
            return false;
         }
         if (email.substring(lat-1,lat)==dot || email.substring(lat+1,lat+2)==dot){
            alert("Please provide a valid email address");
            return false;
         }
         if (email.indexOf(dot,(lat+2))==-1){
         alert("Email addresses cannot have more than one at symbol \(@\)");
            return false;
         }
         if (email.indexOf(" ")!=-1){
            alert("Email addresses cannot have spaces.") ;
            return false;
         }
          return true;
    }

Now add the validation logic to the application:

1. Click Edit Attributes in the Page Definition view for page 2, if necessary, to open the Attributes page.
2. On the Attributes page, scroll to the HTML Header section. Copy the code from Listing 3, and paste it into the HTML Header field between the JavaScript tags, just before the closing script tag and comment ( //--></script>).

3. Click Apply Changes .

Now call the function:

1. In the Items section, click P2_EMAIL .
2. To call the valid_Email function whenever users are in this e-mail field on the form, set the onblur event handler property. In the Element section, enter this text 

onblur="valid_Email(this.value)"

into the HTML Form Element Attributes field.
3. Click Apply Changes .

If you run the form now, you'll find that invalid e-mail addresses aren't welcome any longer. (This JavaScript snippet validates standard e-mail format [name@domain.name], yet the e-mails that exist in the sample data resemble UserIDs [SHORTNAME] rather than e-mail addresses. That's why you're checking this field only for new or modified entries, using the onblur event handler.)

Populating fields with default values. Another great use of client-side JavaScript is filling form fields with default or other values. For the final customization, you use JavaScript to populate salary values (if the salary has not been specified) for two different clerical positions. Unless otherwise specified, the default salary for stock clerks is $45,000 and the default salary for shipping clerks is $50,000.

For this business logic, use two different functions: the Boolean function in Listing 4 uses JavaScript's regular expressions to evaluate the current object and determine if the value is empty; if it is, the function returns true. The second function, in Listing 5, checks the Boolean value in conjunction with the Job field (P2_JOB_ID); if the flag is set to true , the suggested_Salary function sets the salary field to a suggested salary for the job classification.

Code Listing 4: Return a Boolean value on a field 

function isEmpty (pThis) {
  var re = /^\s{1,}$/g; //white space, tab, form-feed
  if ((html_GetElement(pThis).value.length==0) ||
     (html_GetElement(pThis).value==null) ||
     ((html_GetElement(pThis).value.search(re)) > -1)) {
     return true;
  }
   else
  {
     return false;
  }
}

Code Listing 5: Check for empty field and job titles and then set salary 

function suggested_Salary(){
  //  if P2_SALARY is empty, then populate it with
  //  suggested values for the clerk positions
  if (isEmpty(html_GetElement('P2_SALARY'))) {
     if(html_SelectValue('P2_JOB_ID')== 'SH_CLERK'){
        html_GetElement('P2_SALARY').value = '50000';
        }
     else if (html_SelectValue('P2_JOB_ID')== 'ST_CLERK'){
        html_GetElement('P2_SALARY').value = '45000';
        }
  }
}

Now hook the code up to the rest of the form, and render the job titles as a drop-down list:

1. In the Items section, click P2_JOB_ID to open the Attributes page.
2. In the Name section, for Display As , select Select List .
3. In the List of Values section, enter the query 

select job_title, job_id 
from jobs 
order by 1

into the List of values definition field.
4. To call the suggested_Salary function, set an onchange event handler property on the field. In the Element section, enter 

onchange="suggested_Salary()"

into the HTML Form Element Attributes field.
5. Click Apply Changes .

If you run the form now, you'll see that the salary field for shipping clerks and stock clerks cannot be left blank but defaults to the appropriate recommended salary. You'll also note that once the field has been populated with either salary, changing to the other job does not change the salary value again.

Do More with Client-Side Javascript

These exercises demonstrate how easy it is to customize Oracle Application Express forms to enable client-side logic. Now you should experiment on your own with this approach. The Web has many JavaScript examples that make great starting points for your own code. Just copy the functions into your applications, tweak them to fit your needs, and enjoy the sophistication of client-side JavaScript coding in Oracle Application Express.