Free cookie consent management tool by TermsFeed Top Customers and Their Latest Order [Build Reports from Records]
Top Customers and Their Latest Order

This pattern illustrates how to create a grid that shows the top paying customers, their latest order, and their total sum of sales. This pattern also provides a sample scenario to show how you can take common business requirements and quickly turn them into a report.

You'll notice that this pattern provides more than just an expression, it shows you the fastest way to build reports in Design Mode. To get the most out of this pattern, follow the steps in Create this pattern to learn how to build advanced reports using the latest low-code features.

Scenario

Sales executives at the Appian Retail company want to know who are their top paying customers in 2021 so they can send 10 of them exclusive promotions for products purchased in their latest order.

To show the top paying customers, you'll use the pattern on this page to create a read-only grid that displays the customer name, their total sales, and a link to their latest order. You'll also add a filter on the grid so executives can filter the list of customers by a range of total sales.

The report will look like this:

images/sales-by-top-customers.png

Setup

This pattern uses data from the Appian Retail application, available for free in Appian Community Edition. To follow along with this pattern, log in to Appian Community to request the latest Appian Community Edition site.

If you do not see the Appian Retail application available in your existing Appian Community Edition, you can request a new Appian Community Edition to get the latest application contents available.

This pattern will use data from the following record types in the Appian Retail application:

  • Customer record type: Contains individual customers who purchase products. For example, Terry Duffy.
  • Order record type: Contains order information like the order number, date, status, and whether it was purchased online or in stores. For example, order number SO43659 was purchased in stores on 5/31/2019 and the order is closed.

Create this pattern

To create this pattern, you will:

  1. Create a grid with customers and their orders.
  2. Limit and sort orders to only return the latest order.
  3. Add a custom record field on the Customer record type to calculate total sales per customer.
  4. Display the total sales per customer on the grid.
  5. Sort the grid by the highest paying customer.
  6. Add a user filter to filter the grid by total sales.

Step 1: Create a grid with customers and their orders

First, you need to create the read-only grid using the Customer record type.

To create the grid:

  1. In the Appian Retail application, go to the Build view.
  2. Click NEW > Interface.
  3. Configure the interface properties and click CREATE.
  4. From the PALETTE, drag a READ-ONLY GRID component into the interface.
  5. From Data Source, select RECORD TYPE and search for the Customer record type. The grid populates with a Name column and an empty Latest Order column.
  6. Click the Latest Order column to configure the display value.

  7. From Display Value, use the dropdown to hover over the order relationship and select the orderNumber field. The field will display as order.orderNumber.

    images/one-to-many-data-pattern.png

Step 2: Limit and sort the related data

Right now, up to 10 related customer orders appear in each row of the grid column. This is because the Customer record type has a one-to-many relationship with the Order record type (i.e. one customer can have many orders).

Since executives only want to see the latest 2021 orders, you can filter, sort, and limit the one-to-many data using the a!relatedRecordData() function. To allow executives to quickly reference the latest order in 2021, you'll also add a record link to the latest order.

To filter, sort, and limit the related data:

  1. Return to the Read-only Grid component configuration.
  2. Click FILTER RELATED RECORDS.
  3. Hover over Related Record Data and click Edit as Expression .
  4. Replace the existing expression with the following:

    Note:  These record type references are specific to the Appian Retail application. If you're following along in the Appian Retail application, you can copy and paste this expression without updating the record type references.

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

     {
   a!relatedRecordData(
     relationship: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{0143bf2f-ae73-4534-bf44-8448c1c5f4f1}order',
     limit: 1,
     filters: a!queryFilter(
       field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
       operator: "BETWEEN",
       value: {
         todatetime("01/01/2021"),
         todatetime("12/31/2021")
       }
     ),
     sort: a!sortInfo(
       field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
       ascending: false
     )
   )
 }

    In the expression, notice that the filters and sort parameters starts from recordType!Order instead of using a relationship reference (recordType!Customer.relationships.order...). This is because you've already specified the record type relationship in the relationship parameter, so you don't need to reference it again in the sort. This same behavior applies if you want to add a filter.

  5. Click OK. Only the latest order in 2021 appears in the grid column.

Now that the latest order appears, you'll add a record link so executives can quickly view information about that order.

To add a record link:

  1. Click the Latest Order column.
  2. Hover over Display Value and click Edit as Expression .

  3. Replace the existing expression with the following:

    1
2
3
4
5
6
7
8
9

     a!linkField(
   links: {
     a!recordLink(
       label: fv!row['recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{0143bf2f-ae73-4534-bf44-8448c1c5f4f1}order.fields.{3f2ba6f1-4afd-4a21-afd1-f399eb6c18e0}orderNumber'],
       recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
       identifier: fv!row['recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{0143bf2f-ae73-4534-bf44-8448c1c5f4f1}order.fields.{262bb249-cf34-4171-a573-54831d0958dd}orderId']
     )
   }
 )
  4. Click OK.
  5. Click SAVE CHANGES.

Step 3: Calculate total sales per customer

Now that you can see customers and their latest order, you need to calculate the sum of orders for each customer.

Since there's a one-to-many relationship between the Customer and Order record type, you can create a custom record field using the Aggregate Related Record Fields template to calculate and store this value.

Custom record fields allow you to transform existing data into new fields in the record type. This makes it easy to build your reports since you can define your calculations once and seamlessly reuse them in your charts, grids, and queries.

To create an aggregated custom record field:

  1. In the Appian Retail application, open the Customer record type.
  2. Click NEW CUSTOM RECORD FIELD.
  3. From SELECT A TEMPLATE, choose Aggregate Related Record Fields.
  4. Click NEXT.
  5. For Field, use the dropdown to hover over the order relationship and select the subTotal field. The field will display as order.subTotal.
  6. For Aggregation Function, select Sum of.
  7. To add a filter on the aggregation, enable the Filter related record values? toggle and configure the following:
    • For Field, select orderDate.
    • For Operator, select Date Range.
    • For Value, use the context menu () to select Date and Time.
    • For the first value, choose January 1, 2021 (01/01/2021) and 12:00 AM.
    • For the second value, choose December 31, 2021 (12/31/2021) and 12:00 AM.

    Tip:  In your own applications, you can use Date Presets to dynamically filter data. This pattern uses a static date for testing purposes.

  8. Click TEST to verify the results.
  9. Click NEXT.
  10. For Name, enter totalSales2021.
  11. Click CREATE. The custom record field now displays as another field in the record type.
  12. Click SAVE CHANGES.

Step 4: Display total sales per customer on the grid

Now, you'll reference the custom record field as a new column in the grid. To display the value in a dollar amount, you'll use the a!currency() function.

To add the total sales per customer on the grid:

  1. Return to your interface.
  2. In the Read-only Grid component configuration, click ADD COLUMN.
  3. Click Grid Column to configure the column label and display value.
  4. For Label, enter Total Sales (2021).
  5. Hover over Display Value and click Edit as Expression .

  6. In the Display Value dialog, enter the expression below. This expression will display the custom record field values in a dollar amount, and any null values will appear as a hyphen.

    Note:  You cannot copy and paste this expression since your custom record field will have a different UUID. Use this as a reference only.

    1
2
3
4
5
6
7
8
9
10

     if(
   isnull(
     fv!row[recordType!Customer.fields.totalSales2021]
   ),
   "-",
   a!currency(
     isoCode: "USD",
     value: fv!row[recordType!Customer.fields.totalSales2021]
   )
 )
  7. Click OK.

Step 5: Sort the grid by the highest paying customer

To show the highest paying customers at the top of the grid, you'll sort the grid by the salesYTD custom record field.

To sort the grid:

  1. Return to the Read-Only Grid component configuration.
  2. For Initial Sorts, click ADD SORT.
  3. For Field, select totalSales2021.
  4. For Order, keep the default selection of Descending.
  5. Click TEST in the title bar to preview the sort.
  6. Return to the Read-Only Grid component configuration.
  7. Expand the LAYOUT section of the Read-only Grid configuration.
  8. In Label, enter Top Customers in 2021.

  9. Click SAVE CHANGES.

    images/sort-sales-by-top-customers.png

Step 6: Add a user filter to filter the grid by total sales

In this final step, you'll create a user filter to allow sales executives to filter the list of top paying customers by a range of total sales. Once you create the user filter, you can easily reuse it on the read-only grid.

To create the user filter:

  1. Return to the Customer record type.
  2. Go to Filters.
  3. In the User Filters section, click New User Filter. The Create New User Filter dialog appears.
  4. In Name, enter Total Sales.
  5. In Label, enter "Total Sales".
  6. In Field, select totalSales2021.
  7. Under List Configuration, click New Option. The Edit Filter Option dialog appears.
  8. In Option Label, enter "Between 0 and 1 Million".
  9. In Operator, select between.
  10. In Value, enter 1.
  11. In Value 2, enter 1000000.
  12. Click SAVE FILTER OPTION.

  13. Click New Option and configure the rest of the list options:

    Option Label Operator Value Value 2
    "Between 1 and 2 Million" between 1000001 2000000
    "Between 2 and 3 Million" between 2000001 3000000

    The user filter should look like this:

    images/user-filter-report-recipe.png

  14. Click OK.
  15. Click SAVE CHANGES.
  16. Close the Customer record type.

Now that you have the user filter, you can add it to your grid.

To add the user filter to your grid:

  1. Return to your interface.

  2. In the Read-only Grid component configuration, under User Filters, click ADD USER FILTER.

    Tip:  If the button is disabled, refresh your interface.

  3. From the dropdown, select the Total Sales user filter.

  4. Click SAVE CHANGES.

    images/use-user-filters-report-recipe.gif

Full expression

The resulting expression will look like this:

Note:  To copy and paste this expression into an interface in the Appian Retail application, you must complete step 3 to create the custom record field, and step 6 to create the user filter in this expression.

Once you create the custom record field and the user filter, you'll need to update the references to those objects in the 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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

{
  a!gridField(
    label: "Top Customers in 2021",
    labelPosition: "ABOVE",
    data: a!recordData(
      recordType: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer',
      relatedRecordData: {
        a!relatedRecordData(
          relationship: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{0143bf2f-ae73-4534-bf44-8448c1c5f4f1}order',
          limit: 1,
          sort: a!sortInfo(
            field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
            ascending: false
          ),
          filters: a!queryFilter(
            field: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order.fields.{fbcc99f6-1ddf-4923-903b-18122a1737c6}orderDate',
            operator: "BETWEEN",
            value: {
              todatetime("01/01/2021"),
              todatetime("12/31/2021")
            }
          )
        )
      }
    ),
    columns: {
      a!gridColumn(
        label: "Name",
        sortField: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{ab3f9d64-6b2e-4bd6-9289-a03dc5e7d386}person.fields.{120551bc-43ca-4057-8d03-dd69fcd018ed}fullName',
        value: a!linkField(
          links: {
            a!recordLink(
              label: fv!row['recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{ab3f9d64-6b2e-4bd6-9289-a03dc5e7d386}person.fields.{120551bc-43ca-4057-8d03-dd69fcd018ed}fullName'],
              recordType: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer',
              identifier: fv!row['recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.fields.{aaa21651-214a-4462-918f-fbe369e86b94}CustomerID']
            )
          }
        )
      ),
      a!gridColumn(
        label: "Latest Order",
        sortField: 'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{0143bf2f-ae73-4534-bf44-8448c1c5f4f1}order.fields.{3f2ba6f1-4afd-4a21-afd1-f399eb6c18e0}orderNumber',
        value: a!linkField(
          links: {
            a!recordLink(
              label: fv!row['recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{0143bf2f-ae73-4534-bf44-8448c1c5f4f1}order.fields.{3f2ba6f1-4afd-4a21-afd1-f399eb6c18e0}orderNumber'],
              recordType: 'recordType!{ad898682-e651-4b2d-af67-47c1fcb1171f}Order',
              identifier: fv!row['recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.relationships.{0143bf2f-ae73-4534-bf44-8448c1c5f4f1}order.fields.{262bb249-cf34-4171-a573-54831d0958dd}orderId']
            )
          }
        ),
        align: "START"
      ),
      /* Replace these fields with the totalSales2021 custom record field in your application */
      a!gridColumn(
        label: "Total Sales (2021)",
        value: if(
          isnull(
            fv!row[recordType!Customer.fields.totalSales2021]
          ),
          "-",
          a!currency(
            isoCode: "USD",
            value: fv!row[recordType!Customer.fields.totalSales2021]
          )
        )
      )
    },
    /* Replace this field with the totalSales2021 custom record field in your application */
    initialSorts: {
      a!sortInfo(
        field: recordType!Customer.fields.totalSales2021
      )
    },
    validations: {},
    refreshAfter: "RECORD_ACTION",
    userFilters: {
      'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.filters.{2dcd60f9-6620-412a-860d-3f3730315143}Total Sales'
    },
    showSearchBox: true,
    showRefreshButton: true
  )
}

Top Customers and Their Latest Order

FEEDBACK