Filter the Data in a Grid Using a Chart

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 chart with aggregate data from a data store entity, specifically the total number of employees for each department, and a grid to display all data, specifically all employees. Then add links to the chart slices so that when a user clicks on a department, the grid will be filtered to show only employees for that department.

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.

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 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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
=load(
 /* 
  * PagingInfo for the pie chart. It is bringing back all employee data
  * in this case and sorting by department.
  */
  local!chartPagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: -1,
    sort: a!sortInfo(
      field: "department",
      ascending: true
    )
  ),
 /* 
  * local!chartDatasubset is pulling data from the employee data store entity 
  * from the entity backed record tutorial The resulting data value looks 
  * like this data: { {department:Engineering, id: 6}...}
  *
  * To use your data, change the constant from cons!EMPLOYEE_ENTITY to one of
  * your own. Then replace the fields in the queryAggregationColumns to a
  * relevant datapoint from your data store entity
  */
  local!chartDatasubset: a!queryEntity(
    entity: cons!EMPLOYEE_ENTITY,
    query: a!query(
      aggregation: a!queryAggregation(aggregationColumns: {
        a!queryAggregationColumn(field: "department", isGrouping: true),
        a!queryAggregationColumn(field: "id", aggregationFunction: "COUNT"),
      }),
      pagingInfo: local!chartPagingInfo
    )
  ),
  /* 
  * PagingInfo for the paging grid. It is bringing back only the first 10 records
  * and sorting by title.
  */
  local!gridPagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 10,
    sort: a!sortInfo(
      field: "title",
      ascending: true
    )
  ),
  /*
  * local!selectedDepartment is the department to filter on.
  * If it's null, the data displayed in the grid is not filtered
  */
  local!selectedDepartment,
  with(
    /* 
    * local!GridDatasubset is pulling data from the employee data store entity 
    * from the entity backed record tutorial The resulting data value looks 
    * like this data: { {firstName:John, lastName:Smith, department:...}... }
    *
    * To use your data, change the constant from cons!EMPLOYEE_ENTITY to one of
    * your own. Then replace the fields in the queryColumns to a relevant
    * datapoint from your data store entity.
    */
    local!gridDatasubset: a!queryEntity(
      entity: cons!EMPLOYEE_ENTITY,
      query: a!query(
        selection: a!querySelection(columns: {
          a!queryColumn(field: "firstName"),
          a!queryColumn(field: "lastName"),
          a!queryColumn(field: "department"),
          a!queryColumn(field: "title")
        }
        ),
        /*
        * a query filter is set using local!selectedDepartment and a dynamic link
        * from the pie chart. When a user clicks on a pie piece, the filter will
        * re-evaluate the datasubset based on the user's input.
        */
        filter: if(
          isnull(local!selectedDepartment),
          null,
          a!queryFilter(field: "department", operator: "=", value: local!selectedDepartment)
        ),
        pagingInfo: local!gridPagingInfo
      )
    ),
    a!sectionLayout(
      contents:{
        /* 
        * a!forEach is used to generate an array of chart series with links
        *
        * To use your data, change the fv!item element values from 
        * fv!item.department and fv!item.id to relevant datapoints in your datasubset
        */
        a!pieChartField(
          series: a!forEach(
            items: local!chartDatasubset.data,
            expression: a!chartSeries(
              label: fv!item.department,
              data: fv!item.id,
              links: a!dynamicLink(
                value: fv!item.department,
                saveInto: {
                  local!selectedDepartment,
                  /* 
                   * Need to reset the paging info back to the first page when the 
                   * user changes the filter.
                   */
                  a!save(local!gridPagingInfo.startIndex, 1)
                }
              )
            )
          )
        ),
        /*      
         * The showWhen parameter is used here in place of the if() funciton. 
         * The linkField() is set to show only after a user has set a value
         * for local!selectedDepartment and disappear when it's null.
         */
        a!linkField(
          labelPosition: "COLLAPSED",
          links: a!dynamicLink(
            label: "Show all employees",
            value: null,
            saveInto: {
              local!selectedDepartment,
              /* 
               * Need to reset the paging info back to the first page when the 
               * user changes the filter. Otherwise, the grid could try to display
               * a page of data that doesn't exist in our subset, resulting in an error. 
               */
              a!save(local!gridPagingInfo.startIndex, 1)
            }
          ),
          showWhen:not(isnull(local!selectedDepartment))
        ),
        a!gridField(
          label: if( 
            isnull(local!selectedDepartment),
            "All Employees",
            "Employees in " & local!selectedDepartment
          ),
          totalCount: local!gridDatasubset.totalCount,
          columns: {
            a!gridTextColumn(
              label: "First Name", 
              field: "firstName",
              data: index(local!gridDatasubset.data, "firstName", {})
            ),
            a!gridTextColumn(
              label: "Last Name", 
              field: "lastName",
              data: index(local!gridDatasubset.data, "lastName", {})
            ),
            a!gridTextColumn(
              label: "Department",
              field: "department",
              data: index(local!gridDatasubset.data, "department", {}),
              showWhen: isnull(local!selectedDepartment)
            ),
            a!gridTextColumn(
              label: "Title",
              field: "title",
              data: index(local!gridDatasubset.data, "title", {})
            )
          },
          value: local!gridPagingInfo,
          saveInto: local!gridPagingInfo
        )
      }
    )
  )
)

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