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.
Sales executives at the Appian Retail company want to know who are their top paying customers this year 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:
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:
To create this pattern, you will:
First, you need to create the read-only grid using the Customer record type.
To create the grid:
From Display Value, use the dropdown to hover over the order relationship and select the orderNumber field. The field will display as order.orderNumber.
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 order, you can sort and limit the one-to-many data using the a!relatedRecordData() function. To allow executives to quickly reference the latest order, you'll also add a record link to the latest order.
To limit and sort the related data:
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
{
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
)
)
}
In the expression, notice that the sort 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.
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:
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']
)
}
)
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:
Click TEST to verify the results.
salesYTD
.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:
Total Sales (YTD)
.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
if(
isnull(fv!row[recordType!Customer.fields.salesYTD]),
"-",
a!currency(
isoCode: "USD",
value: fv!row[recordType!Customer.fields.salesYTD]
)
)
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:
Top Customers This Year
.Click SAVE CHANGES.
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:
Total Sales
."Total Sales"
."Between 0 and 1 Million"
.1
.1000000
.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:
Now that you have the user filter, you can add it to your grid.
To add the user filter to your grid:
In the Read-only Grid component configuration, under User Filters, click ADD USER FILTER.
Tip: If the button is disabled, refresh your interface.
Click SAVE CHANGES.
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
{
a!gridField(
label: "Top Customers This Year",
labelPosition: "ABOVE",
instructions: "",
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
)
)
}
),
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"
),
a!gridColumn(
label: "Total Sales (YTD)",
sortField: 'recordType!Customer.fields.salesYTD',
value: if(
isnull(
fv!row['recordType!Customer.fields.salesYTD']
),
"-",
a!currency(
isoCode: "USD",
value: fv!row['recordType!Customer.fields.salesYTD']
)
),
align: "START"
)
},
initialSorts: {
a!sortInfo(
field: 'recordType!Customer.fields.salesYTD',
ascending: false
)
},
validations: {},
refreshAfter: "RECORD_ACTION",
userFilters: {'recordType!{1b00c9c1-c2a1-455c-b204-1e6ec5c448a1}Customer.filters.{b9c4d88c-133c-4dc3-bd28-cefe15392617}Total Sales',
null
},
showSearchBox: true,
showRefreshButton: true
)
}
Top Customers and Their Latest Order