1 2 Previous Next 21 Replies Latest reply: Feb 21, 2013 12:50 AM by fac586 RSS

    APEX Form Validation help

    Ewaver
      Hi,

      I really appreciate your help ;)

      I have a simple form with the following columns:

      :P7_BILLING_START_DATE
      :P7_BILLING_END_DATE

      I want to create a simple validation where if someone enters a BILLING_END_DATE that is < the BILLING_START_DATE
      then 'End date cannot be less than end date' will return the error.

      If the BILLING_END_DATE IS LEFT blank or if the end data is > the BILLING_START_DATE then the form should submit as normal (i.e. either null or the end date).

      I've tried about every combination I can think of..the latest being..

      IF :P7_BILLING_END_DATE < :P7_BILLING_START_DATE AND :P7_BILLING_END_DATE IS NOT NULL THEN
      RETURN TRUE;
      ELSE RETURN FALSE;
      END IF;

      I have also tried conditions like "Value of Item / Column in expresion is NOT NULL to account for the BILLING_END_DATE.

      If I use this (below)..I get the right validation if I enter a date < the start date, but if I leave the end date blank it still gives me the error.

      IF :P7_BILLING_END_DATE < :P7_BILLING_START_DATE
      RETURN TRUE;
      ELSE RETURN FALSE;
      END IF;

      Thanks again for your much needed expertise,

      David
        • 1. Re: APEX Form Validation help
          scott.wesley
          Your validation should define the correct scenario, ie - if it returns TRUE, then all is good.

          Therefore, A common date validation might be
          :P1_eff_end >= :P1_eff_start OR :P1_eff_end IS NULL
          It gets me all the time... especially when the validation uses a NOT EXISTS -- too many double negatives ;-)

          Scott
          • 2. Re: APEX Form Validation help
            William Wallace
            Hi,

            I would do something like this. Validation type : SQL Query returns at least 1 row.
            select 1 from dual
            where
            case 
                 when :P7_BILLING_END_DATE is null then 1
                 When :P7_BILLING_END_DATE is not null and :P7_BILLING_START_DATE <= :P7_BILLING_END_DATE then 1
                 else 0
            end = 1
            Assuming your P7_BILLING_START_DATE is a mandatory field for this to work,

            If not, condition this validation with "item/value not null" with P7_BILLING_START_DATE
            • 3. Re: APEX Form Validation help
              scott.wesley
              Not sure how you find this cleaner than IF statements - it's an IF statement re-written within some SQL.

              I personally find PL/SQL expressions the simplest - something that evaluates to TRUE or FALSE, where the TRUE is what you need the data to be
              • 4. Re: APEX Form Validation help
                Ewaver
                Hi,

                Thanks both for your help. I tried this first with no success...

                BEGIN
                IF :P7_FTE_BILLING_END >= :P7_FTE_BILLING_START
                OR :P7_FTE_BILLING_END IS NULL THEN
                RETURN TRUE;
                ELSE RETURN FALSE;
                END IF;
                END;

                It works for End Date >= Start Date and for End Date = Null, but does not produce an error if the End date is < Start.

                I am going to try the SQL example now..but if you something wrong with my If statement let me know where I might be going wrong..

                Kind Regards,

                David
                • 5. Re: APEX Form Validation help
                  Ewaver
                  Hi,

                  I am not able to get either of these to work for some reason. Neither are catching the End Date < Start Date scenario.
                  Any ideas?

                  Thank You,
                  • 6. Re: APEX Form Validation help
                    fac586
                    Ewaver wrote:

                    I am not able to get either of these to work for some reason. Neither are catching the End Date < Start Date scenario.
                    Any ideas?
                    APEX item session state values are all of type <tt>VARCHAR2</tt>. This code:
                    BEGIN 
                      IF :P7_FTE_BILLING_END >= :P7_FTE_BILLING_START 
                      OR :P7_FTE_BILLING_END IS NULL THEN 
                         RETURN TRUE;
                      ELSE RETURN FALSE;
                      END IF;
                    END;
                    is performing a string comparison of P7_FTE_BILLING_END and P7_FTE_BILLING_START. This will return TRUE if P7_FTE_BILLING_END = '31/01/2013' and P7_FTE_BILLING_START = '04/02/2013'. When referencing APEX session state values as dates (or numbers) always perform explicit date (and number) conversions:
                    ...
                      if to_date(:P7_FTE_BILLING_END, 'DD/MM/YYYY') >= to_date(:P7_FTE_BILLING_START, 'DD/MM/YYYY')
                    ...
                      if to_number(v('P7_EXPENSES')) > 200
                    ...
                    I'm also with Scott in preferring PL/SQL Expression validations:
                       to_date(:p7_fte_billing_end, 'DD/MM/YYYY') >= to_date(:p7_fte_billing_start, 'DD/MM/YYYY')
                    or :p7_fte_billing_end is null
                    • 7. Re: APEX Form Validation help
                      Ewaver
                      Hi,

                      That must be the issue..thanks for the help. Any ideas why it is not working?

                      IF to_date(:P7_FTE_BILLING_END, 'DD/MM/YYYY') >= to_date(:P7_FTE_BILLING_START, 'DD/MM/YYYY')
                      OR to_date(:P7_FTE_BILLING_START, 'DD/MM/YYYY') IS NULL
                      THEN RETURN TRUE;
                      ELSE RETURN FALSE;
                      END IF;

                      I have a start date of 01-FEB-2013 and an End Date of 31-DEC-2012, which goes through the validation without an error...
                      Thanks,

                      Edited by: Ewaver on Feb 19, 2013 7:41 AM
                      • 8. Re: APEX Form Validation help
                        Ewaver
                        Hi again,

                        Ok this works for the End Date < Start Date now and also for the End date is null.

                        IF to_date(:P7_BILLING_END_DATE, 'DD/MM/YYYY') >= to_date(:P7_BILLING_START_DATE, 'DD/MM/YYYY') OR
                        :P7_BILLING_START_DATE IS NULL
                        THEN RETURN TRUE;
                        ELSE RETURN FALSE;
                        END IF;

                        Thanks to everyone for your help :)
                        • 9. Re: APEX Form Validation help
                          992011
                          But if you have an htmldb_application report, where you need to apply dats validations on the whole column, for example,
                          "Completion Date" cannot be in the future: you should run this validation through all the values in the column of "Completion Date that are not empty.

                          How to you write validation in this case?
                          • 10. Re: APEX Form Validation help
                            scott.wesley
                            That, 989008, is a question for a new thread.

                            My apologies for forgetting about explicit conversions - how naughty of me.
                            • 11. Re: APEX Form Validation help
                              Damir Vadas
                              Maybe this is more clear:
                              nvl(:P1_eff_end,(:P1_eff_start+1)) >= :P1_eff_start;
                              • 12. Re: APEX Form Validation help
                                fac586
                                Damir Vadas wrote:
                                Maybe this is more clear:
                                nvl(:P1_eff_end,(:P1_eff_start+1)) >= :P1_eff_start;
                                Do you want to re-read the entire thread to see why&mdash;subjective opinions on clarity notwithstanding&mdash;the fact this doesn't work is a serious drawback? Hint: ORA-01722: invalid number.

                                And, personally, no, I don't think it is more clear. Also, in a form that would actually work it would be much less efficient.
                                • 13. Re: APEX Form Validation help
                                  Damir Vadas
                                  Hi!
                                  We'll opinion may vary. OR parts in logical evaluation are always slower then function in kernel, like mine example do.
                                  rg
                                  damir
                                  • 14. Re: APEX Form Validation help
                                    fac586
                                    Damir Vadas wrote:
                                    Hi!
                                    We'll opinion may vary OR parts in logical evaluation are always slower then function in kernel, like mine example do.
                                    Can you substantiate that statement? What about when you add the 3 additional function calls required to actually make your example work in APEX?
                                    1 2 Previous Next