IntroductionIn 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 RequirementJust 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|
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 Validation1. 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"
The HTML for the first Cap field is:-
<input id="f04_0001" type="text" value="0" maxlength="2000" size="2" name="f04"
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:-
FOR i in 1..apex_application.g_f03.count
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;
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'.