Free cookie consent management tool by TermsFeed a!customFieldDateDiff() Function
a!customFieldDateDiff() Function

Function

a!customFieldDateDiff( startDate, endDate, interval )

Used to create a real-time 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.

Parameters

Keyword Type Description

startDate

Any Type

The record field or expression that evaluates to a Date or Date and Time value to use in the date subtraction.

endDate

Any Type

The record field or expression that evaluates to a Date or Date and Time value to use in the date subtraction.

interval

Text

Determines whether the difference is calculated in days, hours, minutes, or seconds. Valid values are "DAY" (default), "HOUR", "MINUTE", "SECOND".

Usage considerations

Where to use this function

The a!customFieldDateDiff() function can only be used to create a custom record field that evaluates in real time. This means you can reference related record fields, constants, and other supported functions in your calculations.

To create a custom record field that evaluates in real-time:

  1. In your record type, go to Data Model.
  2. Click NEW CUSTOM RECORD FIELD.
  3. From SELECT A TEMPLATE, choose Write Your Own Expression.
  4. Choose Real-time evaluation.
  5. Click NEXT.

  6. Enter an expression using any Custom Field function.

    Note:  Custom record fields that evaluate in real-time must use at least one Custom Field function.

  7. Click NEXT.
  8. Enter a Name for your custom record field.
  9. Click CREATE.
  10. Click SAVE CHANGES. The new custom record field appears in your list of fields.

Using the startDate and endDate parameters

By default, the startDate parameter is subtracted from the endDate parameter. This means that if the startDate is 12/1/2022 and the endDate is 12/10/2022, the resulting value would be 9 days.

You can use the following values in the startDate and endDate parameters:

  • Record field or related record fields of type Date or Date and Time. Use the recordType! domain to reference your fields. For example, recordType!Case.fields.submittedDate.

  • Relative date and time functions, like today() or now().

    Note:  You cannot use a relative date and time function in both parameters.

  • Constants.

If either parameter is null or empty, a null value is returned.

Example

Let’s say you want to display the number of days it took to deliver each order on a read-only grid. You could use this function to create a custom record field that subtracts the submittedDate and the deliveryDate and returns the difference in days. If the order hasn’t been delivered yet (so the delivery date is null), then subtract the submittedDate from today’s date.

The expression would look something like this:

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

Tip:  For more examples using the a!customFieldDateDiff() function, check out the AR_DateDiffDashboard in the Appian Retail application available for free in Appian Community Edition.

Supported functions

You can use any of the following supported functions in the interval parameter of a!customFieldDateDiff(). This allows you to use functions like if() or not() to determine which interval value to return.

Note:  When you use a supported function in a Custom Field function, you can only pass static values or constants containing static values into the supported function; you cannot pass record field references.

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 indexes where the values in the input array are true.
Array wherecontains()

Receives one or more values and returns an array of indexes 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 Hex 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 with Timezone.
Conversion todecimal()

Converts a value to Decimal (double-precision floating-point number).
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 or list to text, preserving the original scalar or array structure.
Date and Time a!addDateTime()

Adds the specified increments of time to the startDateTime and returns a date and time value. You can select a process calendar to ensure the return value falls within the specified working days 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 date 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 now()

Returns the current Date and Time as a serial number.
Date and Time second()

Returns the seconds from the specified time.
Date and Time a!subtractDateTime()

Subtracts the specified increments of time from the startDateTime and returns a date and time value. You can select a process calendar to ensure the return value falls within the specified working days and 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 today()

Returns the current day in GMT.
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.
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 a!keys()

Returns the keys of the provided map, dictionary, CDT, or record.
Informational a!listType()

Returns the list type number for a given type number.
Conversion 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 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 rand()

Returns a random number between 0 and 1 based on an even probability distribution, which is seeded by the transaction time.
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 from two integer arrays that are not in both arrays.
Array union()

Returns all unique elements from the given arrays.
System a!jsonPath()

Finds information in a JSON string. JSONPath is used to navigate through elements and attributes in a JSON string.
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()

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

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

Returns a formatted phone number based on the outputFormat parameter. The countryCode parameter, or any country code provided in the phone number, will verify that the phone number is valid. If the phone number does not match any provided country code, the phone number will be returned as invalid and unformatted.
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 a!isPhoneNumber()

Returns true if the phoneNumber parameter contains a valid phone number, otherwise returns false. A phone number is considered valid if its area code is valid, the length is appropriate based upon the value of the countryCode parameter, and the number after the area code is not all zeroes. This function supports countries and area codes for international numbers.
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 the given, case insensitive substring. Returns the one-based positional index of the first character of the first match. Returns zero if the substring is not found.
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).

Feature compatibility

The table below lists this function's compatibility with various features in Appian.
Feature Compatibility Note
Portals Partially compatible

Can be used with Appian Portals if it is connected using an integration and web API.
Offline Mobile Incompatible
Sync-Time Custom Record Fields Incompatible
Real-Time Custom Record Fields Compatible

Can only be used to create a custom record field that evaluates in real time. It cannot be used anywhere else in your application.
Process Reports Incompatible

Cannot be used to configure a process report.
Process Events Incompatible

Cannot be used to configure a process event node, such as a start event or timer event.

a!customFieldDateDiff() Function

FEEDBACK