Building an APEX 5 Search Screen

Search screens have always been a standard feature of APEX applications, and there are a number of different ways they can be implemented. Creating an interactive report gives the user a huge amount of search and filter options, but can be slow and all the features may not be needed. If you create a classic report page, you can specify that you would like a search bar. This is a much simpler implementation which generates a text field page item and then uses the value entered into it in LIKE clauses in the query. The other option is to create a search bar from scratch, similar to the one created by the classic report wizard, but you have full control over which columns are searched and can also easily add any other filters you like.

Recently I was asked to create a ‘Google Search’ by a user. This is a common request for me and comes from users being used to the fast, predictive searching available on Google.com. It can be difficult to explain to users that Google have spent many years and lots of money developing their search tools and this isn’t possible for a small scale business application.

This time however, I decided to try and replicate some of the features of Google search rather than the same old search implementations I have used in the past.

Predictive search bars

The most obvious feature to replicate is the predictive results in the search bar and I didn’t have to do much work to get this working. By using a ‘Text Field with autocomplete’ item and supplying the database column you are searching, the field will predict what you are searching for based on what you have already typed.

As you type, the results are reduced based on what you have entered.

Refresh Results

Another nice feature we might want to replicate from Google search is the automatically updating results set. Using dynamic actions we can create something similar in APEX.

A dynamic action with an event ‘Key Release’ should be attached to your autocompleting text field. This will fire every time a character is typed into the field. Next simply add a refresh true action that refreshes a classic report region containing the results. You may also need to upper the text entered so that the search brings back all the expected results.

Further typing into the search field reduces the number of results displayed. I have also removed the ‘Search’ button now that the report refreshes automatically.

We can take this even further and add more filters to our search page which also cause the report to refresh when changed, giving the user a way to search data that feels a little more modern than a regular search bar.

So, with these two simple ideas we can improve the UI of simple search screens. In a future blog I will be talking about how we can incorporate Oracle text to create a fast, modern search screen on top of large, complicated data sets.

Author: Craig Sykes 

Job Title: Senior Oracle Development Consultant

Bio: Craig is a Senior Development Consultant at DSP-Explorer. Craig has an MSc in Computing Science and is an experienced software engineer, utilising development tools such as PL/SQL and APEX to provide bespoke ERP software to both UK and international businesses. Craig has experience developing solutions to connect Oracle systems to a wide range of existing external applications within business environments.