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
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?
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?
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;
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.