Column-Level Search on a table in Oracle Visual Builder

This article demonstrates how to enable column-level search fields on a table in Oracle Visual Builder.

In Visual Builder, there are different data providers such as Service Data Provider (SDP), and Array Data Provider (ADP). Using a Service Data Provider (SDP) we add data to the table component and demonstrate the search operation.

The following are the steps :

Create a VB application and a web application under the VB application using the Web application navigator.
Create an Employees Business Object with the following three fields:
First Name
Last Name
Department Id
Go to the Data tab and click on +Row to add some rows to the Business Object.

For more detail on the creation of Business objects please refer to our antecedent blog which includes the steps to create Business Objects.

3. Go to the Variables tab of the page and create variables for the input fields
FirstName, LastName, Department Id ,queryString of String type.

4. Go to main-start, drag and drop the table component from the Component pallet to the design area as shown in the following screenshot:

5. In the Properties of table, switch to Quick start tab and click on Add Data option as shown in the following screenshot:

With this, a popup will open showing all the business objects and service connections available. Of these, we need to select the data source to be added to the table.

Here we are working on Employees Business objects so we are selecting the Employees BO and click on Next button.

Then we need to select the fields that are to be displayed on the table and then click on the Next button, map queryString to q param under uriParameter followed by the Finish button.

6. To add some CSS, We are going to update app.css and apply styles to the input text so it matches the appearance in Fusion. Now open app.css and add the below code piece in it:

.formclass .oj-text-field-container {

  height: 32px !important;

  min-height: 32px !important; }

You can Increase and decrease the heights based on your requirements.

7. Now Go to the code view of the table and add a header template to add input search fields to the table as shown in the below code:

Before HeaderTemplate Addition:

 <oj-table scroll-policy="loadMoreOnScroll" display ="grid" 
     class="oj-flex-item oj-sm-12 oj-md-7"
    data="[[$variables.employeesBOListSDP]]"
    columns='[
       {"headerText":"First Name","field":"firstName"},
       {"headerText":"Last Name","field":"lastName"},
       {"headerText":"DepartmentId","field":"departmentId"}
      ]'></oj-table>

After HeaderTemplate Addition:

 <oj-table scroll-policy="loadMoreOnScroll"  display ="grid"
class="oj-flex-item oj-sm-12 oj-md-7"

    data="[[$variables.employeesBOListSDP]]" 

columns='[
{"headerText":"FirstName","field":"firstName","headerTemplate":"firstname"},

{"headerText":"Last Name","field":"lastName","headerTemplate":"lastname"},    {"headerText":"DepartmentId","field":"departmentId","headerTemplate":"departmentId"}
]'>

    <template slot="firstname">

            <oj-input-text   aria-label="firstname" class="formclass"
value="{{ $variables.firstName }}"></oj-input-text>

             <br>

             <oj-bind-text value="[[$current.data]]"></oj-bind-text>

      </template>

      <template slot="lastname">

             <oj-input-text aria-label="lastname" class="formclass"  value="{{                                   $variables.lastName }}"></oj-input-text>

             <br>

            <oj-bind-text value="[[$current.data]]"></oj-bind-text>

       </template>

       <template slot="departmentId">

            <oj-input-text aria-label="departmentId" class="formclass"  value="{{                              $variables.departmentId}}"></oj-input-text>

            <br>

            <oj-bind-text value="[[$current.data]]"></oj-bind-text>

        </template>

  </oj-table>

In the code above, we have created templates for the header to include input fields assigned with previously defined variables. I also added CSS classes and bound the `headerText` for each column.

8. To perform Column level search we are using querystring approach and the following JavaScript function is written on the page:

QueryStringGenerator(firstName, lastName, departmentId) {
let queryString = "";
if (typeof firstName !== 'undefined' && firstName !== '' && firstName !== null) {
if (queryString == "") {
queryString = queryString + "firstName like (\'%" + firstName + "%\')";
} else {
queryString = queryString + " AND " + "firstName like (\'%" + firstName + "%\')";
}
}
if (typeof lastName !== 'undefined' && lastName !== '' && lastName !== null) {
if (queryString == "") {
queryString = queryString + "lastName like (\'%" + lastName + "%\')";} else {
queryString = queryString + " AND " + "lastName like (\'%" + lastName + "%\')";
}
}
if (typeof departmentId !== 'undefined' && departmentId !== '' && departmentId !== null) {
if (queryString == "") {
queryString = queryString + "departmentId like (\'%" + departmentId + "%\')";
} else {
queryString = queryString + " AND " + "departmentId like (\'%" + departmentId + "%\')";
}
}
return "(" + queryString + ")";
}

9. Now go to Action chains on main-start page, click on + Action chain, Type in ID, and click on the Create button as shown in the following screenshot:

10. In the action chain, drag and drop the Call Function action, and call the QueryStringGenerator function.

Map the DepartmentId, FirstName, and LastName to the js input parameters as shown in the following screenshot:

11. Drag and drop the Assign Variables action after the call Function Action.

Note: If we want to see all the records when we don’t enter any value in the input fields then assign “null” as a default value to the QueryString variable so that all the records will be shown in the table if the query string is empty.

12. Drag and drop the Fire Data Provider Event action after the Assign Variable action, enter the employeesListSDP in the Event Target, and select the Refresh option.

13. To value-change action of all the input search fields tag this action chain and with this, we are done with adding the functionality to enable column-level search on the table.

14. You can now go to the UI, enter the First Name, Last Name, and Department ID, or use a combination of these values in the search fields to filter the data accordingly.

15. Finally UI looks as shown in the following screenshot: