This page explains how to create custom record fields.

Overview

Appian Records makes it easy to leverage your enterprise data seamlessly throughout your applications, but there are times when your data isn’t enough on its own. Instead, you may need to aggregate, extract, or change your existing data to show specific insights to your users.

For example, in a Case record type, you may have a createdOn field and a closedOn field to show when each case was created and closed. However, account managers need to know how long each case was open to ensure the company is meeting their service-level agreements (SLAs).

When you sync your data in Appian, you can transform and manipulate your existing record data into the insights you need using custom record fields. You can create custom record fields that calculate the difference between two dates, concatenate a first and last name, aggregate related record fields, and more.

Since you’re creating these custom fields directly on your record type, you can reference them as quickly and easily as any other record field. This means that you can use custom record fields to build your charts, to sort and display data in grids, or as filter options in a user filter.

To learn more about using your record fields and custom record fields, see:

• Build Reports from Records
• Configure Charts Using Records
• Configure Read-Only Grids

About custom record fields

Custom record fields allow you to calculate, simplify, or transform your existing data into new fields on the record type.

Unlike database views, which often require technical experts to establish and maintain your data manipulations, custom record fields can be easily created by any developer.

To create a custom record field, you can use:

• Guided templates: A guided experience to create your custom record field.
• Expression templates: An expression prepopulated with Custom Record Field functions used to create your custom record field.

These templates allow you to address common business requirements, like calculating the sum of customer orders or the number of days a case was open. If your business needs are more complex or you need more flexibility, you can also write your own custom record field expression.

Once you create your custom record field, it will appear in the list of record fields with a calculator icon . You can then reference your custom record field in your interfaces and expressions using the recordType! domain.

The sections below explain how to configure a custom record field using a guided template, an expression template, or by writing your own expression.

Guided templates

Guided templates allow you to create a custom record field using a step-by-step wizard. This codeless approach to custom record fields makes it easy to categorize your data, extract dates, and even aggregate related record fields.

Create custom record fields

To create a custom record field using a guided template:

1. In the record type, go to Data Model.
2. Click NEW CUSTOM RECORD FIELD.
3. From SELECT A TEMPLATE, choose one of the following guided templates:
   • Groups based on a range
   • Groups based on text values
   • Groups based on a date difference
   • Extract partial dates
   • Aggregate related record fields
4. Click NEXT.
5. For CONFIGURE VALUES, follow the template to configure the custom record field values.
6. Click TEST to preview your custom record field values using your record data or test values.
   • Select View Record Data to test your configuration against your existing record fields. This is selected by default, and is useful when you already have production-like data in your environment.
   • Select Enter Test Values to return custom record field values based on sample values. You can test up to five values. This option is useful when you want to test specific scenarios, or if you don't have data in your environment.

   Note:  This option is not available on the Aggregate related record fields template.

7. Click NEXT.
8. Under SET NAME AND TYPE, configure the following:
   • For Name, enter a name for the custom record field. By default, the name is the record field used in the configuration plus the template name.
   • The Type will default to the appropriate data type based on your selected template.
9. Depending on your selected template, you may also configure an Error Value to display if there is an error evaluating a value in the custom record field. To configure this value:
   • Choose Null to display a null value.
   • Choose Custom to enter a custom error value. The custom error value must be a literal value of the same data type as the custom field. You cannot create custom error values for custom record fields of type User.
10. Click CREATE.

Groups based on a range

The Groups based on a range template uses a range to organize your numerical data into groups. This template only supports record fields of type Number (Integer) or Number (Decimal).

To use this template, determine the range of values to include in each group. The group name will replace the numerical values in the new custom record field. You must have a minimum of two groups, and the last group will always include any remaining values that were not specifically placed in a group.

For example, let's say you have a record field called price, and you want to create a chart that shows the distribution of prices in your orders.

Using this template, you can create a new custom record field called priceGroup so all prices that are less than or equal to $10 display as Low, prices greater than $10 and less than or equal to $30 display as Medium, and all other prices display as High.

Once you create the custom record field, you can use the record type to build a chart and easily display the count of items by price group.

Groups based on text values

The Groups based on text values template allows you to organize the text values from a record field into groups. This template only supports record fields of type Text.

To use this template, specify which text values to add to each group. Use the Contains operator to include any record field values that contain a partial match to the specified keyword or keywords, or use the In operator to specify the exact record field values to include in the group.

When you add text values to a group, the group name will replace those values in the new custom record field. You must have a minimum of two groups, and the last group will always include any remaining values that were not specifically placed in a group.

For example, say you have a record field called title that contains all job titles in your organization, and you want to create a pie chart that displays the number of employees per functional area.

Using this template, you could combine all titles that contain the keyword "Software" or "Quality" into a group called Engineers. This means any employees with titles like Associate Software Engineer, Quality Engineer II, or Lead Software Engineer will appear as Engineering in the custom record field. You could create a group for each functional area using this same logic, and include any remaining titles in the Other group.

With your new custom record field, you can now build a pie chart that displays the number of employees per functional area.

Groups based on a date difference

The Groups based on a date difference template allows you to calculate the difference between two dates and organize the difference into groups. This template requires two record fields of type Date or Date and Time.

To use this template, you'll first choose a unit of time like Days, Weeks, Months, or Years to calculate the difference between the two dates. The difference is returned as an Integer, which can then be organized into groups. You must have a minimum of two groups, and the last group will always include any remaining values that were not specifically placed in a group.

For example, say you have the record fields dateAssigned and dateClosed and you want to display the number of days it took to close each ticket on your record list. Using this template, you can create a custom record field that calculates the number of days between the ticket's assigned date and closed date. Then, you can organize the differences into groups:

• The One day group includes values less than or equal to 1 day.
• The One week group includes values greater than 1 and less than or equal to 7.
• The Over a week group includes all remaining values.

But what if the ticket hasn't been closed? This would make the dateClosed null. To handle a null date, you can specify a Default Value to appear on the custom record field. In this example, we could set the Default Value to In Progress.

Now you can easily reference this new custom record field in your record list, and even sort and filter the list by this new field.

Extract partial dates

The Extract partial dates template allows you to return a particular value from a Date or Date and Time record field.

To use this template, select a Date or Date and Time field, then select the Unit of Time that you want to extract. You can choose to extract the day, month, or year from the field, or calculate the day of the week, the week of the year, or the quarter from a given date.

For example, say you have a Date and Time field called dateSubmitted, but you only need to know the date a ticket was submitted, not the time. You could use this template to create a custom record field called shortDateSubmitted to extract the date from the dateSubmitted field and set the new field to type Date.

Now, you can use this field to display submission dates on your record list, record views, or in a report.

Aggregate related record fields

The Aggregate related record fields template allows you to aggregate and filter a related record field from a one-to-many relationship. You can use this template when your record type has a one-to-many relationship, or when a related record type has a one-to-many relationship.

To use this template, select a related record and choose an Aggregation Function to calculate the sum, average, count, distinct count, maximum, or minimum of the related record field. If needed, you can also apply one or more filters to your aggregation to determine which values are included in the calculation.

For example, let's say you have a Customer record type that has a one-to-many relationship with the Case record type, and you want to create a report that shows the number of open cases associated with each customer.

On the Customer record type, you can use this template to create a custom record field that calculates the count of Ids from the Case record type that have a status of Open.

Once you create the field, the aggregation will automatically be grouped by the common field shared between the two record types. In this example, that would be the customer Id. You could then reference this field in a KPI or read-only grid to quickly and easily display an aggregations without any additional queries.

"At least one" filter behavior

In the example above, you are aggregating and filtering on the same related record type (the Case record type). Now, let's look at an example where you aggregate and filter on different related record types.

For example, let's say the Customer record type also has a one-to-many relationship with the Order record type, and you want to create a report that shows, for each customer, how many orders include a clearance item.

In this example, the order item information is on a separate record type called Order Item. Since the Order record type has a relationship with the Order Item record type, you can reference related record fields from the Order Item record type in the custom record field.

To create this custom record field on the Customer record type, you would get the count of Ids from the Order record type, then apply a filter on the isClearance field from the Order Item record type.

Since there is a one-to-many relationship between the Order and Order Item record types, the custom field will count any orders that have at least one clearance item. This at least one filter behavior occurs when there is a one-to-many relationship between where you are aggregating and where you are filtering.

Learn more about "at least one" filter behavior.

Filter by dates

When you filter the aggregation by a date or date and time field, you can select a Date Preset as your filter value.

These presets will dynamically filter your data based on the selected time frame. For example, if you select the date preset Today, the filter value will update each day to reflect the correct date. When you use a Date Preset, all date and time values are returned in Greenwich Mean Time (GMT).

The tables below lists the available date presets for Date or Date and Time related record fields. To ensure you're selecting the correct date preset, review the underlying expression associated with each preset.

Date presets for Date fields

Date presets for Date and Time fields

Expression templates

Expression templates are prepopulated with the functions and syntax you need to configure a custom record field.

These templates leverage powerful Custom Record Field functions, which allow you to reference record fields, related record fields, and even relative dates (like today() or now()) within your expression.

For example, the a!customFieldDateDiff() function allows you to create a custom record field that returns the difference between two dates. Using this function, you can subtract a record field (like createdOn) from today's date and time, or simply subtract two Date fields.

To keep your calculations up-to-date, Custom Record Field functions are calculated in real-time. Whenever you reference a custom record field that uses one of these functions, the expression is evaluated to ensure it’s using the latest data.

This real-time evaluation is what allows you to reference related record fields and relative dates in your expressions. For example, since the today() function returns a new value each day, the a!customFieldDateDiff() function will evaluate in real-time to ensure the latest date is used in your custom record field calculation.

Create custom record fields

There is currently one expression template available, the Date Difference template.

To create a custom record field using this expression template:

1. On the record type, go to Data Model.
2. Click NEW CUSTOM RECORD FIELD.
3. From SELECT A TEMPLATE, choose Date Difference.
4. Click NEXT.
5. The Expression is populated with the a!customFieldDateDiff function and is configured with a record field reference. You can replace the values in the expression with any of the following values:
   • Record field or related record field references. You must reference fields using the recordType! domain. For example, recordType!Case.fields.createdOn.
   • Relative date and time functions, like today() or now().
   • Constants.
   • Other supported functions.
6. Click TEST to preview your custom record field values using your record data.
7. Click NEXT.
8. Under SET NAME AND TYPE, configure the following:
   • For Name, enter a name for the custom record field.
   • The Type is set to Number (Integer).
9. Click CREATE.

Date difference

The Date Difference template allows you to return the difference between two dates using the a!customFieldDateDiff function.

This function allows you to subtract two record fields or related record fields of type Date or Date and Time. You can also use relative date and time functions like today() or now() to subtract by the current date.

For example, let's say you want to know how many days are left between today and delivery due date for an order. From the Order record type, you can create a custom record field using this template to calculate the difference between today's date and the order's delivery due date.

The expression would look like this:

1
2
3
4
5
a!customFieldDateDiff(
 startDate: today(),
 endDate: recordType!Order.fields.deliveryDueDate,
 interval: "DAY"
)

Then, you can use this custom record field in a KPI, chart, or grid. For example, the chart below uses the above custom record field to group the number of orders by days left until their delivery due date.

Write your own expression

If your business needs are more complex and cannot be satisfied by a template, you can write your own custom record field expression.

Within the expression, you can use record fields and supported functions to configure your custom record field.

Since Custom Record Field functions are only supported in expression templates, you cannot reference the following values when you write your own expression:

• Related record fields.
• Relative date and time functions, like today() or now().
• Constants.

See the following sections to learn how to write your own expression, as well as some sample recipes you can use to write your own custom record field expression.

Write your own custom record field expression

To write your own custom record field expression:

1. On the record type, go to Data Model.
2. Click NEW CUSTOM RECORD FIELD.
3. From SELECT A TEMPLATE, choose Write Your Own Expression.
4. Click NEXT.

5. Use supported functions and record fields to configure your expression. You must reference a record field using rv!record followed by a record field reference in brackets. For example, rv!record[recordType!Case.fields.name].

   Tip:  See the sample recipes below to see how you can write your own custom record field expression.

6. Click NEXT.
7. Under SET NAME AND TYPE, configure the following:
   • For Name, enter a name for the custom record field.
   • For Type, choose the appropriate data type.
8. For Error Value, choose the value to display if there is an error evaluating a value in the custom record field:
   • Choose Null to display a null value.
   • Choose Custom to enter a custom error value. The custom error value must be a literal value of the same data type as the custom field. You cannot create custom error values for custom record fields of type User.
9. Click CREATE.

Recipe: concatenate existing values

Goal: Create a new custom record field that concatenates existing record fields and displays them as one value.

In this example, we will use the concat() function to concatenate the record fields street, city, state, and zipCode from the Employee record type into a new custom record field called address:

1
2
3
4
concat(rv!record[recordType!employee.fields.street], ", ", 
rv!record[recordType!employee.fields.city],  ",  ", 
rv!record[recordType!employee.fields.state], " ", 
rv!record[recordType!employee.fields.zip]) 

Recipe: replace null values

Goal: Create a new custom record field that displays the null values in a record field as "N/A".

In this example, we'll create a new custom record field called cleanReference that displays any null values from the reference record field as "N/A". If the value is not null, then the record value will display.

1
a!defaultValue(rv!record[recordType!applicant.fields.reference], "N/A")

Recipe: use conditional logic with two record fields

Goal: Create a new custom record field with values based on the conditions of two existing fields.

In this example, we'll use the record fields urgency and supportLevel to determine a ticket's priority level. Based on the value of each field, the new custom record field will display values of "High", "Medium", or "Low".

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
if(
	  and(
	    rv!record[recordType!case.fields.urgency] = 1,
	    rv!record[recordType!case.fields.supportLevel] = 3
	    ),
	  "High",
	  if(
	    and(
	      rv!record[recordType!case.fields.urgency] = 2,
	      rv!record[recordType!case.fields.supportLevel] = 2
	    ),
	  "Medium",
	  "Low"
	  )
	)

Recipe: create percentages

Goal: Create a new custom record field that performs a calculation on existing values and displays the values as a percentage.

In this example, we'll calculate the return on investment (ROI) using the record fields revenue and totalExpenses. When you add this field in a report or grid, you can use rich text to add the percent sign. For example, rv!record[recordType!roi] & "%".

1
2
(rv!record[recordType!sales.fields.revenue] - rv!record[recordType!sales.fields.totalExpenses]) 
/ rv!record[recordType!sales.fields.totalExpenses] * 100

Recipe: perform arithmetic calculations

Goal: Create a new custom record field that uses arithmetic to calculate its values.

In this example, we'll use the record fields price, cost, and unitsSold to calculate the values for the new custom record field called profit.

1
2
(rv!record[recordType!Sales.fields.price] - rv!record[recordType!Sales.fields.cost])
* fv!record[recordType!Sales.fields.unitsSold]

Supported functions

When you use an expression template or write your own expression, you can use different functions to configure your custom record field.

Expression templates must use a Custom Record Field function to create the field values. Within a Custom Record Field function, you can leverage other functions as well. See a list of supported functions for a!customFieldDateDiff().

Custom Record Field functions are not supported when you write your own expression, or when you edit a guided template in expression mode. Instead, you can use the following functions to write your custom record field expression.