I want to make a list where when selected the table is edited according to whether the box is checked or not - Apex oracle

Question

Good day out for everyone

I am enhancing an application in apex oracle where I need to change the state of a table according to this selected the checkbox I am doing it in pl / sql with a report in this way.

    a.GRUPO,
    a.ESTUDIANTE,
    b.documento,
    b.programa,
    a.FECHA,
     b.nombrescompletos,
      apex_item.hidden(p_idx   => 1 ,
                        p_value => id_listado) ||
    apex_item.checkbox2(p_idx   => 2, 
                        p_value => id_listado,
                        p_attributes=>DECODE(asistencia,'Si', 'CHECKED',NULL) ) Asistencia
    
   
from LISTADO_BIEN_REPRESE_TB a join estudiantes_bien_represe_tb b on
a.estudiante = b.id_estudiante
where a.grupo = :P6_NEW_1 and a.fecha = :P6_NEW

Where I create two items of apex oracle to be able to create a list of students where the user makes the list call and marks the checkbox of whether they came or not, by checking each box it will give the button to "save list" and depending on whether the student's box is marked in my database there is a field called assistance where it will be updated with a yes if the check box and if not edit it and put no.

image where i have my students list

But by putting a conditional of the else in case this markup edits the table change the state to no, it seems that the conditional does not detect if it is marked or not and changes them to all not regardless of whether they are marked or not.

    if apex_application.g_f01(i) = apex_application.g_f02(j) then
      -- esta seleccionado
      update LISTADO_BIEN_REPRESE_TB
         set asistencia = 'Si'
       where id_listado = apex_application.g_f02(j);
       
     
    end if;
  end loop; 
end loop;

My problem is when I create the pl / sql and I mark the checkboxes and I give it to save I edit correctly the table of my database, but it is possible that on the part of my user is wrong and wants to remove that checkbox with the conditional that I currently have will not let edit my field assistance to no , try the conditional else but behaves in an erative manner and edits the fields as you want as if it does not detect if they are marked.

Thank you very much to the one who can help me understand why this happens.

Answer 1

The problem is that there is no easy way to figure out which records do not have the checkbox selected. An "ELSE" block does not solve this problem as I explained in the comments. I suggest you put apex_debug statements in your code to check what is in the 2 loops...

A workaround is to create an array of all the ids, then another one with the selected ids and finally exclude the selected ids from the all the ids - that gives you the unselected ones.

Here is an example of a report using checkboxes rendered with apex_item on the EMP sample table. The report has 3 columns rendered with apex_item: a checkbox, a text field and a hidden column.

Source:

select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO,
       APEX_ITEM.CHECKBOX(1,empno) checkbox,
       APEX_ITEM.TEXT(2,ename) some_text,
       APEX_ITEM.HIDDEN(3,empno) hidden_empno
  from EMP

This a page process that will increase the salary for the selected employees with 1 and decrease the salary of the unselected employees with 1 :

DECLARE
  l_emp_id_arr apex_t_varchar2;
  l_selected_emp_id_arr apex_t_varchar2;
  l_unselected_emp_arr apex_t_varchar2;
BEGIN
  -- push all emp_id values to an array
  FOR i IN 1..APEX_APPLICATION.G_F03.COUNT LOOP
    apex_string.push(l_emp_id_arr,APEX_APPLICATION.G_F03(i));
    FOR j IN 1 .. APEX_APPLICATION.G_F01.COUNT LOOP
      IF APEX_APPLICATION.G_F01(j) = APEX_APPLICATION.G_F03(i) THEN
       -- push all selected emp_id values to a 2nd array
       apex_string.push(l_selected_emp_id_arr,APEX_APPLICATION.G_F03(i));
      END IF;
    END LOOP;
  END LOOP;
  -- now remove the selected values
  l_unselected_emp_arr := l_emp_id_arr MULTISET EXCEPT l_selected_emp_id_arr;
  UPDATE emp SET sal = sal + 1 WHERE empno IN (SELECT column_value FROM table(apex_string.split(apex_string.join(l_selected_emp_id_arr,':'),':')));
  UPDATE emp SET sal = sal - 1 WHERE empno IN (SELECT column_value FROM table(apex_string.split(apex_string.join(l_unselected_emp_arr,':'),':')));
END;