Skip to main content

Adding custom quick filter to Interactive Grid column

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
  1. Somehow add new "control" in column header menu
  2. 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 ID as "<ig_region_id>_ig_column_header_menu" and displays the DIV element below column header. Optionally APEX makes an AJAX call to get distinct column values for quick filtering. This DIV element contains UL element another DIV element with class a-IRR-sortWidget-search, as direct childs. UL element contains buttons for actions like control break, aggregate etc. and child DIV contains "Search" related elements. We can see sample HTML generated for column header menu below.

Column Header Menu Example


For point 1, we can add new icon button (as li element) to UL element at the end. We can do this easily using jQuery.

For point 2, Roel Hartman has already shared one technique using which we can apply filters on IG using JavaScript.

And here is the JavaScript function which will add new control button for "Is Empty" filter. Put this JS code in "Page > Function and Global Variable Declaration" section. This will add new icon button (empty cart icon) to the column header menu.

function addNullFilter(pRegionId, pColumnName) {
 // check if pColumnName has filtering enabled or not
 // to do - there should be better way to check this. data.column does not have filter option (as true/false)
 if ($("#" + pRegionId + "_ig_column_header_search").length > 0) {
  // copied li tag from generated HTML and modified title, aria-label and class
  // removed data-option so APEX IG JS will ignore this li click
  liObj = $('<li class="a-IRR-sortWidget-actions-item"><button type="button" class="a-Button a-IRR-button a-IRR-sortWidget-button" title="Empty Filter" aria-label="Empty Filter"><span aria-hidden="true" class="a-Icon fa fa-cart-empty"></span></button></li>');
  // define click behavior for new li object
  $(liObj).click(function() {
   // add is Empty filter for pColumnName
   apex.region(pRegionId).widget().interactiveGrid("addFilter", {
    type: 'column',
    columnType: 'column',
    columnName: pColumnName,
    operator: 'N',
    value: '',
    isCaseSensitive: false
   });
   // hide column header menu
   $("#" + pRegionId + "_ig_column_header_menu").hide();
  });
  // append li object to existing ul element under column header menu
  $("#" + pRegionId + "_ig_column_header_menu ul").append(liObj);
 }
}

And we need to call this function addNullFilter when user clicks on column header. So put below code in "Page > Execute when page load" section.

// emp is IG static id
$("#emp").on("gridactivatecolumnheader", function(event, data) {
 // column header activated, call addNullFilter function with region id and column name
 setTimeout(addNullFilter, 0, $(this).attr("id"), data.column.property);
});

Alternatively, we can create dynamic action with custom event as "gridactivatecolumnheader" for IG region which executes below JS code.

// column header activated, call addNullFilter function with region id and column name
setTimeout(addNullFilter, 0, $(this.triggeringElement).attr("id"), this.data.column.property);

IG - with (Blanks) Quick Filter Option

After publishing this post, I got another idea. Why can't I just add new quick filter option "(Blanks)" along with DISTINCT values or other quick filter options that APEX displays when we click on column header? With little trial & error and modifying above JavaScript function addNullFilter, I was able to achieve this.

Here is the new updated JavaScript function

// function to add (Blanks) filter
function addNullFilter1(pRegionId, pColumnName) {
 // check if pColumnName has filtering enabled or not
 // todo - there should be better way to check this. data.column does not have filter option (as true/false)
 if ($("#" + pRegionId + "_ig_column_header_search").length > 0) {
  // copied anchor tag from generated HTML
  // removed data-return-value for anchor tag and modified class name (appended -copy)
  // if we use same class name, then APEX will trigger its own filtering when we click it
  var nullFilter = $('<a class="a-IRR-sortWidget-row-copy">(Blanks)</a>');
  // append nullFilter object next to search input item
  $("#" + pRegionId + "_ig_column_header_search").after(nullFilter);
  // define click behavior for new anchor object
  $(nullFilter).click(function() {
   event.preventDefault();
   // add is Empty filter for pColumnName
   apex.region(pRegionId).widget().interactiveGrid("addFilter", {
    type: 'column',
    columnType: 'column',
    columnName: pColumnName,
    operator: 'N',
    value: '',
    isCaseSensitive: false
   });
   // remove column header menu
   $("#" + pRegionId + "_ig_column_header_menu").hide();
  });  
 }
}

And we need to call this function addNullFilter1 when user clicks on column header. So put below code in "Page > Execute when page load" section.

// emp1 is IG static id
$("#emp1").on("gridactivatecolumnheader", function(event, data) {
 // column header activated, call addNullFilter function with region id and column name
 setTimeout(addNullFilter1, 0, $(this).attr("id"), data.column.property);
});

However, an additional step is required for this approach. For APEX to ignore this new filter option, I have modified anchor tag class from "a-IRR-sortWidget-row" to "a-IRR-sortWidget-row-copy". So I have copied style definitions as-well for this class and copied them in "Page > CSS > Inline" section. 

Based on demo application theme style, here is the CSS code I have copied. You may have to change this CSS code based on "a-IRR-sortWidget-row" class definition for your application theme style.

.a-IRR-sortWidget-row-copy {
    display: block;
    text-decoration: none;
    color: #FFF;
    font-size: 12px;
    line-height: 16px;
    white-space: nowrap;
    overflow: hidden;
    text-overflow: ellipsis;
    padding: 8px;
    -webkit-box-shadow: 0 -1px 0 rgba(0, 0, 0, 0.05) inset;
    box-shadow: none;
    background-clip: border-box;
    cursor: pointer;
}

Link for demo. I have updated demo page to showcase both the approaches discussed here.

What do you think about these approaches? Do you know any better ways of implementing this requirement? Let me know your thoughts. 

Thank you.

Comments

Stefan Dobre said…
Very interesting blog post. Thanks for this!

I noticed some hoops you had to jump through to get it to work, such as

1) the addFilter API is not documented - but there's not much we can do about that.
2) the filter menu is not an actual menu (widget), just plain HTML. It would have been nicer had it been its own menu, as we could use the API to properly add another control. Not much we can do here either.
3) the 500ms delay. It's interesting how the gridactivatecolumnheader event is raised by the grid widget, but before the interactiveGrid widget is done building up the menu. So a delay seems to be necesarry, but 500 is a bit much. In my case it even worked with 0.
4) I looked into it and checking if a column is filterable seems to only be possible with a private IG function. Not much we can do there either.

So, to answer the question you posed at the end: no, I personally don't know of any better way to achieve this.

Good job. Looking forward to more interesting content like this!
Hari said…
Thank you Stefan for checking out this blogpost and providing your feedback. Yes, first I have checked documentation to see if I can achieve this using documented JS APIs. As I can't find any, I went with this approach. It's kind of hack and should be revisited after every APEX release.

Yes, I have tested with 0 ms for setTimeout and it worked. I will update the post with it.

Luckily, there is already "gridactivatecolumnheader" event, which I was able to use for this use case. This is better than defining custom onclick handlers on IG column headers using jQuery. One less hoop to jump through :)
Veerendra said…
Hi Hari,
Very good article.

Just wanted to ask a question.
Here we have addFilter -Value = ''
Can we have filters based on some functions
For example - All salaries > avg (sal)

Thanks,
Veerendra
Hari said…
Hi Veerendra,

Thanks for your feedback.

Regarding your question, as of APEX 20.1, you can do it as below.

1) Make an AJAX call to get average salary and store it in JavaScript Variable
2) Use average value calculated in step-1 and use "addFilter" method to filter
Veerendra said…
Hi Hari,
Thanks for your reply.
How will this work when I add another filter - say deptNo = 20.
How can I calculate the AVG(sal) that time?
How to recalculate avg(sal) when filters added/removed?

Can you give me some sample code please?

Thanks,
Veerendra
Hari said…
Hi Veerendra,

I don't see any easy way to handle this filter. If not already done, I suggest to you post your question at APEX Forum. https://bit.ly/2ZF9lUa Good luck.

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