0

APEX – Tabular Form Validation Issues/Bugs

Today seems to be going from bad to worse. Not only am I fighting a number of IE6 issues with APEX and CSS I’ve run into a couple of issue with tabular form validations in APEX 4.1.

Please Note: the workarounds which I’m about to describe in this post are not supported by the APEX team and that they may not work in future versions of APEX. Please proceed with caution. I take no responsibility for any negative outcomes from this.

Problem 1

The first issue I encountered occurs when I have line breaks in a text area in a tabular form and a tabular form validation error is raised. The javascript error displayed in the console on page load is “unterminated string literal”. To make matters a little more worse it stops all other dynamic action javascript from running which includes my page load mask, so the end result is a never ending page load mask.

To circumvent the problem I created a “PLSQL Function Returning Boolean” validation which runs after all others validations and strips the line breaks for the first two columns e.g.

BEGIN
  FOR i IN 1.. apex_application.g_f01.count LOOP
   apex_application.g_f01(i) := regexp_replace(apex_application.g_f01(i), '('||CHR(10)||'|'||CHR(13)||')','');
   apex_application.g_f02(i) := regexp_replace(apex_application.g_f02(i), '('||CHR(10)||'|'||CHR(13)||')','');
  END LOOP;
  RETURN TRUE;
END;

The point to note is that our validation always returns TRUE as we’re not actually validating anything, just running some PLSQL to strip the line breaks. I find this hack quite useful in certain situations, since we can’t use computations as they are bound to page and application items. In order to work out which f0x array you need to strip the EOL characters from, use firebug or an equivalent DOM inspector.

Now if you want to keep line breaks in your tab form columns and only strip them to avoid validation errors then set a condition on this validation to “Inline Validation Errors Displayed”.

Problem 2

The next issue I encountered was that POPUPKEY LOV’s display value was not set on validation error, however the actual value is. The workaround again is to use the same technique as described above with a little more code and after creating a protected hidden page item to hold the javascript which will run on page load and set the popupkey display values:

BEGIN
'';
  RETURN TRUE;
END;

Please Note: in the above code there is an intentional space between the colon and item name because wordpress is replacing these with smilies.

In the above PLSQL, f04 is our display value for the popup key LOV and f03 is our hidden return value. We need to perform an extra step and do a lookup of the return value in the code. This is because the actual display value is a disabled input item and never submitted with the HTML form so we can’t access the f04 array in PLSQL (it’s part of the HTML spec). Also in your label lookup function make make sure you JSON escape the label value to avoid any javascript errors like the APEX one described in the first problem above.

The next step is that we create one more fake validation which is the last in out list (i.e. has the highest display sequence). It is a “PLSQL Function Returning Error Text” validation which has the following PLSQL:

RETURN v('P1_TABFORM_VALIDATION_FAILURE_JS');

This will append our javascript unescaped to the validation error messages. Again, ensure you set a condition on this validation to “Inline Validation Errors Displayed” as we only ever want to output the javascript when a validation failure has occurred. The reason why we use another fake validation is to ensure that the javascript is only outputted once and also so that the popupkey display value is always set for any validation failure. The other reason is that we cannot define javascript within page items and use substitution strings in the error messages, as APEX will escape them and they will appear as plain text in your error message.

In the end it’s a pretty heavy and ugly solution for something which is expected to be built-in within APEX, but the bright side is that APEX is still flexible enough for us to code workarounds when there’s bugs within the product.

1

Custom Tabular Form Validations

This is a follow up post to “Combining your item validations into one, without losing inline display”. Whilst most of the techniques I describe (present and past) are not officially supported, the point to note is that they simply exist because APEX currently does not provide the required functionality. The thing I like about APEX is that there is a lot of flexibility to interact with the engine during page rendering and processing, at the same time though we need to ensure that we do this in a safe and future proof manner, or at least be aware of the risks and properly document them.

Custom Tabular Form Validation

So the point of today’s post is to fulfil the requirement of creating complex tabular form validations without losing the benefits of inline display. Now I’m sure this will be coming in the next APEX 4.1 release, but as always I have an immediate requirement. So to fill the void in the meantime I’ve extended the “LOG_VALIDATION_ERROR” function defined in the earlier post to include support for tabular forms ( it’s simply a wrapper for code centralization purposes ).

-- ---------------------------------------------------------------------------
--
-- Procedure      : log_validation_failure
-- Author         : Matt Nolan
-- Private/Public : Public
-- Description    : Wrapper for logging validation failures in the APEX engine
-- ---------------------------------------------------------------------------
-- Revision History
-- Date            Author       Reason for Change
-- ---------------------------------------------------------------------------
-- 02 FEB 2011     M.Nolan      Created.
-- ---------------------------------------------------------------------------
PROCEDURE log_validation_failure
( p_item          IN VARCHAR2
, p_message       IN VARCHAR2
, p_location      IN VARCHAR2 DEFAULT 'INLINE_WITH_FIELD'
, p_column_id     IN VARCHAR2 DEFAULT NULL
, p_column_name   IN VARCHAR2 DEFAULT NULL
, p_row_num       IN VARCHAR2 DEFAULT NULL
)
AS

  l_app_id     VARCHAR2(10) := apex_application.g_flow_id;
  l_page_id    VARCHAR2(10) := apex_application.g_flow_step_id;
  l_item_id    apex_application_page_items.item_id%TYPE;
  l_index      PLS_INTEGER;

BEGIN

  apex_application.add_validation_error
  ( p_message                => p_message
  , p_error_display_location => p_location
  , p_page_item_name         => p_item
  , p_column_id              => p_column_id
  , p_column_name            => p_column_name
  , p_row_num                => p_row_num
  );  

  IF apex_application.g_debug THEN
    apex_debug_message.log_message('Custom Validation');
    apex_debug_message.log_message('...Item: '||p_item);
    apex_debug_message.log_message('...Message: '||p_message);
    apex_debug_message.log_message('...Location: '||p_location);
    IF p_column_id IS NOT NULL THEN
      apex_debug_message.log_message('...Column ID: '||p_column_id);
      apex_debug_message.log_message('...Column Name: '||p_column_name);
      apex_debug_message.log_message('...Row Number: '||p_row_num);
    END IF;
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    -- custom error handler goes here
    RAISE;
END log_validation_failure;

Here’s an example of how we would code a custom tabular form validation ( Note: its a PLSQL page level validation which always returns TRUE as we add the errors ourselves within the function):


FUNCTION mytabform_validation
( p_tabform_pk      IN APEX_APPLICATION_GLOBAL.VC_ARR2
, p_tabform_column  IN APEX_APPLICATION_GLOBAL.VC_ARR2
, p_column_id       IN VARCHAR2
, p_column_name     IN VARCHAR2
, p_message         IN VARCHAR2
) RETURN BOOLEAN
AS

  l_student_id        user_volts_reservation_day_vw.student_id%TYPE;
  l_reservation_id    user_volts_reservation_day_vw.reservation_id%TYPE;
  l_message           VARCHAR2(500);

BEGIN

  FOR i IN 1.. p_tabform_column.count LOOP

    l_pk_id := p_tabform_pk(i);
    l_value := p_tabform_column(i);

    IF NOT some_validation
          ( p_pk               => l_pk_id
          , p_value            => l_value
          , p_message_override => l_message
          )
    THEN
      log_validation_failure
      ( p_item        => NULL
      , p_message     => nvl(l_message,p_message)
      , p_location    => 'INLINE_WITH_FIELD_AND_NOTIFICATION'
      , p_column_id   => p_column_id||'_'||lpad(i,4,'0')
      , p_column_name => p_column_name
      , p_row_num     => i
      );
    END IF;
  END LOOP;
  --
  -- Always return true as we validate and add the error messages ourselves
  --
  RETURN TRUE;

EXCEPTION
  WHEN OTHERS THEN
    -- your custom error handler goes here
    RAISE;
END;

And here is how we’d call the validation, by passing in the column arrays which we are interested in validating along with the column_id of the field we are validating and its corresponding name. In this example I also pass through the tabular form primary key as well to use in table lookups during validation.

RETURN mytabform_validation
      ( p_tabform_pk     => apex_application.g_f02
      , p_tabform_column => apex_application.g_f03
      , p_column_id      => 'f03'
      , p_column_name    => 'STUDENT_ID'
      , p_message        => 'Student is booked on another course!'
      );

Note: the “column_id” and “g_f0x” array items can be determined by using firebug and inspecting the input elements in the tabular form on the page. Alternatively you could omit them and do a lookup of the APEX Data Dictionary within the validation based on the column name passed through to identify the required id’s.

If you need extra validation control on your tabular form but are uncomfortable with using the undocumented “ADD_VALIDATION_ERROR” procedure, then have a look at an alternative post Denes Kubicek wrote late last year. Otherwise I hope you find this information useful, as it’s helped me on my current project immensely.

Finally if one of the APEX development team members happens to read this ( not naming and shaming names :) ) a little inside knowledge of whether this will break in the next 4.1 release would be invaluable.

3

Combining item validations into one, without losing inline display

Page item validations, whilst very useful under normal circumstances can become a management nightmare if you have a large number of items defined on the page that require several validations per item. This is especially evident when you use repeating fields on the same page as per the below form design which I’m currently working on for a customer (in APEX 4.0).

Add to this: catering for conditional display of the items and conditional firing of the validations you’ve got yourself a big headache on your hands. Now I’m sure some of you are shaking your heads going, “No you can use a page level validation a write a single validation” instead of defining 3 or 4 validations per item for all items. The trade off though is not being able to associate your page level validation with an item. Now when you have 40+ items on a page and no highlighting of the actual item which raised the error, rest assured your end user is going to explode in a rage after submitting the page for the tenth time still wondering what the problem field is.

Now I’m not the first to complain about validation support, to be honest in their current form they are still quite usable, but as in life there is always room for improvement. Many have previously requested validation groups, dependencies etc. etc. I’d second those and perhaps also suggest an array page item, similar to how a tabular form field operates, we would then only need one item definition and one validation for a repeating field such as a number of dates as in the above screenshot.

Now for those of you, like myself, who can’t wait for enhancements… we can work around this issue with a little PLSQL and using an internal undocumented procedure ADD_VALIDATION_ERROR in APEX_APPLICATION (synonym translated to WWV_FLOW).

-- ---------------------------------------------------------------------------
--
-- Procedure      : log_validation_failure
-- Author         : Matt Nolan
-- Private/Public : Public
-- Description    : Wrapper for logging validation failure in APEX engine
-- ---------------------------------------------------------------------------
-- Revision History
-- Date            Author       Reason for Change
-- ---------------------------------------------------------------------------
-- 02 FEB 2011     M.Nolan      Created.
-- ---------------------------------------------------------------------------
PROCEDURE log_validation_failure
( p_item      IN VARCHAR2
, p_message   IN VARCHAR2
, p_location  IN VARCHAR2 DEFAULT 'INLINE_WITH_FIELD'
)
AS

BEGIN

  apex_application.add_validation_error
  ( p_message                => p_message
  , p_error_display_location => p_location
  , p_page_item_name         => p_item
  );  

  IF apex_application.g_debug THEN
    apex_debug_message.log_message('AF Custom Item Validation');
    apex_debug_message.log_message('...Item: '||p_item);
    apex_debug_message.log_message('...Message: '||p_message);
    apex_debug_message.log_message('...Location: '||p_location);
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    --logger.log_error('log_validation_failure','Item: '||p_item||' '||SQLERRM);
    RAISE;
END log_validation_failure;

What the above will do is add a validation error the “stack”, so it can be combined with existing standard item validations. Also when calling the above procedure I normally have my items in an array of records which I loop through and perform the custom validations which I have designed. Here’s an idea of the concept e.g.

FOR i IN 1.. page_items.count LOOP
  IF i < page_items.count THEN
    IF NOT validate_dates(v(page_items(i).item_name),v(page_items(i+1).item_name)) THEN
      log_validation_failure
      ( p_item      => l_item_name
      , p_message   => 'must be < Day '||to_number(page_items(i).seq+1)
      );
    END IF;
  END IF;

  IF NOT is_asset_available
        ( p_course_id         => p_course_id
        , p_date              => p_date
        , p_start             => p_date
        , p_finish            => p_date
        , p_no_students       => p_no_students
        , p_ins_server        => p_ins_server
        , p_no_students_srvr  => p_no_students_srvr
        , p_virt_classroom    => p_virtual_classroom
        , p_out_message       => l_msg
        )
  THEN
    log_validation_failure
    ( p_item      => l_item_name
    , p_message   => l_msg
    );
  END IF;
END LOOP;

I then create a single validation on the page with the type “Function returning Boolean” that has my custom validation function call and always returns “TRUE”. It is a very key point that our actual validation never fails as we actually populate the errors ourselves within the function call. The validation we define on the APEX page is merely a wrapper around the group of real validations which we’ve created in our custom PLSQL.

In addition to the above you may also want to check out an older post I wrote about updating the REQUEST value within your custom PLSQL. This can give you more control as to what validations actually fire when using the built-in condition in combination with the above technique and standard validations.

The end result is a single group validation with inline item error display which translates into reduced maintenance, more control, and de-cluttering of the page in the IDE (well in the old view). The point to note is that using this method may mean that the validations you define may potentially break when upgrading to future releases of APEX. That is if the development team change the current validation design. For me this is something I can live with as the benefits outweigh the risks. However it is something for you to note if you intend to follow this post. Note: if you read the comment posted by Patrick Wolf, you will see that using this method will be fine at least for the 4.1 release. The original code I posted populated the validation arrays directly, however I have ammended it to use the procedure “add_validation_error” after Patricks suggestion.

On the topic of validations I must thank Guido Zaleen for the Form Validator plugin which he posted on APEX-PLUGIN.com. This works extremely well and is very thorough in design. I love the fact that it even takes jQuery UI tabs on the page into account and will automatically tab-switch if the error is on a hidden tab at page render. The code is also easy enough to follow and add additional custom validations as well. If you’re looking for client side and AJAX validations don’t write your own, this plugin is perfect. Great work Guido.