2 Replies Latest reply: Mar 26, 2014 2:51 PM by C Patel RSS

    How to validate column value for each row in tabular form?

    C Patel

      I have a custom tabular form based on a collection and I'm interested in being able to validate the value entered by a user in any given row/column combination.  Conceptually, it is basically a grid-format time sheet, where each row represents a task, and each column represents a day of the week.

       

      Task Name

      03 / 24

      Mon

      03 / 25

      Tues

      03 / 26

      Wed

      03 / 27

      Thurs

      03 / 28

      Fri

      03 / 29

      Sat

      03 / 30

      Sun

      03 / 31

      Mon

      04 / 01

      Tues

      04 / 02

      Wed

      04 / 03

      Thurs

      04 / 04

      Fri

      04 / 05

      Sat

      04 / 06

      Sun

      Task ID
      Task A13416312345
      Task B436142123567890
      Task C22172245789
      Task D12345112389

       

      The user can enter hours worked for any given task, for any given day of the week.  So whenever the user inputs some hours, the form needs to validate whether that task still has a valid GL code for the current time period.  I started to go down the path of Javascript / AJAX for validating prior to submitting the form, but got stuck on how to reference values in other cells on the same row.  For example, the user enters some hours in tabular form cell f03_0010, the javascript needs to look up the "task id" found in f18_0010 (Task ID is actually a hidden column but always in f18_xxxx cell).  It should then pass the value of f18_0010 to checkWBS function (see below) which calls an application process using PL/SQL to verify whether the task id is still valid.

       

      function checkWBS(pITEM) {
      
        var get = new htmldb_Get(null, html_GetElement('pFlowId').value, 'APPLICATION_PROCESS=VALIDATE_TASK_CODE',0);
      
      
        get.addParam('x01',pITEM);
      
        var gReturn = get.get();
      
        return gReturn;
      }
      
      
      
      
      

       

      Having client-side validation would be nice, but alternatively, a page process which executes On Submit will also work.   I understand that I could achieve this by looping through each row using the syntax below, but since the columns can span a 2-week period (14 cells) what's the best way to go through each column without having to code for each day?  Are there any built-in APEX functions to make this easier?

       

      BEGIN
        FOR i IN 1..apex_application.g_f02.count  LOOP
      
         IF apex_application.g_f03.count > 0 THEN
              v_tot_hours := v_tot_hours + TO_NUMBER(NVL(RTRIM(apex_application.g_f03(i)),'0'));
         END IF;
      
        /* keep adding all 14 days to v_tot_hours */
      
        IF apex_application.g_f016.count > 0 THEN
             v_tot_hours := v_tot_hours + TO_NUMBER(NVL(RTRIM(apex_application.g_f016(i)),'0'));
         END IF;
      
      /* now validate if there are hours entered for the task during any day */
        IF v_tot_hours > 0 THEN
      -- do something here
      END IF;
      
        END LOOP;
      END;
      
      
      
      
      

       

      Hope this makes sense.  Thanks for any input!

       

      Oracle APEX 4.2

        • 1. Re: How to validate column value for each row in tabular form?
          Danny*D201

          JS/Jquery validations, say each cell you have a js call onChange="checkWBS(this);"

          everytime value changed in a cell, it calls this function

           

          function checkWBS(pITEM) {

          var taskID = $(pITEM).parent().parent().find("input[name='f18']").val();

          //Jquery saying to get the value of f18 in this row

          // $(pITEM) is the input box  <input>

          // $(pITEM).parent() is the cell object as <td>

          // $(pITEM).parent().parent() is the row object as <tr>

          if (taskID) { // if taskID is not empty

             var get = new htmldb_Get(null, html_GetElement('pFlowId').value, 'APPLICATION_PROCESS=VALIDATE_TASK_CODE',0);  

             get.addParam('x01',taskID);

             var gReturn = get.get();

             return gReturn;

          }

          return false; // the default is to fail the validation, unless it passes in the ajax call above

          }

           

          This should give what you need to continue.
          P.S, you might also want to validate the value in pITEM is numeric while you have it here, to avoid unwanted error/data when saving to DB.
          or force the cell to accept numeric input only using javascript.

           

          PLSQL validations, yes, you have to hard code the column g_fxx for each day column.
          I would change one thing
          IF apex_application.g_f03.count > 0 THEN 
          should be
          IF apex_application.g_f03(i) > 0 THEN  -- only add positive hours

          the apex_application.g_f03.count is irrelevant within the apex_application.g_f02 loop
          and make sure the value is validated before calling TO_NUMBER() function to avoid convertion error.

           

          hope this helped, good luck

          • 2. Re: How to validate column value for each row in tabular form?
            C Patel

            Thank you for your suggestion.  It worked great!  Sometimes you just don't know what to search for when you don't know the syntax or technical terminology!  I am a newbie with jQuery/JS/AJAX, so I'm glad you could point me in the right direction.   I already have functions to validate the numeric values, but thanks for pointing that out.   I will work on the PL/SQL validations as extra layer.   Thanks again!