Oracle APEX Tips & Techniques

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

In Oracle APEX forums, there was a question about "how to conditionally unfreeze interactive grid columns based on the screen size". I had bookmarked this question on my blog notes. Because, this is a good candidate for blogpost as it's a common use case and it may be helpful for other developers.  Photo by Aaron Burden on Unsplash So, I have started building demo for this blogpost. Then, I found that freezing, unfreezing columns based on column name may not be the best solution, because whenever we change column order, then we need to change JavaScript code as-well. With further research (reading documentation thoroughly 😊), I found out that we can also pass column definition object while using freezeColumn and unfreezeColumn methods. This solves the problem with column names.  And then I did a little google search and found another similar question in APEX forums where John Snyders has provided a solution. This solution looks little different. Here, column propert...

Recently one of the users has asked a question "Would it be possible to display selected master row data in detail interactive grid region header?". Yes, why not, it's indeed a reasonable request. However, during implementation, I have found it is little tricky to implement a solution which works in all cases and finally I have managed to get it done. I am sharing this here as it may help other APEX developers. When user selects any row using the row selector check box or when user just clicks on any row, then selectionchange event is fired. This event is fired, irrespective of whether IG is in edit mode or not. So, we can use this event to get data from selected master record. For demo purpose, consider Interactive Grid (IG) on Department table as master and IG on Employees table as detail. And assume requirement is to display selected/current department name in Employees IG region title.  To implement this, let's create Dynamic Action (D.A.) as below Create D.A. an...

When we click on IG column header and if filtering is enabled for that column, then depending on column type, quick filtering "options" (distinct values or date filters) will be displayed. In a recent demo, one of the users has asked, is it possible to show any control to quickly filter for rows having no value for the selected column, like "(Blanks)" filter in Excel. First thing that came to my mind was to use COALESCE for nullable columns and display "(Blanks)" text instead of NULL. This works well for string columns, but not for columns with DATE or NUMBER types. Also, if we use this approach, we need to ensure "(Blanks)" text won't be displayed in downloaded files. We can solve our problem if we Somehow add new "control" in column header menu Apply "Is Empty" filter, preferably using JavaScript, when user selects/clicks new "control" When we click on column header, then APEX appends dynamic DIV element with I...

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

In my previous blog post Interactive Grid - Aggregate Validations , I have explained how we can do "Aggregate Validations" for Interactive Grid (IG) data using server side validations. Server side validations technique is useful for complex validations that may involve checking data from multiple tables or number of rows in IG are more than 50 etc. However, for simple validations, like the one defined in my previous blog post, we can achieve it using Java Script (JS) APIs available for IG, entirely on client side. Let's consider the same example again. 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. To start with, create IG region on BLOG_BUDGET_PLANNING table and enable edit mode. Assume BLOG_BUDGET_PLANNING columns as PLAN_ID, PLAN_CATEGORY, PLAN_PERCENTAGE and Audit (Who) columns In I...

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 implemen...

There was a requirement to build a page where users can  search for data in a report and then choose a row to be added to Interactive Grid (IG),  besides regular way of adding rows to IG. There are two approaches (could be more) to achieve this. Old school approach is adding a row to APEX Collections when user chooses a row in report and using same collection in IG query. Other approach could be, doing something with JavaScript (JS) and build solution on top of IG framework using IG JS APIs, without using collections. When we convert this requirement into technical specification, essentially it has two parts. Adding new row to IG via JS Set values for newly added row via JS Luckily, there are JS APIs and functions already available to achieve both the points. Adding new row to IG via JS Adding new empty row via JS is rather straight forward and easy. // "order" is IG region static ID var $widget = apex.region( 'order' ).widget(); var $gr...

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