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.

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:

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.

Record types with data sync enabled can only have 100 fields, and no more than 20 of them can be custom record fields. For example, if your record type has 98 fields, you can only create two custom record fields.

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

    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.

Functions that return dynamic values, like today() or now(), are not currently supported in this template.

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.

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.

at-least-one-crf-ex

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.

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.

You cannot modify the underlying expression of the date preset, nor can you create your own expression in this template. The tables below are for reference only.

Date presets for Date fields
Filter Operator Available Date Preset Underlying Expression Example (If today is September 24, 2021)
=, <>, >, >=, <, <= Today today() 9/24/2021
=, <>, >, >=, <, <= Yesterday today()-1 9/23/2021
=, <>, >, >=, <, <= Day Before Yesterday today() -2 9/22/2021
=, <>, >, >=, <, <= Today Last Week today() - 7 9/17/2021
=, <>, >, >=, <, <= Today Last Month date(year(eomonth(today(), -1)), month(eomonth(today(), -1)), day(today())) 8/24/2021
=, <>, >, >=, <, <= Today Last Year edate(today(), -12) 9/24/2020
Date Range Week-to-Date (From Monday of the Current Week) {today() - weekday(today(), 2) + 1, today()} {9/20/2021, 9/24/2021}
Date Range Week-to-Date (From Sunday of the Current Week) {today() - weekday(today(), 1) + 1, today()} {9/19/2021, 9/24/2021}
Date Range Previous Week-to-Date (From Monday to Today of the Previous Week) {todate(today() - 7 - weekday(today(), 2) + 1), today() - 7 } {9/13/2021, 9/17/2021}
Date Range Previous Week-to-Date (From Sunday to Today of the Previous Week) {todate(today() - 7 - weekday(today(), 1) + 1), today() - 7 } {9/12/2021, 9/17/2021}
Date Range Month-to-Date {eomonth(today(),-1) + 1, today()} {9/1/2021, 9/24/2021}
Date Range Previous Month-to-Date (From Beginning of Last Month to Today Last Month) {eomonth(date(year(eomonth(today(), - 1)), month(eomonth(today(), - 1)), day(today())), - 1) + 1, date(year(eomonth(today(), - 1)), month(eomonth(today(), - 1)), day(today()))} {8/1/2021, 8/24/2021}
Date Range Month-to-Date Last Year (From Beginning of This Month Last Year to Today Last Year) {eomonth(today(),-13) + 1, edate(today(), -12)} {9/1/2020, 9/24/2020}
Date Range Year-to-Date {date(year(today()), 1, 1), today()} {1/1/2021, 9/24/2021}
Date Range Previous Year-to-Date (From Beginning of Last Year to Today Last Year) {date(year(today())-1, 1, 1), edate(today(), -12)} {1/1/2020, 9/24/2020}
Date Range Beginning of Last Month to Today {eomonth(today(),-2) + 1, today()} {8/1/2021, 9/24/2021}
Date Range Trailing 3 Months {eomonth(today(),-4) + 1, eomonth(today(),-1)} {6/1/2021, 8/31/2021}
Date Range Trailing 6 Months {eomonth(today(),-7) + 1, eomonth(today(),-1)} {3/1/2021, 8/31/2021}
Date Range Trailing 9 Months {eomonth(today(),-10) + 1, eomonth(today(),-1)} {12/1/2020, 8/31/2021}
Date Range Trailing 12 Months {eomonth(today(),-13) + 1, eomonth(today(),-1)} {9/1/2020, 8/31/2021}
Date Range Trailing 18 Months {eomonth(today(),-19) + 1, eomonth(today(),-1)} {3/1/2020, 8/31/2021}
Date presets for Date and Time fields
Filter Operator Available Date Preset Underlying Expression Example (If today is September 24, 2021 at 2:30 PM GMT)
=, <>, >, >=, <, <= Today: Current Time now() 9/24/2021 2:30 PM GMT+00:00
=, <>, >, >=, <, <= Today: Beginning of the Day todatetime(today()) 9/24/2021 12:00 AM GMT+00:00
=, <>, >, >=, <, <= Yesterday: Current Time That Day now() - 1 9/23/2021 2:30 PM GMT+00:00
=, <>, >, >=, <, <= Yesterday: Beginning of the Day todatetime(today()-1) 9/23/2021 12:00 AM GMT+00:00
=, <>, >, >=, <, <= Day Before Yesterday: Current Time That Day now() -2 9/22/2021 2:30 PM GMT+00:00
=, <>, >, >=, <, <= Day Before Yesterday: Beginning of the Day todatetime(today()-2) 9/22/2021 12:00 AM GMT+00:00
Date Range Today: From Midnight to Current Time {todatetime(today()), now()} {9/24/2021 12:00 AM GMT+00:00, 9/24/2021 2:30 PM GMT+00:00}
Date Range Yesterday: From Midnight to Current Time That Day {todatetime(today()-1), now()-1} {9/23/2021 12:00 AM GMT+00:00, 9/23/2021 2:30 PM GMT+00:00}
Date Range Day Before Yesterday: From Midnight to Current Time That Day {todatetime(today()-2), now()-2} {9/22/2021 12:00 AM GMT+00:00, 9/22/2021 2:30 PM GMT+00:00}
Date Range Week-to-Date (From Monday of the Current Week) {todatetime(today() - weekday(today(), 2) + 1), now()} {9/20/2021 12:00 AM GMT+00:00, 9/24/2021 2:30 PM GMT+00:00}
Date Range Week-to-Date (From Sunday of the Current Week) {todatetime(today() - weekday(today(), 1) + 1), now()} {9/19/2021 12:00 AM GMT+00:00, 9/24/2021 2:30 PM GMT+00:00}
Date Range Previous Week-to-Date (From Monday to Today of the Previous Week) {todatetime(today() - 7 - weekday(today(), 2) + 1), now()-7} {9/13/2021 12:00 AM GMT+00:00, 9/17/2021 2:30 PM GMT+00:00}
Date Range Previous Week-to-Date (From Sunday to Today of the Previous Week) {todatetime(today() - 7 - weekday(today(), 1) + 1), now()-7} {9/12/2021 12:00 AM GMT+00:00, 9/17/2021 2:30 PM GMT+00:00}
Date Range Month-to-Date {todatetime(eomonth(today(),-1) + 1), now()} {9/1/2021 12:00 AM GMT+00:00, 9/24/2021 2:30 PM GMT+00:00}
Date Range Previous Month-to-Date (From Beginning of Last Month to Today Last Month) {todatetime(eomonth(date(year(eomonth(today(), - 1)), month(eomonth(today(), - 1)), day(today())), - 1) + 1), datetime(year(eomonth(today(), - 1)), month(eomonth(today(), - 1)), day(today()), hour(now()), minute(now()), second(now()), milli(now()))} {8/1/2021 12:00 AM GMT+00:00, 8/24/2021 2:30 PM GMT+00:00}
Date Range Year-to-Date {datetime(year(today()), 1, 1), now()} {1/1/2021 12:00 AM GMT+00:00, 9/24/2021 2:30 PM GMT+00:00}
Date Range Month-to-Date Last Year (From Beginning of This Month Last Year to Today Last Year) {todatetime(eomonth(today(),-13) + 1), if(isleapyear(year(now())), now() - 366, now() - 365)} {9/1/2020 12:00 AM GMT+00:00, 9/24/2020 2:30 PM GMT+00:00}
Date Range Previous Year-to-Date (From Beginning of Last Year to Today Last Year) {datetime(year(today()) - 1, 1, 1), edate(today(), - 12) + (now() - today())} {1/1/2020 12:00 AM GMT+00:00, 9/24/2020 2:30 PM GMT+00:00}
Date Range Beginning of Last Month to Today {todatetime(eomonth(today(),-2) + 1), now()} {8/1/2021 12:00 AM GMT+00:00, 9/24/2021 2:30 PM GMT+00:00}
Date Range Trailing 3 Months todatetime({eomonth(today(),-4) + 1, eomonth(today(),-1) + 1}) {6/1/2021 12:00 AM GMT+00:00, 9/1/2021 12:00 AM GMT+00:00}
Date Range Trailing 6 Months todatetime({eomonth(today(),-7) + 1, eomonth(today(),-1) + 1}) {3/1/2021 12:00 AM GMT+00:00, 9/1/2021 12:00 AM GMT+00:00}
Date Range Trailing 9 Months todatetime({eomonth(today(),-10) + 1, eomonth(today(),-1) + 1}) {12/1/2020 12:00 AM GMT+00:00, 9/1/2021 12:00 AM GMT+00:00}
Date Range Trailing 12 Months todatetime({eomonth(today(),-13) + 1, eomonth(today(),-1) + 1}) {9/1/2020 12:00 AM GMT+00:00, 9/1/2021 12:00 AM GMT+00:00}
Date Range Trailing 18 Months todatetime({eomonth(today(),-19) + 1, eomonth(today(),-1) + 1}) {3/1/2020 12:00 AM GMT+00:00, 9/1/2021 12:00 AM GMT+00:00}

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.

For more examples using the Date Difference custom record field template, check out the AR_DateDiffDashboard in the Appian Retail application available for free in Appian Community Edition.

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

    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]

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.

The following functions are only supported when writing your own custom record field expression, or when you edit a guided template.

For a list of functions supported in the Date Difference template, see the a!customFieldDateDiff() page.

Category Function Description
Array a!flatten()

Converts an array that contains other arrays into an array of single items.

Array a!update()

Inserts new values or replaces existing values at the specified index or field name and returns the resulting updated data.

Array append()

Appends a value or values to the given array, and returns the resulting array.

Array index()

Returns the data[index] if it is valid or else returns the default value.

Array insert()

Inserts a value into the given array and returns the resulting array.

Array joinarray()

Concatenates the elements of an array together into one string and inserts a string separator between each element.

Array ldrop()

Drops a given number of values from the left side of an array and returns the resulting array.

Array length()

This function returns the number of elements in an array.

Array rdrop()

Drops a given number of values from the right side of an array, and returns the resulting array.

Array remove()

Removes the value at a given index from an array, and returns the resulting array.

Array reverse()

Returns an array in reverse order.

Array updatearray()

Inserts new values or modifies existing values at the specified index of a given array, and returns the resulting array.

Array where()

Returns the indices where the values in the input array are true.

Array wherecontains()

Receives one or more values and returns an array of indices that indicate the position of the values within the array.

Base Conversion bin2dec()

Converts a Binary number as text to a Decimal number.

Base Conversion bin2hex()

Converts a Binary number as text to a Hex number as text.

Base Conversion bin2oct()

Converts a Binary number as text to an Octal number as text.

Base Conversion dec2bin()

Converts a Decimal number to a Binary number as text.

Base Conversion dec2hex()

Converts a Decimal number to a Binary number as text.

Base Conversion dec2oct()

Converts a Decimal number to an Octal number as text.

Base Conversion hex2bin()

Converts a Hex number as text to a Binary number as text.

Base Conversion hex2dec()

Converts a Hex number as text to a Decimal number.

Base Conversion hex2oct()

Converts a Hex number as text to an Octal number as text.

Base Conversion oct2bin()

Converts an Octal number as text to a Binary number as text.

Base Conversion oct2dec()

Converts an Octal number as text to a Decimal number.

Base Conversion oct2hex()

Converts an Octal number as text to a Hex number as text.

Conversion displayvalue()

Tries to match a value in a given array with a value at the same index in a replacement array and returns either the value at the same index or a default value if the value is not found.

Conversion externalize()

Converts the given value to a string representation so that it can be saved externally.

Conversion toboolean()

Converts a value to Boolean.

Conversion todate()

Converts a value to Date with Timezone.

Conversion todatetime()

Converts a value to Date and Time.

Conversion todecimal()

Converts a value to Decimal (double precision floating point).

Conversion toemailaddress()

Converts a value to email address.

Conversion tointeger()

Converts a value to Integer.

Conversion tointervalds()

Converts a value to Interval (Day to Second).

Conversion tostring()

Converts a value to Text.

Conversion totime()

Converts a value to Time.

Conversion touniformstring()

Converts a value to Text, preserving the original scalar or array structure.

Custom Record Field a!customFieldDateDiff()

Used to create a custom record field, this function returns the difference between two dates as a Number (Integer). The difference can be returned in days, hours, minutes, or seconds. Returns null when the startDate or endDate is null or empty.

Date and Time caladddays()

This function adds a given number of working days as designated on the process calendar to a Date and Time value, and returns a Date and Time value that falls within the work time defined in the process calendar.

Date and Time caladdhours()

This function adds a given number of hours to a Date and Time plus any non-working hours (as designated on the process calendar) and returns the resulting Date and Time.

Date and Time calisworkday()

This returns whether or not the given Date and Time is a work day, according to the calendar defined for the system.

Date and Time calisworktime()

This returns whether or not the given Date and Time is within working hours, according to the calendars defined for the system.

Date and Time calworkdays()

This returns the actual number of work days between two Date and Times (both inclusive), according to the calendar defined for the system.

Date and Time calworkhours()

This returns the actual number of work hours between two given Date and Times (both inclusive), according to the calendar defined for the system.

Date and Time date()

Converts text into data accepted by the date data type and functions that require date parameters.

Date and Time datetime()

Converts the given Date and Time into a serial number that holds the Date and Time data type.

Date and Time datevalue()

Converts a value to a date.

Date and Time day()

Returns the day of the month from the day specified.

Date and Time dayofyear()

Returns the day number within the year.

Date and Time days360()

Returns the number of days between two dates, based on a 360-day calendar.

Date and Time daysinmonth()

Returns the number of days in the given month in the given year.

Date and Time edate()

Returns the date that is the number of months before or after the given starting date.

Date and Time eomonth()

Returns the date for the last day of the month that is the number of months before or after the given starting date.

Date and Time gmt()

Subtracts a time zone offset from a given Date and Time.

Date and Time hour()

Returns the hour from the time specified.

Date and Time intervalds()

Converts the given time components into an equivalent time duration, an interval expressing days to seconds. This value is treated as a duration (Joe ran the marathon in 3 hours and 23 minutes), not a point in time.

Date and Time isleapyear()

Returns a Boolean value for whether the given year is a leap year.

Date and Time lastndays()

Returns a Boolean value for whether the given date is within the last given number of days.

Date and Time local()

This is a Date and Time addition function, adding time zone offset to given Date and Time.

Date and Time milli()

This function returns the millisecond portion of a timestamp or the decimal number that represents 1 millisecond in days.

Date and Time minute()

Returns the minute from the time specified.

Date and Time month()

Returns the month from the specified date.

Date and Time networkdays()

Returns the number of working days between two specified dates.

Date and Time second()

Returns the seconds from the specified time.

Date and Time time()

Converts the given time into an equivalent time value.

Date and Time timevalue()

Converts the given time into an equivalent interval.

Date and Time timezone()

Returns the default offset in minutes from GMT, which is generally the process initiator's time zone.

Date and Time timezoneid()

Returns the time zone ID for the current context.

Date and Time weekday()

Returns the day of the week of the specified date.

Date and Time weeknum()

Returns the week number within the year for the given date using a given methodology.

Date and Time workday()

Returns the date the given number of workdays before or after the given date.

Date and Time year()

Returns the year for the date specified.

Date and Time yearfrac()

Determine the fraction of the year.

Evaluation a!localVariables()

Lets you define one or more local variables for use within an expression. When used within an interface, the value of each variable can be refreshed under a variety of conditions, configured using a!refreshVariable(). When used outside of an interface, all refresh properties configured using a!refreshVariable() are ignored.

Evaluation load()

Lets you define local variables within an expression for an interface and evaluate the expression with the new variables, then re-evaluate the function with the local variables' values from the previous evaluation.

Evaluation with()

Lets you define local variables within a function and evaluate the expression with the new variables.

Informational a!defaultValue()

Returns a default value when the specified value is null or empty. When there are multiple default parameters, each parameter is evaluated in order and the first non-null and non-empty default will be returned. Null, "", and {} are all considered null or empty values.

Informational cast()

Converts a value from its existing type to the specified type.

Informational error()

Raises an error with the given message, used for invalidating execution.This function never returns a value.

Informational infinity()

Represents a constant number that stands for positive infinity or a negative infinity if you negate the value.

Informational isinfinite()

Tests given numbers against positive and negative infinity, returning true if the number is infinite, false if the number is not infinite.

Informational isnegativeinfinity()

Tests given numbers against negative infinity, returning true if number is negative infinity, false if number is not negative infinity.

Informational a!isNotNullOrEmpty()

Returns false if the value is null, an empty string, or an empty list. Otherwise returns true.

Informational isnull()

Returns true if value is null, false otherwise.

Informational a!isNullOrEmpty()

Returns true if the value is null, an empty string, or an empty list. Otherwise returns false.

Informational ispositiveinfinity()

Tests given numbers against positive infinity, returning true if the numbers are positive infinity, false if the numbers are not positive infinity.

Informational nan()

Constant number representing Not A Number, generally used for comparison to the result of mathematical operations with invalid inputs. This is equivalent to a decimal (floating point) null, but nan() is provided for more explicit usage in mathematical expressions.

Informational null()

Returns a null value.

Informational runtimetypeof()

Returns the numerical representation of an Appian system data type when used during process execution.

Informational typename()

Returns the type name of a given type number.

Informational typeof()

Returns the type number of a given value.

Logical and()

Returns true if all inputs are true; returns false if at least one input is false.

Logical choose()

Evaluates the choice argument at the given index and returns the result.

Logical false()

Returns the Boolean value false.

Logical if()

Returns valueIfTrue if condition returns true; returns valueIfFalse otherwise.

Logical a!match()

Evaluates the value against multiple conditions and returns a value based on a match. If no match is found, the default is returned. For example, if "a" then "b" else "c".

Logical not()

Converts true into false, and false into true.

Logical or()

Returns true if any inputs are true; returns false if all inputs are false.

Logical true()

Returns the Boolean value true.

Looping a!forEach()

Evaluates an expression for each item in a list and returns a new array of the results.

Looping all()

Calls a rule or function that returns either true or false for each item in list, asks the question, "Do all items in this list yield true for this rule/function?", and returns true if all items in list evaluates to true.

Looping any()

Calls a rule or function that returns either true or false for each item in list by asking the question, "Do any items in this list yield true for this rule/function?" with the intent to discover if any item(s) yield true.

Looping apply()

Calls a rule or function for each item in a list, and provides any contexts specified.

Looping filter()

Calls a predicate for each item in a list and returns any items for which the returned value is true.

Looping merge()

Takes a variable number of lists and merges them into a single list (or a list of lists) that is the size of the largest list provided.

Looping none()

Calls a rule or function that returns either true or false for each item in list by asking the question, "Do all items in this list yield false for this rule/function?" with the intent to discover if no items will yield true.

Looping reduce()

Calls a rule or function for each item in a list, passing the result of each call to the next one, and returns the value of the last computation.

Looping reject()

Calls a predicate for each item in a list, rejects any items for which the returned value is true, and returns all remaining items.

Mathematical abs()

Returns the absolute value(s) of the specified number(s).

Mathematical ceiling()

Rounds the number up to the nearest multiple of the specified significance.

Mathematical combin()

Calculates the number of unique ways to choose m elements from a pool of n elements.

Mathematical a!distanceBetween()

Returns the distance between the two locations (in meters) specified by the start and end coordinates. The distance is calculated by tracing a line between the two locations that follows the curvature of the Earth, and measuring the length of the resulting arc.

Mathematical e()

Returns the value of e.

Mathematical enumerate()

Returns a list of integer numbers from 0 through n-1.

Mathematical even()

Rounds positive numbers up to nearest even integer and negative numbers down to the nearest even integer.

Mathematical exp()

Returns e raised to the specified power.

Mathematical fact()

The factorial of specified number.

Mathematical factdouble()

The double factorial of specified number (mathematically n!!).

Mathematical floor()

Rounds the number down to the nearest multiple of the specified significance.

Mathematical int()

Rounds the specified number down to the nearest integer.

Mathematical ln()

Returns the natural logarithm of the specified number, which is the power that e must be raised to in order to equal the specified number.

Mathematical log()

Returns the logarithm of the number using the specified base, which is the power that base must be raised to, to equal the number.

Mathematical mod()

Returns the remainder of dividend when divided by the divisor.

Mathematical mround()

Rounds the number to the specified multiple.

Mathematical multinomial()

Adds the specified integers and divides the factorial of the sum by the factorial of the individual numbers.

Mathematical odd()

Rounds positive numbers up to nearest odd integer and negative numbers down to the nearest odd integer.

Mathematical pi()

Returns the value of pi.

Mathematical power()

Returns the base number raised to the specified exponent.

Mathematical product()

Returns the product of the specified numbers.

Mathematical quotient()

Returns the quotient when numerator is divided by the denominator, and drops the remainder.

Mathematical round()

Rounds off the number to the specified number of digits.

Mathematical rounddown()

Rounds the number down to the specified digit.

Mathematical roundup()

Rounds the number up to the specified digit.

Mathematical sign()

Returns the number divided by its absolute value, which is 1 if the number is positive and -1 if the number is negative.

Mathematical sqrt()

Returns the square root(s) of the specified number(s).

Mathematical sqrtpi()

Multiplies the number by pi, then returns the square root of the product.

Mathematical sum()

Returns the sum of the specified numbers.

Mathematical sumsq()

Squares each number and then returns the sum of the squares.

Mathematical trunc()

Truncates a decimal number to the specified number of places after the decimal point.

Scripting offsetFromGMT()

Returns the offset (in minutes) from GMT of the given date and timezone.

Scripting property()

This function extracts a bean's property under a given key name (the nameOfProperty parameter).

Array contains()

Checks whether an array contains the value.

Array difference()

Returns the values in array1 and not in array2.

Array intersection()

Returns only those elements that appear in all of the given arrays.

Array symmetricdifference()

Returns the values unique to two arrays but are not in the intersection of them.

Array union()

Returns all unique elements from the given arrays.

Statistical avedev()

Returns the average deviation of the specified number(s).

Statistical average()

Returns the average of the specified number(s).

Statistical count()

Returns the number items in all arrays passed to the function.

Statistical frequency()

Uses the bin array to create groups bounded by the elements of the array.

Statistical gcd()

Returns the greatest common denominator of the specified non-negative number(s), which is the largest number that divides all the given numbers without a remainder.

Statistical geomean()

Returns the geometric mean of the specified number(s).

Statistical harmean()

Returns the harmonic mean of the specified number(s), which is the number of terms divided by the sum of the terms' reciprocals.

Statistical lcm()

Returns the least common multiple of the specified non-negative number(s), which is the smallest number that is a multiple of all the given numbers.

Statistical lookup()

Returns location of data within multiple values, or valueIfNotPresent.

Statistical max()

Returns the maximum of the specified number(s).

Statistical median()

Returns the median of the specified number(s).

Statistical min()

Returns the minimum of the specified number(s).

Statistical mode()

Returns the mode of the specified number(s), which is the most commonly repeated element.

Statistical rank()

Returns an integer representing the rank of the number in the specified array.

Statistical stdev()

Returns the standard deviation of the specified number(s).

Statistical stdevp()

Returns the standard deviation of the specified number(s), assuming that the numbers form the entire data set and not just a sample.

Statistical var()

Returns the variance of the specified number(s).

Statistical varp()

Returns the variance of the specified number(s), assuming that the numbers form the entire data set and not just a sample.

System a!storedProcedureInput()

Creates an input to be passed to the a!executeStoredProcedureOnSave or a!executeStoredProcedureForQuery functions.

System a!map()

Creates a map of values (Any Type) with each value stored at the corresponding string key. Values stored in maps are not wrapped in variants.

Text char()

Converts a number into its Unicode character equivalent.

Text charat()

Returns the character at given index within specified string.

Text clean()

Returns the specified text, minus any characters not considered printable. Printable characters are the 95 printable ASCII characters plus three special characters: BACKSPACE (0x08), TAB (0x09), and NEWLINE (0x0a).

Text cleanwith()

Returns the specified text, minus any characters not in the list of valid characters.

Text code()

Converts the text into Unicode integers.

Text concat()

Concatenates the specified strings into one string, without a separator.

Text exact()

Compares two given text strings in a case-sensitive manner, returning true only if they are exactly the same.

Text extract()

Returns the value (or values, if the text contains multiple delimited values) between the delimiters from the given text.

Text extractanswers()

Returns an array of strings that respond to the questions provided.

Text find()

Returns index location of case-sensitive substring with given string.

Text fixed()

Rounds the specified number off to a certain number of decimals and returns it as text, with optional commas.

Text initials()

Returns only the uppercase characters from within the given text.

Text insertkey()

Returns the provided text, wrapped with the specified delimiters.

Text insertkeyval()

Returns the provided key-value pairs, wrapped with the specified delimiters.

Text insertquestions()

Returns an array of questions with a ==EOQ== at the end, returning a single string that can be parsed with extractanswers() function after it has been filled in by a user.

Text keyval()

Returns the value(s) associated with the given key(s).

Text left()

Returns a specified number of characters from the text, starting from the first character.

Text leftb()

Returns a specified number of bytes from the text, starting from the first byte.

Text len()

Returns the length in characters of the text.

Text lenb()

Returns the length in bytes of the text.

Text like()

Tests whether a string of text is like a given pattern.

Text lower()

Converts all characters in the text into lowercase (Unicode case folding).

Text mid()

Returns a substring from the middle of the specified text.

Text midb()

Returns a substring from the middle of the specified text.

Text padleft()

Pads text with spaces on the left so that it is a certain length.

Text padright()

Pads text with spaces on the right so that it is a certain length.

Text proper()

Converts each character in the text into proper case, meaning it will capitalize the first first letter of every word and convert the rest into lowercase.

Text replace()

Replaces a piece of the specified text with new text.

Text replaceb()

Replaces a piece of the specified text with new text.

Text rept()

Concatenates the text to itself a specified number of times and returns the result.

Text resource()

Retrieves a string of translated text appropriate for the current user, according to their language preference, by matching a given key with text.

Text right()

Returns a specified number of characters from the text, starting from the last character.

Text search()

Searches the text for a particular substring, returning the positional index of the first character of the first match.

Text searchb()

Searches the text for a particular substring, returning the positional index of the first byte of the first match.

Text soundex()

Returns the soundex code, used to render similar sounding names via phonetic similarities into identical four (4) character codes.

Text split()

Splits text into a list of text elements, delimited by the text specified in the separator.

Text strip()

Returns the provided text, minus any characters considered printable. Printable characters are the 95 printable ASCII characters plus three special characters: BACKSPACE (0x08), TAB (0x09), and NEWLINE (0x0a).

Text stripHtml()

Changes the provided HTML string into a plain text string by converting <br>, <p>, and <div> to line breaks, stripping all other tags, and converting escaped characters into their display values.

Text stripwith()

The function returns the provided text, minus any characters on the list of invalid characters.

Text substitute()

Substitutes a specific part of a string with another string.

Text toHtml()

Converts a string in plain text to the HTML equivalent that displays appropriately in an HTML page, by replacing reserved characters with their escaped counterparts.

Text trim()

Removes all unnecessary spaces from the text.

Text upper()

Converts all letters in the text into uppercase.

Text value()

Converts text representing a number into an actual number or datetime.

Trigonometry acos()

Returns the arccosine(s) of the specified number(s) in radians.

Trigonometry acosh()

Returns the hyperbolic arccosine(s) of the specified number(s) in radians.

Trigonometry asin()

Returns the arcsine(s) of the specified number(s) in radians.

Trigonometry asinh()

Returns the hyperbolic arcsine(s) of the specified number(s) in radians.

Trigonometry atan()

Returns the arctangent(s) of the specified number(s) in radians.

Trigonometry atanh()

Returns the hyperbolic arctangent(s) of the specified number(s) in radians.

Trigonometry cos()

Returns the cosine(s) of the specified number(s).

Trigonometry cosh()

Returns the hyperbolic cosine(s) of the specified number(s).

Trigonometry degrees()

Converts the measure(s) of the specified angle(s) from radians to degrees.

Trigonometry radians()

Converts the measure of the specified angle from degrees to radians.

Trigonometry sin()

Returns the sine(s) of the specified number(s).

Trigonometry sinh()

Returns the hyperbolic sine(s) of the specified number(s).

Trigonometry tan()

Returns the tangent(s) of the specified number(s).

Trigonometry tanh()

Returns the hyperbolic tangent(s) of the specified number(s).

Open in Github Built: Fri, Jun 24, 2022 (04:33:20 PM)

On This Page

FEEDBACK