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.

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.