Searching on Multiple Fields

SAIL Recipes give you an opportunity to explore different interface design patterns. To learn how to directly use SAIL recipes within your interfaces, see Adapt a SAIL Recipe to Work with My Applications.

Goal

Display a grid populated based on search criteria specified by end users. Search criteria, when left blank are not included in the query.

This recipe uses an employee data structure and objects created through the Use the Write to Data Store Entity Smart Service Function on an Interface recipe. Make sure that recipes has been built first in order to see data in this recipe.

Searching_On_Multiple_Fields_Query_Recipe.png

This design pattern is not recommended for offline interfaces because reflecting immediate changes in an interface based on user interaction requires a connection to the server.

This scenario demonstrates:

  • How to store values from multiple fields to filter query results
  • How to use a!queryLogicalExpression() to query from multiple fields

Expression

=load(
   /* In a real app, these values should be held in the database or in a constant */
  local!allDepartments: {"Corporate", "Engineering", "Finance", "HR", "Professional Services", "Sales"},
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 5,
    sort:a!sortInfo(
      field:"lastName",
      ascending:true
    )
  ),
  /* These local variables store filter values*/
  local!lastName,
  local!title,
  local!department,
  with(
    local!noFiltersApplied: all(
      fn!isnull,
      {local!lastName, local!title, local!department}
    ),
    local!datasubset:a!queryEntity(
      entity: cons!EMPLOYEE_ENTITY,
      query: a!query(
        logicalExpression: if(
          /* if all filter values are null, ignore the subsequent logical expression*/
          local!noFiltersApplied,
          null,
          a!queryLogicalExpression(
            operator: "AND",
            filters: {
              /* Preform ad-hoc filtering for each field */
              if(
                isnull(local!lastName),
                {},
                a!queryFilter(field: "lastName", operator: "includes", value: local!lastName)
              ),
              if(
                isnull(local!title),
                {},
                a!queryFilter(field: "title", operator: "includes", value: local!title)
              ),
              if(
                isnull(local!department),
                {},
                a!queryFilter(field: "department", operator: "=", value: local!department)
              )
            }
          )
        ),
        pagingInfo: local!pagingInfo
      )
    ),
    a!sectionLayout(
      contents:{
        a!columnsLayout(
          columns:{
            a!columnLayout(
              contents:{
                a!textField(
                  label: "Last Name",
                  refreshAfter: "KEYPRESS",
                  value: local!lastName,
                  saveInto: {
                    local!lastName,
                    a!save(local!pagingInfo.startIndex, 1)
                  }
                ),
                a!textField(
                  label: "Title",
                  value: local!title,
                  refreshAfter: "KEYPRESS",
                  saveInto: {
                    local!title,
                    a!save(local!pagingInfo.startIndex, 1)
                  }
                ),
                a!dropdownField(
                  label: "Department",
                  placeholderLabel: "All Departments",
                  choiceLabels: local!allDepartments,
                  choiceValues: local!allDepartments,
                  value: local!department,
                  saveInto: {
                    local!department,
                    a!save(local!pagingInfo.startIndex, 1)
                  }
                ),
                a!buttonLayout(
                  primaryButtons: {
                    a!buttonWidget(
                      label: "Clear",
                      saveInto: {
                        local!lastName,
                        local!title,
                        local!department,
                        a!save(local!pagingInfo.startIndex, 1)
                      },
                      disabled: local!noFiltersApplied
                    )
                  }
                )
              }
            ),
            a!columnLayout(
              contents:{
                a!gridField(
                  label: "Results",
                  columns: {
                    a!gridTextColumn(
                      label: "Last Name",
                      field: "lastName",
                      data: index(local!datasubset.data, "lastName", {})
                    ),
                    a!gridTextColumn(
                      label: "Title",
                      field: "title",
                      data: index(local!datasubset.data, "title", {})
                    ),
                    a!gridTextColumn(
                      label: "Department",
                      field: "department",
                      data: index(local!datasubset.data, "department", {})
                    )
                  },
                  totalCount: local!datasubset.totalCount,
                  value: local!pagingInfo,
                  saveInto: local!pagingInfo
                )
              }
            )
          }
        )
      }
    )
  )
)

Test it out

  1. Select Sales in the department dropdown. The list of employees in the grid is now limited to those in the Sales department.
  2. Click the Clear button. On the Last name field, enter "Johnson". The grid now contains only employees whose last name contains "Johnson".

Notable implementation details

  • Fields are set to refresh after keypress. As soon as an end user types in a value the filter will evaluate and reduce the datasubset. If this pattern is not desired, Designers should attempt to only evaluate after unfocus or use a search button that performs the evaluation.
FEEDBACK