Display Data with CDT Fields from a Record in a Grid

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

Goal

Display data that contains CDT fields from a record type in a read-only paging grid.

image:SAIL_Recipes_Display_Data_with_CDT_fields_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.

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
    )
  )
)

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 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