Filter the Data in a Grid Using a Chart

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

Display an interactive pie chart with selectable sections so that a user may filter the results in a grid.

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.

This interface has two main components: (1) a grid listing all of a company’s employees, and (2) a pie chart with dynamic link sections capable of filtering the grid by department.

This scenario demonstrates:

  • How to use an expression to add links to each slice of the pie chart and use those links to filter grid data.
  • How to use multiple datasubsets.

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
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
        ),
        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. Click a slice of the chart. The grid below the chart will be filtered to display only employees for that department.
  2. Click the "Show all employees" link. The grid will display all employees.

Notable implementation details

  • Notice that when the grid is filtered, we are not querying the department field. This allows us to only query the data that we plan on displaying in the grid.
  • Notice that when the user goes back to the chart to select a new filter, we're always resetting the value of local!gridPagingInfo, ensuring that the user will see the first page for the new filter. This is necessary regardless of what the user has selected, so we ignore the value returned by the component and instead insert our own value.
FEEDBACK