Filter an Interactive Grid using JavaScript in APEX 22.1

When using an Interactive Grid with big data and mutiple columns, it may be helpful for the user to be able to filter the results on specific values. For this purpose, APEX offers the native filter functionality of the Interactive Grid, where the user can add filter options on single columns. In this case, the filter options must be set one by one.

There are some existing Oracle PL/SQL procedures like APEX_IR.ADD_FILTER which allow adding filter operations on columns on the side of the database. However, this is only available for Interactive Reports and requires the APEX page to be reloaded so that the filters can be set. Alternatively, the page items of Faceted Search and Smart Filters have been added to APEX recently, but they can be used (as of APEX 22.1) only in combination of a Classic Report, but neither with an Interactive Grid nor an Interactive Report.

In this blog post, I would like to show how the methods “addFilter” and “deleteFilter” of the IG could be used to add several filters at the same time using Dynamic Actions and JavaScript without adding them manually over the “Actions” menu or by rerendering the page. Please note that these methods are not documented on Oracle documentation pages and are therefore not supported. They will be probably subject to change and may not work in the same way for future versions of APEX.

Available operators to use are:

• EQ = Equals
• NEQ = Not Equals
• LT = Less than
• LTE = Less then or equal to
• GT = Greater Than
• GTE = Greater than or equal to
• LIKE = Like operator
• NLIKE = Not Like operator
• N = Null
• NN = Not Null
• C = Contains
• NC = Not Contains
• IN = In Operator
• NIN = Not In Operator
• BETWEEN = Value within a range
• NBETWEEN = Value not within a range
• LAST = Value In The Last (Unit as [MI]nutes, [H]ours, [D]ays, [W]eeks, [M]onths, [Y]ears)
• LAST = Value Not In The Last (Unit as [MI]nutes, [H]ours, [D]ays, [W]eeks, [M]onths, [Y]ears)
• NEXT = Value In The Next (Unit as [MI]nutes, [H]ours, [D]ays, [W]eeks, [M]onths, [Y]ears)
• NNEXT = Value Not In The Next (Unit as [MI]nutes, [H]ours, [D]ays, [W]eeks, [M]onths, [Y]ears)
• REGEXP = Value Matches Regular Expression
• S = Value starts with
• NS = Value does not start with
• OTEXT = Oracle Text Search

The following image shows an example how to use page items for filtering on columns of an Interactive Grid:

The following JavaScript code used in a Dynamic Action can filter and refresh the columns of the Interactive Grid with region id “employees_ig” shown above. First, all page items are verified and checked whether values have been entered. Some of the characters, like the separator “:” of the page item P1_FILTER_JOB (Popup LOV), need to be replaced by Unicode representations. To be able to use a date value to search for, you may need to convert it to a specific date format like “YYYYMMDD000000” (e.g. 20220902000000). When using the BETWEEN operator, you can combine the values by a tilde (~).

//an array with all filters we want to add when pressing Search
var arrayFields = [];
 
//counter controller to check whether it should save and refresh IG
var cont = 1;

//adding job filter
if ($v('P1_FILTER_JOB')) {
  var searchStrings = $v('P1_FILTER_JOB').replace(/:/g, '\u0001');
  arrayFields.push(
  {
    type: 'column',
    columnType: 'column',
    columnName: 'Job',
    operator: 'IN',
    value: searchStrings,
    isCaseSensitive: false
  });
}

//adding hire date filter
if ($v('P1_FILTER_HIREDATE')) {
  arrayFields.push(
  {
    type: 'column',
    columnType: 'column',
    columnName: 'Hire Date',
    operator: 'GT',
    value: $v('P1_FILTER_HIREDATE') + '000000',
    isCaseSensitive: false
  });
}

//adding salary filter
if ($v('P1_FILTER_SALARY_FROM') && $v('P1_FILTER_SALARY_TO')) {
  arrayFields.push(
  {
    type: 'column',
    columnType: 'column',
    columnName: 'Salary',
    operator: 'BETWEEN',
    value: $v('P1_FILTER_SALARY_FROM') + '~' + $v('P1_FILTER_SALARY_TO'),
    isCaseSensitive: false
  });
}

//adding on leave filter
if ($v('P1_FILTER_ON_LEAVE')) {
  arrayFields.push(
  {
    type: 'column',
    columnType: 'column',
    columnName: 'On Leave',
    operator: 'EQ',
    value: $v('P1_FILTER_ON_LEAVE'),
    isCaseSensitive: false
  });
}

//adding no manager filter
if ($v('P1_CHECKBOX_NO_MANAGER') == 'Y') {
  arrayFields.push(
  {
    type: 'column',
    columnType: 'column',
    columnName: 'Manager',
    operator: 'N',
    value: 'null',
    isCaseSensitive: false
  } );
}

//adding starts with filter
if ($v('P1_FILTER_STARTS_WITH_COLUMN') && $v('P1_FILTER_STARTS_WITH_EXPRESSION')) {
  arrayFields.push(
  {
    type: 'column',
    columnType: 'column',
    columnName: $v('P1_FILTER_STARTS_WITH_COLUMN'),
    operator: 'S',
    value: $v('P1_FILTER_STARTS_WITH_EXPRESSION'),
    isCaseSensitive: false
  } );
}

//delete all filters
var filters = apex.region('employees_ig').call("getFilters"); 
for (var i in filters) { 
  var filderId = filters[i].id; 
  apex.region('employees_ig').call("deleteFilter", filderId); 
} 

//iterate through the filters and refresh IG
for (var filterObj of arrayFields) {
  if (filterObj.value) {
    apex.region('employees_ig').widget().interactiveGrid("addFilter", filterObj,
    {
      refreshData: (cont == arrayFields.length) ? true : false,
      save: (cont == arrayFields.length) ? true : false
    });
  }
  cont++;
}