Skip to main content

Interactive Grid - Aggregate Validations

In Oracle APEX, validating Interactive Grid (IG) data at row level, is easy and straight forward. You just need to select "Editable Region" for the validation and then you can use IG column values using :COLUMN_NAME syntax in SQL and PL/SQL code. However, if you want to create a validation at table level or validation which uses multiple rows data, then it becomes tricky.

Let's consider a simple example of budget planning, where you can allocate percentage for each category, like 10% for healthcare, 10% for education etc. Here, we need to implement a simple table level validation to ensure total allocation % does not exceed 100.

Please refer Interactive Grid - Client Side Aggregate Validations for implementing this validation entirely on client side. You can use technique explained below for more complex validations that can't be done on client side (checking data from multiple tables, number of rows in IG are more than 50)


Before jumping into the implementation, let's review few points about IGs
  1. For IGs, when page is submitted or when you click default "Save" button, then only newly added rows and modified rows are submitted to server.
  2. Selecting default check-box (Column APEX$ROW_SELECTOR) will not change behavior of which rows are submitted to server. This check box selection is mainly for performing row actions enabled for IG (Column APEX$ROW_ACTION)
  3. When you use default "Save" button to update IG data into DB, then APEX won't submit entire page. APEX will make AJAX call and partially submits the page and processes new/modified rows of IG. However, all validations defined for the IG will still be executed. But, page level validations will not be executed (because whole page is not submitted).

Aggregate Validations: 

To perform above discussed validation, we need data added/modified by user in front-end UI + data which is already existing in DB, if any. 

Table Design: 

To understand code better, please assume table BLOG_BUDGET_PLANNING with below columns

PLAN_ID - Primary Key Column (GENERATED ALWAYS AS IDENTITY)
PLAN_CATEGORY - Budget Planning Category
PLAN_PERCENTAGE - Number column to store allocation percentage
+ Audit (Who) columns. 

Implementation: 

To start with, create IG region on table BLOG_BUDGET_PLANNING and enable edit mode. Next, create two hidden page items. One item, P11_SUM_PLAN_PERCENTAGE, to hold sum of "Allocation %" entered by user in front-end and another item, P11_PLAN_IDS, to hold list of primary key values for the rows modified by user. Please disable "Value Protected" option for both these page items. When "Value Protected" option is enabled, and if you modify item value using JS and submit page, then you will get an error. 

Create after submit page process "Aggregate P11_SUM_PLAN_PERCENTAGE" to calculate SUM of "Allocation %" entered by user for added/modified rows and to capture primary key values for rows modified. Selected "Budget Planning" IG as "Editable Region" for the page process.

Page Process Code:
-- aggregate plan percentage value for added/modified rows
:P11_SUM_PLAN_PERCENTAGE := COALESCE(:P11_SUM_PLAN_PERCENTAGE,0) + :PLAN_PERCENTAGE;

-- for rows being modified, save plan_ids (primary key values)
IF :PLAN_ID IS NOT NULL THEN
  :P11_PLAN_IDS := :P11_PLAN_IDS||':'||:PLAN_ID;
END IF;


Next, create a page level validation which checks "Allocation %" already there in DB, excluding rows that are being modified by user (variable l_db_plan_percentage in below code) and add it to "Allocation %" entered by user which was calculated in above page process. Check if the sum of these two is more than 100. If yes, then raise validation error.

Validation Type: PL/SQL Function Body (returning Boolean)
Editable Region: Null
Validation Code:
DECLARE 
  l_db_plan_percentage NUMBER;
BEGIN
  -- take sum of existing rows
  SELECT SUM(PLAN_PERCENTAGE)
  INTO l_db_plan_percentage
  FROM blog_budget_planning
  -- consider rows that are NOT being updated
  WHERE INSTR(':'||:P11_PLAN_IDS||':',':'||TO_CHAR(plan_id)||':') = 0
  ;
  
  -- Check if total percentage is > 100, then raise error
  IF :P11_SUM_PLAN_PERCENTAGE + COALESCE(l_db_plan_percentage,0) > 100 THEN
    RETURN FALSE;
  END IF;
  
  RETURN TRUE;
END;


Next, create button 'SAVE" which will trigger dynamic action and execute below JS code. Here, we need to create separate SAVE button to ensure page is submitted and page validation defined above is executed. Because, above validation is not linked with IG, so IG default "Save" button will not trigger this validation.

SAVE button D.A. JS code:

apex.page.submit( {
    request: "SAVE"
    ,set: {
       "P11_SUM_PLAN_PERCENTAGE": 0
        ,"P11_PLAN_IDS": ""
    }
    ,showWait: true,
} );

Please note, while submitting the page we are also clearing/initializing the hidden items values. This is required to clear values from previous page submit and validation cycle. 

That's it. We are good to go. link to demo

What do you think about this approach? Do you have any other ideas to solve this problem? I am eager to hear your feedback. 

Thank you.

Comments

apexgeek said…
it's enough to create one item to get sum of all IG percentages in one item then make a normal validation.
Hari said…
Yes, it would work when there are no rows exists in DB. But when "Allocation %" exists for some categories already in DB and if user is modifying existing "Allocation %" and/or adding new rows, then we need to know Primary key values of rows user is modifying.
Pavel said…
Great, but there are another simpler way. One can create additional "PL/SQL" process after "Interactive Grid DML" and associate it with your IG. This process will be executed after inserting/updating all the rows but before commiting. Here you can calculate the summary directly in DB - because all rows are already here - and raise exception in case sum > 100. For sure, you can use Succesfull Message and Error Message - fields for customisation.
Hari said…
Thank you Pavel for sharing this tip.

Yes, agree. Initially I did the same way, but some how I did not liked validating data in "processing" section. For me, first preference is to do validations before "Processing" section. If it's not possible, then, next option is to do it in "Processing" section.
APEX Rocks said…
Nice approach for server side table level IG validations. Thank you.
Divya said…
Thats a good approach. Thanks for sharing. What if we want to update one of the row % allocation based on another row % allocation entry. Like the max is 100. If i have one standard row having value as 100 in it. When i add new row and input 10 there, the default row will change to 90. If i add another row with 10, the default will go to 80 and in that way..once the default row goes down to 0, it doesnt let me add any more rows.

Popular posts from this blog

Interactive Grid - Conditional Enable/Disable

In this blogpost, I am going to discuss few approaches using which we can conditionally enable/disable Interactive Grid (IG) column(s) based on other column(s) values. Note:    There is a bug  30801170  in APEX 19.2/20.1 with respect to "enable/disable" dynamic actions for IG columns. Workaround for this bug is provided at the end of this blogpost . This bug has been fixed in APEX version 20.2. Client Side Only Conditions If conditions to enable/disable are simple, then we can check those conditions easily on the client side. For e.g. let's consider IG on EMP table with following SQL Query. SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP Let's consider the requirement as - Enable "Commission" column only when JOB is equals to 'SALESMAN' and disable "Commission" column in all other cases. This can be done declaratively using dynamic actions (DA) DA "Enable/Disable Commission - 1" Create DA and give it a prope

Interactive Grid - Bulk Operation on Selected Rows

What's the Problem? Let's say you have an IG on employee table and you want to update selected employees commission based on user's input. Ideally, it should be very simple, where you can write UPDATE statement in page process and select IG region as "Editable Region" under "Execution Options" of the page process. But, when you select rows and submit page, you can see that, this process won't get executed! The reason is  Selection of 'Row Selector' check-boxes is not considered as row-change. Thus selected rows are not submitted to server. So, is there any work around?  Yes! Luckily there are lot of JavaScript (JS) APIs available to work with IG. If you are not already aware, you can refer "APEX IG Cookbook"  or  JavaScript API Reference documentation. If we continue with above Employee IG example, when user selects IG rows, enters "Commission %" and clicks on "Update Commission" button, then we can writ

Interactive Grid - Process Filtered Data on Server Side

Recently one of the APEX developers has reached out to me and asked if it's possible to capture filtered rows data of the Interactive Grid on the server-side and do some processing. In APEX 20.1, there is a new API APEX_IG , using which we can achieve this. Photo by Jakub Kapusnak on Unsplash The approach is very simple and straightforward. Get the internal region id based on the Static ID given for the IG region Get the last viewed report id based on region id Open query context for the region and report using region id and report id Fetch and loop through the rows using the query context Do something with fetched rows And finally, close the query context If you have already done this for interactive reports, then you should be already aware of these steps. The only difference here is, we use APEX_IG APIs instead of APEX_IR APIs. For the demo purpose, let's Build an Interactive Grid on EMP table and let's give it a Static ID as emp Create a Textarea page item with