Show Calculated Columns in a Grid

Interface patterns give you an opportunity to explore different interface designs. To learn how to directly use patterns within your interfaces, see How to Adapt a Pattern for Your Application.

Goal

Display and sort on column whose data is not a field in the CDT but a calculated form of the data in the CDT.

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.

We will provide calculated vales in two column. First, we will concatenate firstName and lastName to display as a single display name. Secondly, we will look at an employee's start date, and determine that employee's next performance review date.

This scenario demonstrates:

  • How to concatenate to separate data points to create a single value
  • How to format a date so it's more readable
  • How to calculate a date conditionally

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
=load(
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 10,
    sort: a!sortInfo(
      field: "firstName",
      ascending: true
    )
  ),
  with(
    /* 
    * local!datasubset is pulling data from the employee data store entity 
    * from the entity backed record tutorial.
    *
    * 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!datasubset: 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!queryColumn(field: "phoneNumber"),
          a!queryColumn(field: "startDate")
        }),
        pagingInfo: local!pagingInfo
      ),
      fetchTotalCount: true
    ),
    a!gridField(
      totalCount: local!datasubset.totalCount,
      columns: {
        a!gridTextColumn(
          label: "Name",
          field: "firstName",
          data: a!forEach(
            items: local!datasubset.data,
            expression: fv!item.firstName & " " & fv!item.lastName
          )
        ),
        a!gridTextColumn(
          label: "Department",
          field: "department",
          data: index(local!datasubset.data, "department", null)
        ),
        a!gridTextColumn(
          label: "Title",
          field: "title",
          data: index(local!datasubset.data, "title", null)
        ),
        a!gridTextColumn(
          label: "Phone Number",
          data: index(local!datasubset.data, "phoneNumber", null)
        ),
        a!gridTextColumn(
          label: "Next Performance Review",
          field: "startDate",
          data: a!forEach(
            items: index(local!datasubset.data, "startDate", null),
            expression: text(
              date(
                if(
                  and(
                    month(today()) > month(fv!item),
                    day(today()) > day(fv!item)
                  ),
                  year(today()) + 1,
                  year(today())
                ),
                month(fv!item),
                day(fv!item)
              ),
              "mmm dd, yyyy"
            )
          ),
          alignment: "RIGHT"
        )
      },
      value: local!pagingInfo,
      saveInto: local!pagingInfo
    )
  )
)

Test it out

  1. Sort the grid by the "Name" column. Notice that the data sorts appropriately even across all pages of data.

Notable implementation details

  • In order to correctly sort on the calculated column, we queried the entire data set, calculated the new value for each row, then paged and sorted on the result. If you were to only calculate the column for the current page, sorting on the calculated column would not work correctly across pages.
  • Since you must query all data and then loop over each item to calculate the additional data, this technique should not be used for a large amount of data, as the query and calculation may become slow. To further optimize this grid, only perform the calculation on every row when the user sorts by the calculated column, otherwise, simply calculate the data for the current page.
FEEDBACK