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. This functionality may be inconvenient to some users as they would need to set each 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 “addFilter” methods 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 reloading 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:
Form mask for filtering on Interactive Grid columns

The following JavaScript code used in a Dynamic Action can filter and refresh the columns of the Interactive Grid 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 (~).