Format Data from a Record in a Grid

See SAIL Recipes for information about how to work through recipes and adapt them to your application.

Goal

Format the data from a record type to display in a read-only paging grid, specifically a decimal number as a dollar amount and a username as a user's display name.

image:SAIL_Recipes_format_data_from_a_Record_in_a_Grid_76.jpg

This scenario demonstrates:

  • How to use the report builder to generate a grid to display data from a record type.
  • How to modify the generated expression to show data from a nested field in the grid.
  • How to format the data that is returned from the query to display in the grid.

For this recipe, you'll need a record. Let's use the process-backed record from the Records Tutorial. If you haven't already created the Expense Report record type, do so now by completing the first five steps of the "Create Process-Backed Records" tutorial and starting at least one instance of the process, then follow the steps below to generate a grid using the report builder.

  1. Create a constant called EXPENSE_REPORT_RECORD with Record Type as the type and Expense Report as the Value.
  2. Open the Interface Designer and select Report Builder from the list of templates.
  3. In the Source Constant field, select the EXPENSE_REPORT_RECORDconstant.
  4. Set the display name for each of the columns as Item and Amount, respectively.
  5. Click Generate. You should see the following expression in the design pane on the left-hand side:
load(
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 20,
    sort: a!sortInfo(
      field: "expenseItem",
      ascending: true
    )
  ),
  with(
    local!datasubset: queryrecord(
      cons!EXPENSE_REPORT_RECORD,
      a!query(
        selection: a!querySelection(columns: {
          a!queryColumn(field: "expenseItem"),
          a!queryColumn(field: "expenseAmount"),
        }),
        pagingInfo: local!pagingInfo
      )
    ),
    a!gridField(
      totalCount: local!datasubset.totalCount,
      columns: {
        a!gridTextColumn(
          label: "Item",
          field: "expenseItem",
          data: index(local!datasubset.data, "expenseItem", null)
        ),
        a!gridTextColumn(
          label: "Amount",
          field: "expenseAmount",
          data: index(local!datasubset.data, "expenseAmount", null)
        ),
      },
      value: local!pagingInfo,
      saveInto: local!pagingInfo
    )
  )
)
  1. Add a new query column for pp.initiator and a corresponding column in the grid by modifying the expression as shown below:
load(
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 20,
    sort: a!sortInfo(
      field: "expenseItem",
      ascending: true
    )
  ),
  with(
    local!datasubset: queryrecord(
      cons!EXPENSE_REPORT_RECORD,
      a!query(
        selection: a!querySelection(columns: {
          a!queryColumn(field: "expenseItem"),
          a!queryColumn(field: "expenseAmount"),
          a!queryColumn(field: "pp.initiator", alias: "initiator")
        }),
        pagingInfo: local!pagingInfo
      )
    ),
    a!gridField(
      totalCount: local!datasubset.totalCount,
      columns: {
        a!gridTextColumn(
          label: "Item",
          field: "expenseItem",
          data: index(local!datasubset.data, "expenseItem", null)
        ),
        a!gridTextColumn(
          label: "Amount",
          field: "expenseAmount",
          data: index(local!datasubset.data, "expenseAmount", null)
        ),
        a!gridTextColumn(
          label: "Requested By",
          field: "initiator",
          data: index(local!datasubset.data, "initiator", null)
        )
      },
      value: local!pagingInfo,
      saveInto: local!pagingInfo
    )
  )
)
  1. Create an expression rule ucUserDisplayName with a single input user of type User and the following definition:
=user(ri!user, "firstName") & " " & user(ri!user, "lastName")
  1. Format the "Amount" column using the dollar function to display the value in dollars and the "Requested By" column using rule!ucUserDisplayName to display the user's first and last name by modifying the expression as shown below:
load(
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 20,
    sort: a!sortInfo(
      field: "expenseItem",
      ascending: true
    )
  ),
  with(
    local!datasubset: queryrecord(
      cons!EXPENSE_REPORT_RECORD,
      a!query(
        selection: a!querySelection(columns: {
          a!queryColumn(field: "expenseItem"),
          a!queryColumn(field: "expenseAmount"),
          a!queryColumn(field: "pp.initiator", alias: "initiator")
        }),
        pagingInfo: local!pagingInfo
      )
    ),
    a!gridField(
      totalCount: local!datasubset.totalCount,
      columns: {
        a!gridTextColumn(
          label: "Item",
          field: "expenseItem",
          data: index(local!datasubset.data, "expenseItem", null)
        ),
        a!gridTextColumn(
          label: "Amount",
          field: "expenseAmount",
          data: dollar(index(local!datasubset.data, "expenseAmount", {}))
        ),
        a!gridTextColumn(
          label: "Requested By",
          field: "initiator",
          data: apply(rule!ucUserDisplayName, index(local!datasubset.data, "initiator", {}))
        )
      },
      value: local!pagingInfo,
      saveInto: local!pagingInfo
    )
  )
)

Notable implementation details

  • The grid generated by the report builder is already configured to page and sort.
  • Nested fields cannot be added through the report builder, so they must be added after the expression is generated.
  • The grid displays the text representation of all types, including Appian Objects such as a user, so we applied our own formatting.
  • The query that populates this grid will return all data for the record type (in pages). If you want to return only a subset of data, add a default filter to the query. See also: Filter Data from a Record in a Grid
FEEDBACK