How to Filter Table Data in Oracle Visual Builder

We often have Tables in our VB screens which can contain large numbers of rows. We also may have requirements that provide a way to end users to filter data within it.

If the table is hooked to SDP then you can follow this method in vbcookbook

In this article let's see how can we add a similar filter but for an ADP.

Let us assume we have an oj-table which is hooked to ADP and an oj-input-text to enter the filter text.

Now to filter data we need will leverage ListDataProviderView . A similar example in JET can be found here. Let us see how to get it working in VBCS.

• Go to JavaScript of the Page and in the define block we need to import ojs/ojlistdataproviderview and ojs/ojdataprovider.

  • ojs/ojlistdataproviderview will be used to create the ListDataProviderView

  • ojs/ojdataprovider will be used to create the filterCriterion.

• Add a function (createLDPV) that takes in the ADP + filter text and creates a ListDataProviderView

define(['ojs/ojlistdataproviderview', 'ojs/ojdataprovider'], (ListDataProviderView, ojdataprovider_1) => {
  'use strict';
  class PageModule {
    createLDPV(ADP, filter) {
      let filterCriterion = null;
      if (filter && filter != '') {
        filterCriterion = ojdataprovider_1.FilterFactory.getFilter({
          filterDef: { text: filter } //This is text filter

          // --- Below is ExtendedCompoundFilterDef where we can specify the columns to filter on ---
          // filterDef: { op: '$or', criteria: [{ op: '$co', value: { fIRSTNAME: filter } } , { op: '$co', value: { lASTNAME: filter } }]}

        });
      }
      return new ListDataProviderView(ADP, { filterCriterion: filterCriterion });
    }
  }
  return PageModule;
});

• Now hook Table to this function rather than the original ADP. In the below image

  • $variables.get_EmployeesVar is the ADP

  • $variables.filterText is the variable hooked to the Filter Input Text

    

That's it, now if we type something into the Filter Text Box and Enter, the table will show the filtered results.

A sample application can be found in my GitRepo here.

Note :

• In this sample, we use a text filter that looks at all the columns

• But if you would like to filter only on specific columns then you can use ExtendedCompoundFilter. The documentation is here. The sample JS code above has a commented line for reference which searches only the first/last name columns.