Hi all,
I want to create a validation on the column totals of an interactive grid. A requirement is that this also needs to be done without the use of Javascript.
The interactive grid:
This is the idea:
It is possible to add or delete more rows as desired (hours are booked per activity) and every cell can be updated to another numeric value (or null).
The total booked hours per day may not exceed 24 and I want to create a validation which checks that the column total per day is <= 24.
Users can update the values, and after pressing a save button, an error message should show up if one of the column totals turns out to be > 24.
For the validation I've tried a PL/SQL function returning a boolean, something like:
declare
ln_sum number;
begin
case :APEX$ROW_STATUS
when 'C' -- if the row is newly created, only check if the total is still <24 with the new value added
then
select nvl(sum(MONDAY),0) into ln\_sum
from TABLE;
if ln\_sum + nvl(:MONDAY,0) > 24 then
return false;
else
return true;
end if;
when 'U' -- Problem, right here. updating values.
then
select nvl(sum(MONDAY),0) into ln\_sum
from TABLE
where rowid \<> (:CURRENT\_ROWID);
if ln\_sum + nvl(:MONDAY,0) > 24 then
return false;
else
return true;
end if;
end case;
end;
With MONDAY as the associated column. But this of course doesn't work for updated values because the table is being referenced with the old values.
The problem I have is that when I'm updating the values, I can't seem to find a way to get the column total of the new values I just typed in but haven't processed yet.
I know something similar was possible with tabular forms using a loop:
DECLARE
v_val NUMBER :=0;
v_tot NUMBER :=0;
BEGIN
FOR i IN 1 .. APEX_APPLICATION.G_F06.COUNT
LOOP
-- This will make sure valid numbers are being specified
SELECT TO\_NUMBER(APEX\_APPLICATION.G\_F06(i)) INTO v\_val FROM DUAL;
v\_tot := v\_tot + v\_val ;
END LOOP;
IF v_tot != 100
THEN
RETURN 'Percentage values must total 100.';
END IF;
EXCEPTION
WHEN INVALID_NUMBER THEN
RETURN 'Please enter a valid number for all records.';
END;
(source https://software.databasedevelop.com/article/12304935/Validate+column+total+in+tabular+form )
This is not available for IG's.
Is there a way to validate column totals in an Interactive grid in APEX without using Javascript?
I want it to be so that the user can update the values, and when the save button is pressed, the validation goes off and checks if the column totals are <= 24.
Your help is greatly appreciated.
Cheers!
P.S.
APEX version: Application Express 5.1.4.00.08
DB: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0
Browser: Google Chrome Version 78.0.3904.97 (Official Build) (64-bit)