Filter the Data in a Grid

Interface patterns give you an opportunity to explore different interface designs. Be sure to check out How to Adapt a Pattern for Your Application.

Goal

Filter the data in a read-only grid using a dropdown. When the user selects a value to filter by, update the grid to show the result.

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 recipe uses example data 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.

The result displays on page 1 even if the user was previously on a different page number.

This scenario demonstrates:

  • How to change displayed data based on a filter value

Expression

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
a!localVariables(
  /*PagingInfo for the pie chart. Returns all employees by department.*/
  local!chartPagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: -1,
    sort: a!sortInfo(
      field: "department",
      ascending: true
    )
  ),
  local!chartDataSubset: a!queryEntity(
    entity: cons!EMPLOYEE_ENTITY,
    query: a!query(
      aggregation: a!queryAggregation(
        aggregationColumns: {
          /* Grouping on department then counting the total ids in that group
             for the pie chart to size. This returns an array of departments
             with the total number of employees in that department. It looks
             like this: { {department:Engineering, id: 6}...} */
          a!queryAggregationColumn(field: "department", isGrouping: true),
          a!queryAggregationColumn(field: "id", aggregationFunction: "COUNT"),
        }
      ),
      pagingInfo: local!chartPagingInfo
    )
  ),
  /* In a real app, these values should be held in the database or in a constant */
  local!departments: { "Corporate", "Engineering", "Finance", "HR", "Professional Services", "Sales" },
  /* local!selectedDepartment holds the name of the selected pie chart section. */
  local!selectedDepartment,
  a!sectionLayout(
    contents: {
      a!pieChartField(
        series: a!forEach(
          items: local!chartDataSubset.data,
          expression: a!chartSeries(
            label: fv!item.department,
            data: fv!item.id,
            links: a!dynamicLink(
              /* The dynamic link stores the department value into local!selectedDepartment. */
              value: fv!item.department,
              saveInto: local!selectedDepartment
            )
          )
        )
      ),
      a!linkField(
        labelPosition: "COLLAPSED",
        links: a!dynamicLink(
          label: "Show all employees",
          value: null,
          saveInto: {
            local!selectedDepartment,
            /*Reset the grid paging info back to the first page.*/
            a!save(local!chartPagingInfo.startIndex, 1)
          }
        ),
        showWhen:not(isnull(local!selectedDepartment))
      ),
      a!gridField(
        label: if(isnull(local!selectedDepartment),
        "All Employees",
        "Employees in " & local!selectedDepartment
        ),
        emptyGridMessage: "No employees meet this criteria",
        data: a!queryEntity(
          entity: cons!EMPLOYEE_ENTITY,
          query: a!query(
            selection: a!querySelection(
              columns: {
                a!queryColumn(field: "firstName"),
                a!queryColumn(field: "lastName"),
                a!queryColumn(field: "title")
              }
            ),
            /* Filter the department column based on the value of local!selectedDepartment. */
            logicalExpression: a!queryLogicalExpression(
              operator: "AND",
              filters: {
                a!queryFilter(
                  field: "department",
                  operator: "=",
                  value: local!selectedDepartment
                )
              },
              ignoreFiltersWithEmptyValues: true
            ),
            pagingInfo: fv!pagingInfo
          ),
          fetchTotalCount: true
        ),
        pageSize: 5,
        columns: {
          a!gridColumn(
            label: "First Name",
            sortField: "firstName",
            value: fv!row.firstName
          ),
          a!gridColumn(
            label: "Last Name",
            sortField: "lastName",
            value: fv!row.lastName
          ),
          a!gridColumn(
            label: "Title",
            sortField: "title",
            value: fv!row.title
          )
        }
      )
    }
  )
)

Test it out

  1. Select a department from the dropdown to filter the grid. Notice that employees from only one department are visible in the grid.
  2. Select "Filter By Department" from the dropdown. All employees will return to the grid.
FEEDBACK