4 Replies Latest reply on Apr 14, 2014 1:14 PM by odysam

    Access the values of unchecked checkboxes in a report.

    odysam

      Hello all,

       

      I have a report to track attendance of students in classes.

      The report has values from many tables, including students, subject, class, class_meeting etc.

      The report includes all students registered in class and it has one checkbox field which is used to show if they were present or not at the specific class meeting.

      The checkbox is the student_id column and it is checked when the "present" field is 1 and unchecked when the "present" field is 0, it's implemented like this:

       

      apex_item.checkbox2(1, student_id, DECODE(present, 1, 'CHECKED', null))
      

       

      as per the api documentation.

       

      I have also created an on submit process which looks something like

       

      FOR I in 1..APEX_APPLICATION.G_F01.COUNT LOOP
        UPDATE class_meeting SET present = 1 WHERE student_id = APEX_APPLICATION.G_F01(i);
      END LOOP;
      

       

      So far so good. What I want to do is, let's say the professor opens the report again and unchecks one of the students and submits the page, how do I set "present" back to 0 for the unchecked student?

      The main question I guess is, how do you access the values of the unchecked checkboxes, with an on submit process.

        • 1. Re: Access the values of unchecked checkboxes in a report.
          Sunil Bhatia

          Hi,

           

          I am not able to understand why have you kept single checkbox for 2 use

          1. by your query it seems something like student_id) is stored in background

          2. attendence

           

          Don't you think more logical design would be to keep checkbox backend as student id for updating records, and another radio group/ LOV/ checkbox in the same report as attendence.

           

          So, now if your professor wants to remove attendence, he can uncheck the attendence checkbox and click save using primary key as 1st checkbox.

           

          Does it makes sense?

           

          Thanks,

          Sunil Bhatia

          • 2. Re: Access the values of unchecked checkboxes in a report.
            odysam

            If I understand correctly, you mean I should use something like APEX_ITEM.HIDDEN for the student_id column and then use a checkbox for the attendance column, so then I can iterate all the student ids using the hidden item and set the attendance according to the checkbox state for that row.

             

            So for example the query would be something like

             

            SELECT APEX_ITEM.HIDDEN( p_idx => 1, p_value => student_id) as "ID",
                         APEX_ITEM.CHECKBOX2( p_idx => 2, p_value => present, p_attributes => NULL, p_checked_values => 1) as "PRESENT"
            FROM CLASS_MEETING
            
            

             

            and the on submit process would be

             

            FOR I in APEX_APPLICATION.G_F01.COUNT LOOP
              IF APEX_APPLICATION.G_F02(i) = 1
                update class_meeting
                   set present = 1
                   where student_id = APEX_APPLICATION.G_F01(i);
              ELSEIF APEX_APPLICATION.G_F02(i) is null
                 update class_meeting
                    set present = 0
                    where student_id = APEX_APPLICATION.G_F01(i);
              END IF;
            END LOOP;
            
            

             

            Would something like this work? Or will the APEX_APPLICATION.G_F02 array have entries only for the checked checkboxes?

            • 3. Re: Access the values of unchecked checkboxes in a report.
              Tom Petrus

              This will fail. Unchecked checkboxes DO NOT submit a value to the session state. Eg 10 students -> 10 records in array f01. Checked the 3rd record -> 1 record in array f02 with value 1. Pretty useless.

               

              Keep the checkboxes with the PK as a value AND add a hidden item with the student ids. This way you can match the checkboxes up to the correct record.

              One way to deal with those other, now unchecked, checkboxes, would probably be to update the records to have present set to 0, and then update the records for which the checkboxes have been ticked.

              BEGIN
                -- set all students as not present before setting those who were
                update class_meeting 
                   set present = 0
                 where meeting_id = PX_MEETING_ID;  -- The meeting has a PK of its own I suppose.
                
                -- Array F01 = checkbox, student IDs
                FOR i in APEX_APPLICATION.G_F01.COUNT LOOP 
                  update class_meeting 
                     set present = 1 
                   where student_id = APEX_APPLICATION.G_F01(i); 
                END LOOP;
              END;
              

              Alternatively, loop over the student IDs, then in that loop see if you can match the ID with a record in the checkbox array. If you can: present, if you find none: not present.

              DECLARE
                l_found BOOLEAN; 
              BEGIN
                -- Array F01 = student IDs
                -- Array F02 = checkbox, student IDs
                FOR i in APEX_APPLICATION.G_F01.COUNT LOOP 
                  l_found := FALSE;
                  FOR j IN APEX_APPLICATION.G_F02.COUNT LOOP
                    IF APEX_APPLICATION.G_F02 = APEX_APPLICATION.G_F01(i) THEN
                      l_found := TRUE;
                      EXIT;
                    END IF;
                  END LOOP;
                 
                  IF v_found THEN
                    update class_meeting 
                       set present = 1 
                     where student_id = APEX_APPLICATION.G_F01(i); 
                  ELSE
                    update class_meeting 
                       set present = 0 
                     where student_id = APEX_APPLICATION.G_F01(i); 
                  END IF;
                END LOOP;
              END;
              
              • 4. Re: Access the values of unchecked checkboxes in a report.
                odysam

                That worked, thanks a lot.

                 

                However I feel APEX really should provide an easier way to get checkbox state for each row, instead of having to do all this extra work.