LSPS documentation logo
LSPS Documentation
Filter over Grid and Table with a Custom Data Source

Note: This tutorial uses the forms module as its form implementation.

Required outcome: Grid and table that pull data from a custom data source and support filtering.

Note: You can download the tutorial model here: go to File > Import; select General > Archive File; locate the zip file; select the modules to import (dataInit and filterGrid).

Creating a Custom Data Source

To create and customize a data source for a tabular forms component, do the following:

  1. First create a shared record Applicant and a query that returns its instances:
    1. Create shared Record Applicant with fields firstName and lastName
    2. Create a standard query that will return the record and deal with potential filtering:
      1. Create a standard query that returns all Applicants.
      2. Define possible filters as its input parameters; in our case, we can filter either by firstName or lastName so we will allow two parameters.
      3. In the condition of the query define the behavior of the query so applies filters when passed:
        if isBlank(firstNameFilter) and isBlank(lastNameFilter) then
          true
        elsif !isBlank(firstNameFilter) and !isBlank(lastNameFilter) then
            a.firstName like ("*" + firstNameFilter + "*") and a.lastName like ("*" + lastNameFilter + "*")
        elsif !isBlank(firstNameFilter) and isBlank(lastNameFilter) then
            a.firstName like ("*" + firstNameFilter + "*")
        else
            a.lastName like ("*" + lastNameFilter + "*")
        end
        
  2. Create the data source for your Grid or Table:
    1. Create a record that represents your data source type.
    2. Import the forms::DataSource interface.
    3. Make the record implement the forms::DataSource interface.
    4. Add fields to the record that represent the filters.
      filteroncustomds_customdsrecord.png
      Custom data source record with the source record
  3. Implement the interface methods: adapt the getCount() and getData() methods to handle the filtering.

    The filters are passed as input parameters to the methods. Here is an example of the methods:

    ApplicantDataSource {
       
      public Integer getCount(Collection<forms::Filter> filters){
       
           def String firstNameFilterSubstring := getFilterValue("firstName", filters);
           def String lastNameFilterSubstring := getFilterValue("lastName", filters);
       
         //count query that filters the results:
         getApplicants_count(firstNameFilterSubstring, lastNameFilterSubstring);
        }
       
      public List<Object> getData(Integer startIndex*, Integer count*, Collection<forms::Filter> filters, Set<Sort> sortSpecs){
       
           def String firstNameFilterSubstring := getFilterValue("firstName", filters);
           def String lastNameFilterSubstring := getFilterValue("lastName", filters);
       
           //query that gets results and applies filters:
           getApplicants(firstNameFilterSubstring, lastNameFilterSubstring);
        }
       
        private String getFilterValue (String filterParameterName, Collection<forms::Filter> filters){
           //get first filter with matching name:
           def forms::Filter firstMatchingFilter := getFirst(filters, { f -> f.id == filterParameterName});
           // get search substring in filters:
           def String filterSubstring := firstMatchingFilter == null ? null : (firstMatchingFilter as SubstringFilter).value;
           filterSubstring
        }
       
      public Boolean supportsFilter(forms::Filter filter*){
        if
          filter.id == "firstName" || filter.id == "lastName" then true;
        else
          false
        end
      }
       
      public Boolean supportsSort(Sort sort*){
        false
        }
      public String toString() {
        #"ApplicantDataSource"
      }
    }
    

Creating the Form

  1. On the Grid, set the data source to Custom with the value of the data source instance.
    new ApplicantDataSource()
  2. Select the column that should filter its value to open its properties:
    1. On the Detail tab, define the value Provider as Property path and the value of the property, Applicant.firstName or Applicant.lastName.
    2. On the Filtering tab, select the Filterable option and define Filter configuration: new FilterConfig(filterId -> "firstName") or new FilterConfig(filterId -> "lastName").