Filter 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

Display data from a record type in a read-only paging grid and a dropdown to allow the user to filter the data that is displayed.

Note: This design pattern is not recommended for offline interfaces because filtering data based on user interaction requires a connection to the server.

image:SAIL_Recipes_filter_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 add a filter for the data.

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 filter to the query and a dropdown so that the user can select what data set to display by modifying the expression as shown below:
load(
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 20,
    sort: a!sortInfo(
      field: "expenseItem",
      ascending: true
    )
  ),
  local!priceRange,
  with(
    local!datasubset: queryrecord(
      cons!EXPENSE_REPORT_RECORD,
      a!query(
        selection: a!querySelection(columns: {
          a!queryColumn(field: "expenseItem"),
          a!queryColumn(field: "expenseAmount"),
        }),
        filter: if(
          isnull(local!priceRange),
          null,
          a!queryFilter(
            field: "expenseAmount",
            operator: choose(local!priceRange, "<", "between", ">"),
            value: choose(local!priceRange, 100, {100, 200}, 200)
          )
        ),
        pagingInfo: local!pagingInfo
      )
    ),
    {
      a!dropdownField(
        label: "Filter by Amount",
        labelPosition: "ADJACENT",
        choiceLabels: {"Less than $100", "$100 - $200", "Greater than $200"},
        placeholderLabel: "All",
        choiceValues: {1, 2, 3},
        value: local!priceRange,
        saveInto: {
          local!priceRange,
          /* We need to reset the paging info so that it goes back */
          /* to the first page of the grid when the user changes   */
          /* the filter. Otherwise, the grid errors out            */
          a!save(local!pagingInfo.startIndex, 1)
        }
      ),
      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
      )
    }
  )
)

Test it out

  1. Select the "Less than $100" option from the filter dropdown. Notice that only items where the amount is less than $100 are displayed in the grid.

Notable implementation details

  • The grid generated by the report builder is already configured to page and sort.
  • Notice that when the user makes a selection from the dropdown, we’re always resetting the value of local!pagingInfo so that the user always sees the first page of results for the selected filter. This is necessary regardless of what the user has selected, so we ignore the value returned by the component (in this case, the value of the dropdown selection) and instead insert our own value.
FEEDBACK