Oracle APEX: how to customise Interactive Grid search

Written by Rene Mikami, Senior Consultant, Oct 15, 2020

One of the main features of Oracle APEX is the Interactive Grid (IG). For those unaware, it works as a CRUD to interact with any data from the database. There are many great features readily available in the IG. Search is one of such out-of-the-box capabilities, which means no extra development is required!That simple? Not always! Let's look at a very common requirement to expose data in a table. You need a proper form with some fields and a search button. So, how would we create a form that would work with the IG itself? The answer is Javascript! We do require some development time, after all. Read on to find out how I do it!

Oracle APEX blog 1

Interactive Grid provides a few Javascript APIs. You can use them to perform search actions on the page.

Below is a an example on how to create a simple search form. Note that I’m using the “Sample database Application” app page 2 (you can install it from the App Gallery menu in the Apex builder).

Consider to have 2 regions on the page:

Region 1: the search form
- 2 page items:
  - P2_CUSTOMER_NAME
  - P2_CITY
- Search Button:
Region 2: the actual Interactive Grid

Oracle APEX Interactive Grid Search form

The next step is to configure the search button to perform the following actions:

set the search parameter in the IG using Javascript API.
refresh IG with the new search.

In order to set the search parameters, you will use the following Javascript:

apex.region([TABLE_STATIC_ID]).widget().interactiveGrid("addFilter",
{ type: 'column', columnType: 'column',
columnName: [COLUMN_NAME],
operator: [OPERATOR],
value: [SEARCH_VALUE],
isCaseSensitive: [IS_CASE]
},
{
refreshData: true,
save: true}
);

You need to fill in the variables in the square brackets with the actual values:

TABLE_STATIC_ID: the static ID of the Interactive Grid.
COLUMN_NAME: the name of the column you want to search.
OPERATOR: the operator of the search. It can be one of the following EQ, NEQ, LT, LTE, GT, GTE, C (for contains) etc. You can find all operators by checking the “widget.interactiveGrid.js” file in your Apex installation.
SEARCH_VALUE: the value to search.
IS_CASE: true if you want to case sensitive search, false otherwise.

Note, that the parameter “refreshData” will refresh the IG after adding all filters.

To prevent the IG from refreshing the search after every filter added, I created and added some logic activated when the Search button is clicked. Here is the final Javascript code:

//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 customer filter

if ($v('P2_CUSTOMER_NAME')) {

arrayFields.push(

{

type: 'column',

columnType: 'column',

columnName: 'CUSTOMER_NAME',

operator: 'C',

value: $v('P2_CUSTOMER_NAME'),

isCaseSensitive: false

} ); }

//adding City filter

if ($v('P2_CITY')) {

arrayFields.push(

{

type: 'column',

columnType: 'column',

columnName: 'CUST_CITY',

operator: 'C',

value: $v('P2_CITY'),

isCaseSensitive: false

} ); }

//iterate through the filters and refresh IG

for (var filterObj of arrayFields) {

if (filterObj.value) {

apex.region('customer_ir').widget().interactiveGrid("addFilter", filterObj,

{

refreshData: (cont == arrayFields.length) ? true : false,

save: (cont == arrayFields.length) ? true : false

});

}

cont++;

}

That’s it! You can now test any field in the search form and see the result displayed in the Interactive Grid:

Hope you found this tip helpful. Don't miss my previous blog about how to get the most out of Oracle APEX logs.

Blog: Oracle APEX: how to customise Interactive Grid search