Oracle APEX - Advanced Tabular Form Validation

Introduction

In my previous APEX post I took you through a method of adding click-able column help to your tabular forms. I also needed to add validation between my columns, and initially it wasn't clear how I might do this. You can validate against static values, normal item values, or just check for nulls, but nowhere can you validate against a value from another column on the same row.

Tabular Form Requirement

Just to recap from before, I have a table of measures with three columns to hold the weighting, cap and uplift values for each. (shown below)

My Tabular Form

The validation I wanted to add was to ensure that if a Cap value is added then it must be greater than the Weighting value.

One way of performing this type of validation is to use some jQuery code which runs on the browser, but generally its not recommended for security reasons. Instead we'll use a method that replies on the inherent use of collections for tabular tables and write our validation using PL/SQL.

Creating the Validation

1. Run the page containing your tabular form and inspect the input fields from the columns your need to validate to get their id's.

The HTML for the first Weighting field is:-

<input id="f03_0001" type="text" value="0" maxlength="2000" size="2" name="f03"

autocomplete="off">

The HTML for the first Cap field is:-

<input id="f04_0001" type="text" value="0" maxlength="2000" size="2" name="f04"

autocomplete="off">

So the two pieces of information we need here is f03 and f04.

2. Create a page level validation and give it a suitable name. (I'm going to call mine "Check Cap Values Exceed Weighting")

3. Select 'PL/SQL' as the validation type.

4. Then select 'Function Returning Boolean'.

5. Here's the code I entered:-

BEGIN
  FOR i in 1..apex_application.g_f03.count
  LOOP
  IF apex_application.g_f04(i) IS NOT NULL
    THEN IF apex_application.g_f03(i) IS NOT NULL 
      THEN IF TO_NUMBER(apex_application.g_f04(i)) <= TO_NUMBER(apex_application.g_f03(i))
        THEN RETURN false;
      END IF; 
    END IF; 
  END IF; 
  END LOOP;
END;

NB. APEX automatically creates a collection for each column in a tabular form ("apex_application.g_f01", "apex_application.g_f02" etc...) This function loops through the values in columns 3 and 4 comparing the values. 

It's reasonably simple, just don't forget to turn these values into numbers before you do your comparison, or you'll find that 8 is greater than 10 and your validation won't work as you intended!

6. Enter the following for Error Message:-

Cap values must be greater than Weighting, or left empty.

7. Finally set the condition to When Button Pressed 'SUBMIT'.

That's it!