Create Custom Record Fields

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.

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.

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).

To easily display this information, you can create a new custom record field on the Case record type called slaStatus. This field will calculate the difference between the created on and closed on date for each case, and displays a value of On Time or Overdue based on the difference.

Since custom record fields are created directly on your record type, you can reference them as quickly and easily as any other record field. Use your custom record fields to build 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 in the record type. This allows you to fill in gaps within your data, display new insights, or even just clean up your data, so you can build reports and queries with the data you need.

You can create custom record fields on any record type with data sync enabled. We have several templates to get you started creating your custom record fields—some are guided, and others provide a prepopulated expression with the functions you need.

You’ll choose which template to configure your custom record field based on your use case and when you want the field to be evaluated.

Custom record fields can be evaluated at two different times:

• At sync time: The custom record field values are calculated whenever a sync occurs in the record type.

• In real time: The custom record field values are calculated whenever the field is actively referenced in your applications.

All custom record field templates are preset to be evaluated at either sync time or in real time. In the following sections, templates that evaluate at sync time are denoted with a icon, while templates that evaluate in real time are denoted with a .

If your business needs are more complex or you need more flexibility, you can also write your own custom record field expression. When you write your own expression, you can choose when the field values are evaluated.

Sync-time evaluations

When a custom record field evaluates at sync time, the field values are only calculated whenever a sync occurs in the record type. For example, when a scheduled sync occurs or when you use the Sync Records smart service to sync specific data, the custom record field values will be reevaluated.

Sync-time evaluations are useful when your field values are not time sensitive, or when you do not require related data.

For example, you could create a custom record field that evaluates at sync-time in order to concatenate two fields on your record type, like firstName and lastName.

Sync-time evaluations also allow you to leverage numerous functions, like if(), a!match(), or a!localVariables() so you can create conditional logic or more complex expressions.

For example, you could create a custom record field that evaluates at sync-time to returns the value High when a case has an urgency of 1 and a supportLevel of 3. Otherwise, return the value Low.

See any template that evaluates at sync-time, or any sync-time evaluation recipes for more examples.

Real-time evaluations

When a custom record field evaluates in real time, the field values are calculated whenever the field is actively referenced in your applications. For example, when an end user views a bar chart or grid that contains the custom record field.

Since real-time evaluations occur more frequently, they are useful when you need to reference relative dates, like today’s date and time, or when you need to reference related record fields.

For example, you could create a custom record field that evaluates in real time in order to subtract the createdOn date from today’s date. With more frequent evaluations, you can be confident that your calculation is always referencing the most current date.

Real-time evaluations leverage a special set of functions called custom field functions. These functions allow you to reference record fields, related record fields, and other custom record fields. Within each custom field function, you can leverage other supported functions like today() and now() in your calculations.

You must use at least one custom field function when creating a custom record field that evaluates in real time. The available custom field functions include:

• a!customFieldConcat()

• a!customFieldCondition()

• a!customFieldDateDiff()

• a!customFieldDefaultValue()

• a!customFieldDivide()

• a!customFieldLogicalExpression()

• a!customFieldMatch()

• a!customFieldMultiply()

• a!customFieldSubtract()

• a!customFieldSum()

See any template that evaluates in real time, or the real-time evaluation recipes below for more examples.

Aggregate Related Record Fields (Real-time evaluation)

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.

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 gets the count of Ids from the Case record type, and filters on the status field so you only count cases with 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.

Use this template

To use this template:

1. In the record type, go to Data Model.
2. Click NEW CUSTOM RECORD FIELD.
3. From SELECT A TEMPLATE, choose Aggregate Related Record Fields.
4. Click NEXT.
5. For Field, select the related record field you want to aggregate.
6. For Aggregation Function, choose how you want to aggregate the field.
7. To filter the aggregation, enable Filter related record values?
8. Configure the following properties:
   • Field: The field to filter on.
   • Operator: The operator to apply to the selected field.
   • Value: The value to compare to the given field using the given operator. Use the context menu () to specify how you pass in the value: using a static value, a date preset, or a constant.

   Note:  If there is a one-to-many relationship in the path between where you are aggregating data and where you are filtering, then you will observe “at least one” filter behavior.

9. Click + Add Condition to add additional filter configurations. Choose how multiple filters are evaluated by selecting one of the following operators:
   • "OR"
   • "AND"
   • "AND_ALL"

   Tip:  Appian recommends using the "AND_ALL" operator whenever you filter by multiple fields from the same one-to-many relationship. Learn more about this operator.

10. Click TEST to preview your custom record field values using your record data.
11. Click NEXT.
12. Enter a Name for the custom record field. This is how you’ll reference the field in your applications.
13. Click CREATE.

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).

See Date Presets for Filters to see the underlying expression associated with each preset.

Extract Partial Dates (Sync-time evaluation)

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

For example, let's 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.

Use this template

To use this template:

1. In the record type, go to Data Model.
2. Click NEW CUSTOM RECORD FIELD.
3. From SELECT A TEMPLATE, choose Extract Partial Dates.
4. Click NEXT.
5. For Field, select the Date or Date and Time record field you want to extract the date from.
6. For Unit of Time, select which part of the date you want to extract.
7. Click NEXT.
8. Enter a Name for the custom record field. This is how you’ll reference the field in your applications.
9. For Error Value, choose the value to display if there is an error evaluating a custom record field value.
10. Click CREATE.

Groups Based on a Date Difference (Sync-time evaluation)

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.

For example, let’s say you have the record fields dateAssigned and dateClosed and you want to know whether a case was closed on time or overdue based on the service-level agreement (SLA).

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 On Time group includes values less than or equal to 5 days.
• The Overdue 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.

Use this template

To use this template:

1. In the record type, go to Data Model.
2. Click NEW CUSTOM RECORD FIELD.
3. From SELECT A TEMPLATE, choose Groups Based on a Date Difference.
4. Click NEXT.
5. For Step 1: Calculate Difference, configure the following:
   • Start Date: The starting Date or Date and Time record field to subtract.
   • End Date: The ending Date or Date and Time record field to subtract.
   • Interval: The unit of time to calculate the difference.
6. For Step 2: Create Groups, configure the following:
   • Number of Groups: The number of groups you want to categorize. You must have at least two groups.
   • Custom Field Value: The name of the field value to return based on the difference. The final Custom Field Value will display for any values that are not included in the other groups.
7. For Step 3: Handle Null Start Date or End Date Values, enter a Default Value. This is the value that will display if the Start Date or End Date is null. You can only enter a static text value.
8. Click NEXT.
9. Enter a Name for the custom record field. This is how you’ll reference the field in your applications.
10. For Error Value, choose the value to display if there is an error evaluating a custom record field value.
11. Click CREATE.

Groups Based on a Range (Sync-time evaluation)

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).

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.

Use this template

To use this template:

1. In the record type, go to Data Model.
2. Click NEW CUSTOM RECORD FIELD.
3. From SELECT A TEMPLATE, choose Groups Based on a Range.
4. Click NEXT.
5. For Create Groups From, select a Number (Integer) or Number (Decimal) record field to base the range off of.
6. For Number of Groups, select the number of groups you want to categorize. You must have at least two groups.
7. For Custom Field Value, enter the name of the field value to return based on the specified range. The final Custom Field Value will display for any values that are not included in the other groups.
8. Click NEXT.
9. Enter a Name for the custom record field. This is how you’ll reference the field in your applications.
10. For Error Value, choose the value to display if there is an error evaluating a custom record field value.
11. Click CREATE.

Groups Based on Text Values (Sync-time evaluation)

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.

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.

Use this template

To use this template:

1. In the record type, go to Data Model.
2. Click NEW CUSTOM RECORD FIELD.
3. From SELECT A TEMPLATE, choose Groups Based on Text Values.
4. Click NEXT.
5. For Create Groups From, choose the Text record field you want to create groups from.
6. For Number of Groups, select the number of groups you want to categorize. You must have at least two groups.
7. For Custom Field Value, enter the name of the field value to return based on the specified Record Field Values. The final Custom Field Value will display for any values that are not included in the other groups.
8. For Operator, choose an operator:
   • Use the Contains operator to include any record field values that contain a partial match to the specified keyword or keywords.
   • Use the In operator to specify the exact record field values to include in the group.
9. For Record Field Value, enter a keyword or record field value to determine which values are included in the group.
10. Click NEXT.
11. Enter a Name for the custom record field. This is how you’ll reference the field in your applications.
12. For Error Value, choose the value to display if there is an error evaluating a custom record field value.
13. Click CREATE.

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.

When you write your own expression, you’ll choose whether the field evaluates at sync-time or in real-time. Depending on your selection, the functions and fields you can use to create your custom record field will vary.

The table below outlines the key configuration differences between sync-time and real-time field evaluations:

See the recipes below for examples configuring custom record fields that evaluate at sync-time or in real-time.

Sync-time evaluation recipes

The following recipes illustrate how to create a custom record field that evaluates at sync-time. Each of these recipes leverage one or more record fields and supported functions.

Concatenate values from the base record type

Goal: Create a new custom record field that concatenates 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]) 

Replace null values with a static value

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")

Use conditional logic with two record fields

Goal: Create a new custom record field with values based on the conditions of two record 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
a!match(
  whenTrue:and(
	    rv!record[recordType!Case.fields.urgency] = 1,
	    rv!record[recordType!Case.fields.supportLevel] = 3
	    ),
  then: "High",
  whenTrue: and(
	      rv!record[recordType!Case.fields.urgency] = 2,
	      rv!record[recordType!Case.fields.supportLevel] = 2
	    ),
  then: "Medium",
  default: "Low"
)

Add, subtract, multiply, and divide record fields

Goal: Create a new custom record field using arithmetic operators to add, subtract, multiply, or divide record fields and displays the resulting 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])
* rv!record[recordType!Sales.fields.unitsSold]

Create percentages using record fields

Goal: Create a new custom record field that performs a calculation on record field 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!Sales.fields.roi] & "%".

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

Real-time evaluation recipes

The following recipes illustrate how to create a custom record field that evaluates in real time.

Each of these recipes leverages one or more custom field functions. You must use at least one of these functions to create a custom record field that evaluates in real time:

• a!customFieldConcat()

• a!customFieldCondition()

• a!customFieldDateDiff()

• a!customFieldDefaultValue()

• a!customFieldDivide()

• a!customFieldLogicalExpression()

• a!customFieldMatch()

• a!customFieldMultiply()

• a!customFieldSubtract()

• a!customFieldSum()

These recipes also highlight the ability to use related record fields, constants, and relative dates within the custom field expressions.

Return the difference between two dates

Goal: Create a new custom record field that returns the difference between two dates in days. If a date is null, subtract by today's date.

In this example, we want to create a field that displays the total number of days it took to deliver each order so we can show and sort by this data in a read-only grid.

To do this, we'll create a new custom record field using the a!customFieldDateDiff() function. Using this function, we'll subtract the submittedDate and the deliveryDate and return the difference in days. We'll also use the a!customFieldDefaultValue() function so that any orders that haven't been delivered (so the delivery date is null) are subtracted by today’s date.

1
2
3
4
5
6
7
8
a!customFieldDateDiff(
 startDate: recordType!Order.fields.submittedDate,
 endDate: a!customFieldDefaultValue(
    value: recordType!Order.fields.deliveryDate,
    default: today()
 ),
 interval: "DAY"
)

Concatenate values from related record types

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

In this example, we want to create a field that displays the sales region and shipping method together so we can create a chart that displays the count of orders by region and shipping method.

Since the sales region and shipping method are stored in related record types, we will create a new custom record field on the Order record type that evaluates in real time. Using the a!customFieldConcat() function, we can concatenate the related record fields from the Sales Region record type and the Shipping Method record type.

1
2
3
4
5
6
7
a!customFieldConcat(
  {
    recordType!Order.relationships.region.fields.label,
    "-",
    recordType!Order.relationships.shippingMethod.fields.label
  }
)

Replace null values with related record field values

Goal: Create a new custom record field that replaces null values in a record field with values from a related record field.

In this example, we have a createdOn field on the Customer record type. If a customer has a null value for createdOn, then we want to display the createdOn field from the related record type Account instead.

To do this, we could create a new custom record field on the Customer record type that evaluates in real time. Using the a!customFieldDefaultValue() function, we can replace any null values from the createdOn field from the Customer record type with the related record field createdOn from the Account record type.

1
2
3
4
a!customFieldDefaultValue(
    value: recordType!Customer.fields.createdOn,
    default: recordType!Customer.relationships.accounts.fields.createdOn
)

Use conditional logic with related record fields

Goal: Create a new custom record field where the values are dependent on conditional logic.

In this example, the Case record type has a many-to-one relationship with the Status record type, and we want to display all open or in progress cases as "Active", and any cases relying on customer action or support action to display as "Paused". Otherwise, if a case has any other status, display the case as "Closed".

To do this, we could create a new custom record field on the Case record type that evaluates in real time. Using the a!customFieldMatch() function, we can use simplified if-then logic to determine the values of our custom record field.

1
2
3
4
5
6
7
8
9
10
a!customFieldMatch(
  value: recordType!Case.relationships.status.fields.name,
  equals: "Open",
  equals: "In Progress",
  then: "Active",
  equals: "Customer Action",
  equals: "Support Action",
  then: "Paused",
  default: "Closed"
)

If you require more complex conditional logic, you can use the whenTrue parameter and the a!customFieldCondition() function or the a!customFieldLogicalExpression() function to meet your business needs. See the examples in a!customFieldMatch() for more information.

Use the if() function to return different values

Goal: Create a new custom record field where the values are dependent on the result of an if() function.

This example is similar to the one above, but in this example, we'll use the if() function to determine what value is returned in the custom record field.

To use the if() function to determine the value of a real-time custom record field, the result of the function must be a Custom Field function. Additionally, you can only pass a static value in the condition parameter.

In this example, we'll use a constant that references a list of environments (development, staging, and production) in the condition parameter and specify that when the environment is development (1), the field should concatenate the lastComment field with the case's createdOn date. If not, the field should concatenate the lastComment field with the case's lastUpdatedOn date.

1
2
3
4
5
6
7
8
9
if(
  cons!Environment = 1, 
  a!customFieldConcat( 
    {recordType!Case.relationships.comments.fields.latestComment, "-", recordType!Case.fields.createdOn}
    ),
  a!customFieldConcat( 
    {recordType!Case.relationships.comments.fields.latestComment, "-", recordType!Case.fields.lastUpdatedOn} 
    ) 
 )

Add, subtract, multiply, and divide record fields and related record fields

Goal: Create a new custom record field that will add, subtract, multiply, or divide record fields and related record fields and displays the resulting values.

In this example, we want to calculate each employee's potential bonus amount.

The Employee record type has an annualSalary field, which contains each employee's salary amount. The Level record type has a percentBonus field, which determines the potential bonus amount based on an employee's level in the organization.

To calculate each employee's potential bonus amount, we could create a new custom record field on the Employee record type that evaluates in real time so you can reference the related record field in your calculation.

1
2
3
4
5
6
a!customFieldMultiply(
  value: {
    recordType!Employee.fields.annualSalary,
    recordType!Employee.relationships.level.fields.percentBonus
  }
)

See all math-related custom field functions:

• a!customFieldSum()
• a!customFieldSubtract()
• a!customFieldMultiply()
• a!customFieldDivide()

Create a percentage using related record fields

Goal: Create a new custom record field that performs a calculation using record field and related record field values and displays the result as a percentage.

In this example, we want to calculate the percent of projected revenue to total revenue. The formula to calculate the percent of projected revenue to total revenue is Projected Revenue / (Current Revenue + Projected Revenue).

The Customer record type has a currentRevenue field, which displays the current revenue generated by each customer. The Account record type has a projectedRevenue field, which displays the forecasted revenue for each customer account according to the sales department.

To calculate these values, we could create a new custom record field on the Customer record type that evaluates in real time so that we can reference the related record field in our calculation.

1
2
3
4
5
6
7
8
9
a!customFieldDivide(
  numerator: recordType!Customer.relationships.account.fields.projectedRevenue,
  denominator:  a!customFieldSum(
    value: {
        recordType!Customer.fields.currentRevenue,
        recordType!Customer.relationships.account.fields.projectedRevenue
    }
  )
)

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!Customer.fields.percentProjectedRevToTotalRev] & "%".

Calculate values using other custom record fields

Goal: Create a new custom record field that performs a calculation using other custom record fields.

In this example, we want to calculate the percent change of sales generated this year versus last year for each country. The formula to calculate the percent change is (YTD Sales - LYTD Sales) / LYTD Sales.

On the Country record type, we already have two existing custom record fields that use the Aggregate Related Record Fields template: one calculates the year-to-date sales for all orders, and another that calculates the previous year-to-date sales for all orders. To calculate the percent change, we'll simply reference those existing custom record fields in our calculation.

1
2
3
4
5
6
7
a!customFieldDivide(
  numerator: a!customFieldSubtract(
      value1: recordType!Country.fields.ytdSales,
      value2: recordType!Country.fields.lytdSales
  ),
  denominator: recordType!Country.fields.lytdSales
)

Calculate values based on a constant

Goal: Create a new custom record field that returns its values based on the values of a constant.

In this example, we want to display the range of days in which an order is due based on the difference between today’s date and the delivery due date. Based on the difference, we want the custom record field to display "Overdue", "Due in the next 5 days", "Due in the next 6 to 10 days", or "Due after 10 days".

On the Order record type, we could create a custom record field that evaluates in real time and call it dueDateGroupingLabel.

We’ll use the a!customFieldMatch() function so we can return different values based on different conditions (the different ranges of days). For the value parameter, we’ll use the a!customFieldDateDiff() function to return the difference between today’s date and the delivery due date.

Then, we’ll create various whenTrue conditions using the a!customFieldCondition() function to determine which value to return based on the date difference. For example, when the difference is less than or equal to 0, then return the value “Overdue”. To make it easier to maintain these return values, we’ll use the constant cons!AR_CONS_DELIVERY_DUE_DATE_GRPOUPING to store the different labels.

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
a!customFieldMatch(
  value: a!customFieldDateDiff(
    startDate: today(),
    endDate: recordType!Order.fields.deliveryDueDate,
  ),
  whenTrue: a!customFieldCondition(
    field: fv!value,
    operator: "<=",
    value: 0
  ),
  /* Returns "Overdue" */
  then: cons!AR_CONS_DELIVERY_DUE_DATE_GROUPING[1],
  whenTrue: a!customFieldCondition(
    field: fv!value,
    operator: "<=",
    value: 5
  ),
  /* Returns "Due in the next 5 days" */
  then: cons!AR_CONS_DELIVERY_DUE_DATE_GROUPING[2],
  whenTrue: a!customFieldCondition(
    field: fv!value,
    operator: "<=",
    value: 10
  ),
  /* Returns "Due in the next 6 to 10 days" */
  then: cons!AR_CONS_DELIVERY_DUE_DATE_GROUPING[3],
  /* Returns "Due after 10 days" */
  default: cons!AR_CONS_DELIVERY_DUE_DATE_GROUPING[4],
)

Supported functions

When you write your own expression or edit certain templates, you can use different functions to configure your custom record field in expression mode.

The table below outlines which functions can be used to create a custom record field based on their evaluation time (sync-time or real-time).

Feedback