Show Calculated Columns 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

Display calculated values in columns in a grid.

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 you've completed that recipe in your environment first.

We will provide calculated values in two columns. 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 two separate data points to create a single value.
  • How to format a date so it's more readable using the Retrieve Next Anniversary Date function recipe.

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

a!gridField(
  label: "Read-only Grid",
  labelPosition: "ABOVE",
  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: "department"),
          a!queryColumn(field: "title"),
          a!queryColumn(field: "startDate")
        }
      ),
      pagingInfo: fv!pagingInfo
    ),
    fetchTotalCount: true
  ),
  columns: {
    a!gridColumn(
      label: "Name",
      sortField: "firstName",
      value: fv!row.firstName &" "& fv!row.lastName
    ),
    a!gridColumn(
      label: "Department",
      sortField: "department",
      value: fv!row.department
    ),
    a!gridColumn(
      label: "Title",
      sortField: "title",
      value: fv!row.title
    ),
    a!gridColumn(
      label: "Next Performance Review",
      sortField: "startDate",
      value:
      /* The following expression is pulled straight from a function recipe in the docs
         for calculating date offsets. */
        if(
          and(month(fv!row.startDate) <= month(today()),day(fv!row.startDate) <= day(today())),
          date(1 + year(today()), month(fv!row.startDate), day(fv!row.startDate)),
          date(year(today()), month(fv!row.startDate), day(fv!row.startDate))
        ),
      align: "END"
    )
  },
  pagesize: 10
)

Test it out

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