Min-/Max Value Validation in the Browser

How often have you already created an APEX validation to make sure that a money amount is greater or egal to 0.00, a percentage value is between 0 and 100 or that an entered date is less or equal to today?

There is some work involved doing that.

  1. You have to create a validation to make sure that the entered value is numeric.
  2. Create another validation which only fires if the previous one returned OK.
  3. Enter the check as PL/SQL expression or SQL Expression
  4. Think about a good error message

All that just to check that the amount is greater or equal to 0… Another drawback is that the user only gets this error message when he submits the page, so if you want to give him immediate feedback in the browser you have to do the same checks with JavaScript…

Hmmm, there has to be an easier and faster way to do that.

There is! :-)

I added a declarative range check for Page Items/Tabular Form Columns in the recent release of the ApexLib Framework.

You can try it on my Feature demonstration web-site.

It’s now possible to define the min-/max value for a field/column. This boundaries are checked immediately in the browser when you leave the field and for security reasons the same check is also done when you submit your page to the server.

You just have to put the ApexLib hints $APEXLIB_MIN_VALUE=min value$ and/or $APEXLIB_MAX_VALUE=max value$ into the Page Item comment or Link Attributes of Tabular Form Columns as you already do for other ApexLib hints.

What’s supported?

You can assign fixed values. Eg. $APEXLIB_MIN_VALUE=0$ $APEXLIB_MAX_VALUE=100$ to do a range check or use $APEXLIB_MIN_VALUE=01-JAN-2007$ to check against a fixed date.

But you can also make it more dynamic and reference other Application Items/Page Items. Eg. $APEXLIB_MAX_VALUE=&F111_TODAY.$ to check that the user doesn’t enter a date which is greater than today.

For Tabular Form Columns you can also use the #COLUMN_NAME# notation to reference a column cell value of the same row. Eg. $APEXLIB_MIN_VALUE=#SALARY#$

If you reference a page item/column on the current page, the min/max value changes when you modify the referenced field.

Note: The values specified, independent if they are fixed or dynamic have to comply to the format mask of the current item/column!!! That means, if your current date picker has DD-MON-YYYY as format mask, the fixed or referenced dynamic value also has to be of that format!

For our above example with the application item F111_TODAY, the corresponding initialization code would be a PL/SQL expression with the following code (we assume the used format mask is DD-MON-YYYY)

TO_CHAR(SYSDATE, 'DD-MON-YYYY');

What doesn’t work (yet)?

It’s not possible to enter an expression. For example to reference another field and add 1 day. eg. $APEXLIB_MIN_VALUE=&P4_VALID_FROM.+1$

But I think it’s a good start for now and can save some development work!

BTW, I have also update the “Set” configuration for the ApexLib hints in the case you are using the APEX Builder Plugin. You can find the files in the “BuilderPlugin” directory.

10 thoughts on “Min-/Max Value Validation in the Browser

  1. Hi,
    I am new to Oracle Appex,
    i would like to know validation of any String with Regualr Expression option [a-z,A-Z ]+. Only this character alows, When i m trying to use above pattern.its not working .
    Can u tell me the ways to do validation
    1) Using SQL expression, i can validate or not???
    2) Using pl/SQL expression, i can validate or not???
    3) Regular Expression, i can validate or not or not???

    Test Case ::
    1) efraer345 should be failed
    2)2342er should be failed
    3) #$%#$%sdfas34 should be failed
    4)rahul bhandari should be pass
    5) dfsd_` should be failed

    Could you please tell me ???

  2. Hello Duncan!

    You’re Apeylib is my dream come true! It’s extremely powerful as I can see. I successfully embedded it into my app and the check routines work on several pages. However, they don’t fire on other pages that have had custom validations before. Is there a way how I can force the Apexlib checks to run on these pages? Would be horrible to rebuild them?

    Would appreciate your reply as it is really urgent! Sorry to bug you!

    Best regards,

    Sebastian

  3. Sorry! Patrick! It’s too early this morning! Slightly misspelled your name! Sorry!

  4. Hi Sebastian,

    if you run the page in debug mode you should see more information why ApexLib doesn’t do something. A possibility is that you have conditions on your regions/page items/tabular form columns which would prevent ApexLib to do the validations. Because it doesn’t know if the region/… has actually been rendered.

    Regards
    Patrick

  5. Hi rahul,

    you do not have to post your question at more than one posting. One is enough.
    BTW, it’s always better to ask unrelated questions which don’t have anything to do with the blog posting at the OTN APEX forum (http://forums.oracle.com/forums/forum.jspa?forumID=137) where you will get a faster response.

    About your regular expression, try

    ^[a-zA-Z ]*$

    instead.

    Regards
    Patrick

  6. Hello Patrick!
    Thanks for your immediate reply! I’m still trying to figure out what is going wrong! For one specific page it outputs the error messages as produced by the Apexlib. (Didn’t work before at all on that page). However, it displays those errors on the seperate error page instead of using the header region.

    Do you have an idea what could be wrong?

    I’m really lost on this one and would appreciate your feedback!

    Regards,

    Sebastian

  7. Hello Patrick!

    I just figured out that certain flash charts seem to block the error handling! I created them from scratch and now it seems to work! By the way, if you happen to have an example on how to check duplicate entries (best with Apexlib) please be so kind and let me know.

    Take care and thanks for your effort,

    Sebastian

  8. Hi Patrick

    i am facing one problem in the self service validations. that i need to validate the one filed containg the Amount Requesting. and i wrote one package and deployed in the oracle standard package fnd_flex_plsql.. and here come my problem.. that is it is validating in the backend.. but not taking place in the SSHR pages..

    if the person is eligible for the loan.. then it will give true in backend. if he is not eligible then it gives false.. so my package is giving as per my requirement. but when i deployed in the standard package.. it is not validating as per my requirement..

    can anyone help me out..

    Thanking you in advance.

  9. plz give me code or syntax for unique date validation.
    if one entry fill from one date then no one fill entry with that date.

Comments are closed.