This section describes the functions supported by the Custom Field Editor when users build their own custom fields.
Aggregates
The Custom Field Editor supports the use of the following aggregates in custom fields:
- Avg()
- Count()
- Max()
- Min()
- Sum()
- Round()
Arithmetic Operators
The Custom Field Editor supports the use of the following arithmetic operators in custom fields:
- + (add)
- - (subtract)
- / (divide)
- % (modulo)
- * (multiply)
Comparison Operators
The Custom Field Editor supports the use of the following comparison operators in custom fields:
- = (equals)
- > (greater than)
- < (less than)
- >= (greater than or equal to)
- <= (less than or equal to)
- <> (not equal to)
- != (not equal to)
- !< (not less than)
- !> (not greater than)
Logical Operators
The Custom Field Editor supports the use of the following logical operators in custom fields:
- And
- Or
- Not
- Between
- In
- Like
- Any
- All
- Some
- Exists
Functions
The Custom Field Editor supports the use of the following functions in custom fields:
- Case: Used to provide ‘if, then, else’ logic.
- Cast: Used to convert one data type into another when needed.
- Convert: Similar to cast; used to convert one data type into another.
- Current_timestamp: Returns the current date.
- DateAdd: Used to add an interval to a date.
- DateDiff: Used to calculate the difference between two dates.
- DateName: Returns the name of the specified DatePart of the specified date.
- DatePart: Used to extract a specific part of a date or time value (Day, Month, Year).
- ExchangeRate: Gets the exchange rate from the source ISO currency code to the destination ISO currency code as of a specified date. For example, a manager needs to report on pay information in Japanese Yen (JPY) for employees who are paid in their local currencies in Canada, the United States, and the United Kingdom. The custom field would have the following expression:
EmployeeEmploymentStatus.BaseSalary*EXCHANGERATE(EmployeeEmploymentStatus.ISOCurrencyCodeName, 'JPY', CURRENT_TIMESTAMP)- In the report, the custom field displays the amounts converted into Japanese Yen. To get the Japanese Yen amount, Dayforce first converts to your organization's base currency, which is configured in System Admin > Exchange Rates. Then it converts the base currency amount into the target currency, in this case Japanese Yen.
- GetDate(): Returns the current system time.
- IsDate: Returns 1 if the expression is a valid date, time, or date time value; otherwise, returns 0.
- Left: Used in joins.
- Len: Used to get the length of a string.
- Lower: Converts uppercase character data to lowercase.
- Ltrim: Used to remove white spaces from the left side of a string.
- Replace: Used to update the content of a string.
- Replicate: Repeats a string value a specified number of times.
- Reverse: Returns the reverse of a string value.
- Right: Used in joins.
- Rtrim: Used to remove white spaces from the right side of a string.
- Space: Returns a string of repeated spaces.
- Str: Returns character data converted from numeric data.
- Substring: Returns the specified number of characters from the specified string, in the format:
SUBSTRING ( expression, start, length )- Sysdatetime: Returns the current server time.
- Upper: Converts lowercase character data to uppercase.