Save a User's Report Filters to a Data Store Entity

SAIL Recipes give you an opportunity to explore different interface design patterns. To learn how to directly use SAIL recipes within your interfaces, see Adapt a SAIL Recipe to Work with My Applications.

Goal

Allow a user to save their preferred filter on a report and automatically load it when they revisit the report later.

image:SailSmartServicesReportRecipe.png

This scenario demonstrates:

  • How to use the Write to Data Store smart service from a SAIL report

For this recipe, you'll want to use a report with some actual data. If you've already set up the Update an Entity-Backed Record from its Summary View recipe you can use that recipe's entity-backed record, otherwise you can set it up now in only a few minutes by following these steps:

  1. Open the Appian Designer and click New Application. This displays the Create New Application form.
  2. Select Create Using Wizard at the top
  3. Select a data source for your application to use from the Data Source dropdown
  4. Click Create. This displays the Create Application wizard
  5. Enter Customer Support Request System in the Application Name field
    • If you use a different name, you'll need to take that into account for the rest of these instructions and adjust the main expression based on the names of the objects you generated.
  6. Enter Support Request in the Record Singular Name field
  7. Enter Support Requests in the Record Plural Name field
  8. Click Continue. This displays the Interfaces page of the wizard
  9. Leave the default fields on the Interfaces page and click Continue
  10. Leave the default collaborators and click Create Application
  11. Click Close

You've now created a fully-functioning application with an entity-backed record! To make sure you have some data to report on, go to Actions and start a few service requests.

Now on to the report! Before you put in the expression, you'll first need to create a place to store user filters:

image:SailSmartServicesReportRecipeDataType.png

  1. From the Customer Support Request System application, click New, and select Data Type
  2. Enter SupportRequestReportFilter in the Name field
  3. Click Create & Edit. This displays the Data Type Designer.
  4. Click New Field three times
  5. Enter username for the first field's name
  6. Enter status for the second field's name
  7. Enter priority for the third field's name
  8. For the username field, click on the key icon and select Primary Key
  9. Click OK
  10. Click Save to create the data type.

Now that you've created the data type, you'll need to make a data store entity so you can write the data to your database.

  1. Back in the application, filter by Data Stores and open the Support Requests data store
  2. Click Add Entity
  3. Enter SupportRequestReportFilter in the Name field
  4. Select SupportRequestReportFilter in the Type field
  5. Click Save
  6. Click Verify
  7. Click Save & Publish to save and publish the data store entity.

Now you need to create a constant pointing at the data store entity so it can be used in an expression:

  1. Back in the application, click New and select Constant
  2. Enter CSRS_FILTER_ENTITY in the Name field
  3. Select Data Store Entity for the Type field
  4. Select Support Requests for the Data Store field
  5. Select SupportRequestReportFilter for the Entity field
  6. Enter CSRS Rules and Constants in the Save In field, then select the folder from the suggestions

  7. Click Create to create the constant.

Now you're ready to build the actual report.

  1. From the application, click New and select Interface
  2. Enter a name in the Name field
  3. Enter CSRS Rules and Constants in the Save In field, then select the folder from the suggestions
  4. Click Create & Edit
  5. Switch to the expression view and paste in the following expression:

Expression

=load(
  local!persistedFilterData: a!queryEntity(
    entity: cons!CSRS_FILTER_ENTITY,
    query: a!query(
      filter: a!queryFilter(field: "username", operator: "=", value: loggedInUser()),
      pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 1)
    )
  ),
  local!persistedFilter: if(
    local!persistedFilterData.totalCount = 0,
    /* There's no existing filter for this user, so create a new one */
    'type!{urn:com:appian:types}SupportRequestReportFilter'(username: loggedInUser()),
    cast('type!{urn:com:appian:types}SupportRequestReportFilter', local!persistedFilterData.data[1])
  ),
  local!saveFilterError: false,
  /* Store the current filter separate from the persisted filter so we know when they are the same */
  local!filter: local!persistedFilter,
  local!allPriorities: rule!CSRS_GetAllPriority().value,
  local!allStatuses: rule!CSRS_GetAllStatus().value,
  local!pagingInfo: a!pagingInfo(1, -1, a!sortInfo("createdOn", false)),
  with(
    local!filterChanged: not(exact(local!persistedFilter, local!filter)),
    /* Data that will be displayed in the grid given the *
     * current search terms and applied filters          */
    local!datasubset: a!queryEntity(
      entity: cons!CSRS_SUPPORT_REQUEST_DSE,
      query: a!query(
        selection: a!querySelection(
          columns: {
            a!queryColumn(field: "id"),
            a!queryColumn(field: "title"),
            a!queryColumn(field: "status"),
            a!queryColumn(field: "priority")
          }
        ),
        logicalExpression: if(
          and(isnull(local!filter.status), isnull(local!filter.priority)),
          null,
          a!queryLogicalExpression(
            operator: "AND",
            filters: {
              if(
                isnull(local!filter.status),
                null,
                a!queryFilter(field: "status.value", operator: "=", value: local!filter.status)
              ),
              if(
                isnull(local!filter.priority),
                null,
                a!queryFilter(field: "priority.value", operator: "=", value: local!filter.priority)
              )
            }
          )
        ),
        pagingInfo: local!pagingInfo
      )
    ),
    a!dashboardLayout(
      contents: {
        a!sectionLayout(
          contents:{
            a!columnsLayout(
              columns:{
                a!columnLayout(
                  contents:{
                    a!dropdownField(
                    label: "Priority",
                    labelPosition: "ADJACENT",
                    placeholderLabel: "All Priorities",
                    choiceLabels: local!allPriorities,
                    choiceValues: local!allPriorities,
                    value: local!filter.priority,
                    saveInto: local!filter.priority
                  ),
                    a!dropdownField(
                    label: "Status",
                    labelPosition: "ADJACENT",
                    placeholderLabel: "All Statuses",
                    choiceLabels: local!allStatuses,
                    choiceValues: local!allStatuses,
                    value: local!filter.status,
                    saveInto: local!filter.status
                  )
                  }
                ),
                a!columnLayout(
                  contents:{
                    a!buttonLayout(
                    secondaryButtons: {
                      if(
                        local!saveFilterError,
                        a!buttonWidget(
                          label: "Could not save filters",
                          disabled: true
                        ),
                        a!buttonWidget(
                          label: "Save Filters",
                          disabled: not( local!filterChanged),
                          saveInto: a!writeToDataStoreEntity(
                            dataStoreEntity: cons!SRS_FILTER_ENTITY,
                            valueToStore: local!filter,
                            onSuccess: {
                              a!save( local!persistedFilter, local!filter)
                            },
                            onError: {
                              a!save( local!saveFilterError, true)
                            }
                          )
                        )
                      )
                    }
                  )
                  }
                )
              }
            )
          }
        ),
        a!gridField(
          totalCount: local!datasubset.totalCount,
          emptyGridMessage: "No Support Requests available",
          columns: {
            a!gridTextColumn(
              label: "Title",
              field: "title",
              data: index( local!datasubset.data, "title", null)
            ),
            a!gridTextColumn(
              label: "Status",
              field: "CSRS_status.value",
              data: index( index( local!datasubset.data, "CSRS_status", null), "value", null)
            ),
            a!gridImageColumn(
              label: "Priority",
              field: "CSRS_priority.value",
              data: if(
                isnull( index( local!datasubset.data, "CSRS_priority", null)),
                {},
                a!forEach(
                  items: index( index( local!datasubset.data, "CSRS_priority", null), "value", null),
                  expression: rule!CSRS_GetIconForPriority( fv!item)
                )
              ),
              size: "ICON"
            )
          },
          value: local!pagingInfo,
          saveInto: local!pagingInfo
        )
      }
    )
  )
)

Test it out

  1. Change the filter dropdowns and notice how the Save Filters button becomes enabled
  2. Click the Test button and notice how the filters are gone. This is equivalent to leaving and returning to the report.
  3. Use the Save Filters button to save a particular combination of filters.
  4. Change the filters again
  5. Now click the Test button again and notice how the filters are put back to their previously saved values.
  6. Change the filters, then change them back. Notice how the Save Filters button becomes disabled when returning to the saved filters.
  7. Try logging in with a different user and trying the same expression (you'll have to give them access to the data store if they don't have it already). Notice how their filter is saved and loaded independently of your original user.

To use as an actual report

  1. Save your interface if you haven't already
  2. From the settings menu (gear icon), click Save as.... This will display the Save Interface As form.
  3. Enter your desired report name and application
  4. Click Save

Notable implementation details

  • The currently selected filter and the persisted filters are kept in separate load() variables so that they can be compared. Not only does this mean the user knows when they have changed the filter (because the button is enabled) but also provides feedback that the filters have been saved (when it becomes disabled).
  • If there's an error writing the filters, the onError parameter is used to change the button label.
  • For simple filters like these, notice they could be saved as soon as the dropdowns are changed (much like the dropdown in the Update an Entity-Backed Record from its Summary View recipe). This would save the user a click but depending on the situation you may need to alert the user to the fact their filter will be saved. If the user doesn't notice the filters were retained when they arrive back, they could be confused or think the report is broken.

See also: Smart Services in SAIL

17.2
FEEDBACK