Cloud Documentation
Advanced Search


Using Fast Formula
Close Window

 

This guide also applies to on-premise implementations

Table of Contents

Show All | Collapse

3 Formula Components

This chapter contains the following:

Using Formula Components: Explained

Formula Statements: Explained

Classes of Variables: Explained

Naming Variables: Explained

Database Items: Explained

Formula Operators: Explained

Literals: Explained

Generating Flexfield Database Items: Explained

Formula Variable Data Types: How They Are Determined

Generating Flexfield Database Items: Explained

Array Variables: Explained

Formula Contexts: Explained

Working Storage Area: Explained

Calling a Formula from a Formula: Explained

Calling a Formula from a Formula: Examples

Using Formula Components: Explained

When developing a formula you must understand formula language, the rules that the application imposes on the formula, and the calculation requirements. Formulas are created using various components.

Formula components include:

  • Assignment statements

  • Return statements

  • Variables

  • Input statements

  • Expressions

  • Conditions

  • Comments

Note

There are several other components used in formulas. These include literals, database items, working storage area, calls to other formulas, functions, and operators.

To illustrate how each component is used in a formula, suppose you wanted to calculate the pay value for the element WAGE by multiplying the number of hours an employee works each week by the hourly rate. The formula can be written as follows:

WAGE = HOURS_WORKED * HOURLY_RATE
RETURN WAGE

Assignment Statements

The first line is an assignment statement that simply assigns a value to the element WAGE.

Return Statements

The second line is a return statement that passes back the WAGE value to the payroll run. A return statement can be used to stop the formula execution without passing back any values.

Variables

Local variables occur in a single formula only. You can change a local variable within the formula by assigning a value to it using an assignment statement. To calculate the WAGE value, the formula needs to get the value for the variable HOURS_WORKED.

You can use local variables to store data in a formula. You might want to hold data temporarily while you perform some other calculations, or to pass data back to the application. Below is an example showing the use of a variable, ANNUAL_LEAVE.

/* Formula: Annual Leave Formula */
IF YEARS_SERVICE >= 10
THEN
ANNUAL_LEAVE = 25
ELSE
ANNUAL_LEAVE = 20 + FLOOR (YEARS_SERVICE/2)
RETURN ANNUAL_LEAVE

Input Statements

The HOURS_WORKED could be an input value of the element WAGE. To pass the element input values to the formula during processing, define an inputs statement as follows:

INPUTS ARE HOURS_WORKED
WAGE = HOURS_WORKED * HOURLY_RATE
RETURN WAGE

Note

This is a payroll application example. The name used in the input statement must be the same as the name of the element input value, and multiple words must be joined by underscores. Other input statements that have nothing to do with elements would have their own rules for formula input variables. In this example, the input variable HOURS_WORKED is numeric. If the input variable is not numeric, you must specify the type. For example,

INPUTS ARE START_DATE (DATE)

Expressions

Each function or calculation is one expression, and you can nest expressions to create more complex calculations. Brackets can be used to control the order in which calculations are performed. Expressions within brackets are evaluated first. Within nested brackets, evaluation proceeds from the least inclusive set to the most inclusive set. When brackets are not used the following hierarchal order or execution is implied: multiplication and division then addition and subtraction.

Expressions combine constants and variables with operators (+, -, *, /), array methods, and functions to return a value of a certain data type. For example, the expression (3 + 2) returns a value of 5, and is of NUMBER data type. The format of an expression is:

SUBEXPRESSION [operator SUBEXPRESSION ...]

A number of sub-expressions can combine in a single expression. For example, the sub-expressions (3 + 2) and MONTHS_BETWEEN(start_date, end_date) can combine in a single expression as follows:

(3 + 2) + MONTHS_BETWEEN(start_date, end_date)

Expressions can also be used inside functions, such as:

salary = GREATEST(minimum_wage, (hourly_rate * hours_worked))

Operands in an expression are usually of the same data type which is the data type of the expression as a whole. For example, in the following expression all the operands are numeric and the expression itself is numeric:

GREATEST(MINIMUM_WAGE, (HOURLY_RATE * HOURS_WORKED)) + BONUS

The operands for the above expression are BONUS, and the return value from GREATEST. The arguments for GREATEST are separate expressions.

Conditions

Conditions are used to process expressions based on whether a certain condition occurs. For example,

TRAINING_ALLOWANCE = 0 
IF (AGE < 20) THEN
TRAINING_ALLOWANCE = 30

This formula checks if the condition (AGE < 20) is true or false. If it is true, the formula processes the statement that follows the word THEN. If the condition is not true, the formula ignores this statement.

Comments

Use comments to explain how all or part of a formula is used. Also, you can change some formula lines into comments until they are ready to be used. Comments are designated by the comment delimiters of /* and */. Anything written inside these delimiters is a comment. You can place comments anywhere within a formula. The beginning of a formula should contain the following comments:

  • The formula title and a short purpose statement.

  • A description of the formula inputs.

  • A list of variables and literals that may require updating.

  • An explanation of the formula's calculation.

  • The dates of any modifications, the name of the person modifying the formula, and the reason for the change.

An example of a comment is:

/* Use this formula to determine the bonus percentage for staff */
INPUTS ARE SALARY_AMOUNT,
START_DATE (DATE),
END_PERIOD_DATE (DATE),
BONUS_PERCENTAGE /* Decided at board level. */

Note

Do not put a comment within a comment. This causes a syntax error when the formula is compiled.

Formula Statements: Explained

Statements are instructions that a formula carries out.

When working with statements, it is important to have knowledge of:

  • Statement types

  • Ordering statement

  • Grouping statements

When using statements in a formula you must enter them in the following order: alias statements, default statements, input statements, then other statements.

Statement Types

Use the alias statement to define another name, or alias, for existing variables. Declare aliases for database items and global values. An example of an alias statement is:


Statement

Example

Description

ALIAS

ALIAS name1 AS name2

ALIAS OVERTIME_QUALIFYING_LENGTH_OF_SERVICE AS OT_QLS

Allows a different name to be used instead of a database item name. Database items are named by the system and sometimes these names are too long to conveniently use in a formula. Use the ALIAS statement to shorten the name of a database item. Once the ALIAS is created, use it instead of the database item name.

Using an alias is more efficient than assigning the database item to a local variable with a short name.

ASSIGNMENT

variable = expression

array[index] = expression

RATE = HOURLY_RATE + 14
WAGE = HOURS_WORKED * RATE

Assigns an expression value to a variable or an array variable at an index position. A formula evaluates the expression on the right hand side of the statement, and places its result in the variable you name on the left hand side. The left side of an assignment statement must always be a local variable because a formula can only change the value of local variables.

Within a CHANGE-CONTEXTS statement only contexts may be assigned values. External to a CHANGE-CONTEXTS statement only input, output, and local variables may be assigned values.

CHANGE-CONTEXTS

CHANGE_CONTEXTS

(context1 = expression1 [,context2 = expression2 ] ...

CHANGE_CONTEXTS(AREA1 = TAX_REPORTING_UNIT_INCOME_TAX_JURISDICTION_GEOGRAPHY_ID)
(
  CHANGE_CONTEXTS(DEDUCTION_TYPE = 'SBJ_TO_REGULAR_TAX')
  (
    L_TAXATION_METHOD = 'NONE'
    EXECUTE('TAXABILITY_RULE_EXISTS')
    IF GET_OUTPUT('TR_EXISTS', 'N') = 'Y' THEN
      L_TAXATION_METHOD = 'REGULAR_TAX'
  ) /* DEDUCTION_TYPE context change undone here. */
) /* AREA1 context change undone here. */

Allows one or more contexts to be changed within a formula. The new values are assigned by one or more ASSIGNMENT statements.

DEFAULT

DEFAULT FOR variable IS literal

DEFAULT_DATA_VALUE FOR variable IS literal

DEFAULT FOR HOURLY_RATE IS 3.00
INPUTS ARE HOURLY_RATE
X = HOURS_WORKED * HOURLY_RATE

The DEFAULT FOR statement allows a value to be used for a formula input if a value is not provided. The DEFAULT FOR statement allows a value to be used for a database item if the database item value is not found, or if a non-array database item value is NULL.

The DEFAULT_DATA_VALUE FOR statement allows a value to be used for an array database item where individual data values are NULL.

Some database items are defined to require a default value because they could return no data or NULL values from the database.

EXIT

EXIT

FOUND = -1 /* -1 is not a valid index for A. */
I = A.FIRST(-1)
WHILE (A.EXISTS(I)) LOOP
(
  /* EXIT-clause for early exit. */
  IF A[I] = KEY THEN
  (
    FOUND = I
    /* Exit the loop. */
    EXIT;
  )
  I = A.NEXT(I,-1)
)

Used to immediately exit from the enclosing WHILE loop. The EXIT statement cannot be used outside of a WHILE loop.

FORMULA CALLING

SET_INPUT(input [,value])

EXECUTE(formula)

The formula RATE_FORMULA is called to get a value for HOURLY_RATE. RATE_FORMULA.

SET_INPUT('UNIT','Hourly')
EXECUTE('RATE_FORMULA')
HOURLY_RATE = GET_OUTPUT('RATE',0.0)
WAGE = HOURS_WORKED * HOURLY_RATE
RETURN WAGE

Instead of writing large formulas, common calculations can be put into their own smaller formulas. These calculation formulas can then be called from other formulas that need the calculation.

IF

IF condition THEN statements

IF condition THEN statements ELSE statements

IF (AGE < 20) THEN
  TRAINING_ALLOWANCE = 30
ELSETRAINING_ALLOWANCE = 40

Allows one or more statements to be executed provided a condition evaluates as true. The IF ELSE statement also specifies a set of statements to execute if the condition evaluates to false.

The IF statement is the only statement that can have other statements nested within it, including other IF statements.

INPUT

INPUTS ARE input1 [, input2] ...

INPUTS ARE HOURS_WORKED
WAGE = HOURS_WORKED * HOURLY_RATE
RETURN WAGE

Lists the input variables for the formula. There is only one INPUT statement in a formula.

RETURN

RETURN [ output1 ] [,output2] ...

INPUTS ARE HOURS_WORKED
IF HOURS_WORKED <= 10 THEN
(
  RETURN
  /* This is ignored. */
  BONUS = 10
)
/* This is executed if HOURS_WORKED > 10. */
BONUS = 50
RETURN BONUS

Causes a formula to stop executing immediately. A formula output variable must appear in the RETURN statement that stopped the formula for its value to be returned to the caller. Multiple return statements are allowed in a formula.

WHILE

WHILE condition LOOP statements

In this example, 'A' is an array variable with a numerical index.

/* -1234 is not a valid index for A in this instance, so use as default. */
NI = A.FIRST(-1234)
WHILE A.EXISTS(NI) LOOP
(
  VA = A[NI] /* Do some processing with element at index NI. */
  NI = A.NEXT(NI,-1234) /* Go to next index. */
)

The WHILE loop executes a number of statements as long as a condition evaluates to true.

To prevent endless looping, an error occurs if the WHILE statement loop performs an excessive number of iterations.

WORKING STORAGE

WSA_DELETE([item]) - Deletes values from the storage area.

WSA_EXISTS(item[,type]) - Determine if an item exists .

WSA_GET(item, value) - Fetches values from the storage area.

WSA_SET(item, value) - Sets values from the storage area.

In the following example, a number of rates are set up:

/* Formula: RATE_SETTER */
WSA_SET('RATE:HOURLY1',3.5)
WSA_SET('RATE:HOURLY2',4.0)
WSA_SET('RATE:HOURLY3',4.5)
WSA_SET('RATE_FLAG','Y') /* Flag to say that the rates have been set. */

Use the working storage area statements to store reference data.

Ordering Statements

Statements need to be placed in a formula in the following order:

  1. ALIAS statements, if any

  2. DEFAULT statements, if any

  3. INPUT statements, if any

  4. Other statements

Grouping Statements

If you want to group more than one statement, under IF - THEN statement, ELSE clauses, WHILE-loop, or CHANGE_CONTEXTS, enclose the group of statements within brackets. In the absence of brackets, the preceding statement only applies to the first statement.

Correct example:

I = A.FIRST
WHILE (A.EXISTS(I)) LOOP
(
  A[I] = I
  I = A.NEXT(I,-1)
)

Incorrect example:

I = A.FIRST
WHILE (A.EXISTS(I)) LOOP
  A[I] = I
  I = A.NEXT(I,-1) /* This is not executed as part of the loop. */

Classes of Variables: Explained

Formula variables can have frequently changing values. The variable's data type determines the type of information it holds. You do not have to specify what type you want a variable to be. The formula works out the type from how you use the variable. For example, if you set a variable to 'J. Smith', this is interpreted as a TEXT variable. The system also warns you if you try to perform any inconsistent operations, such as trying to add a number to a text string.

There are three classes of variables:

  • Input variables appear in INPUTS statements and bring values into a formula.

  • Output variables appear in RETURN statements and return values from a formula. A variable can be both an input and output.

  • Local variables are only used within one formula.

Variable values can be changed using an assignment statement and referenced within expressions. However, if a variable has not been given a value when referenced, the formula will raise an error.

Naming Variables: Explained

There are two naming schemes for variables. In both cases, the maximum size of a variable name is 255 characters.

In the first naming scheme, variables have names comprising one or more words, joined by underscores. The words must each start with a letter and can be followed by a combination of letters, and digits.

In the second naming scheme, variable names begin and end with double quotes ("). Between the quotes, any printable characters can be used such as "This is a quoted variable name!". Note that any word consisting of only digits could be mistaken for numbers.

Formulas are not case sensitive. For example, the variable named EMPLOYEE_NAME is the same as the variable employee_name.

The following reserved words cannot be used as the names of variables:

ALIAS
AND
ARE
AS
CHANGE_CONTEXTS
DEFAULT
DEFAULT_DATA_VALUE
DEFAULTED
ELSE
EMPTY_DATE_NUMBER
EMPTY_NUMBER_NUMBER
EMPTY_TEXT_NUMBER
EMPTY_DATE_TEXT
EMPTY_NUMBER_TEXT
EMPTY_TEXT_TEXT
EXIT
FOR
IF
INPUTS
IS
LIKE
LOOP
NEED_CONTEXT
NOT
OR
RETURN
THEN
USING
WAS
WHILE

Formula Data Types

DATE
DATE_NUMBER
DATE_TEXT
NUMBER
NUMBER_NUMBER
NUMBER_TEXT
TEXT
TEXT_NUMBER
TEXT_TEXT

Array Methods

COUNT
DELETE
EXISTS
FIRST
LAST
NEXT
PREVIOUS
PRIOR

Built-in Calls

CONTEXT_IS_SET
EXECUTE
GET_CONTEXT
GET_OUTPUT
IS_EXECUTABLE
SET_INPUT
WSA_DELETE
WSA_EXISTS
WSA_GET
WSA_SET

Database Items: Explained

Database items exist in the application database and have associated code that the system uses to find the data. All database items are read-only variables. An attempt to write to a database item causes a compiler error.

Database item values cannot be changed within a formula. Database items exist in the application database and have a label, hidden from users, which the system uses to find the data. Database items are specific to the context in which you use them.

There are two types of database items:

  • Static

  • Dynamic

Static Database Items

Static database items are predefined. They include standard types of information, such as the sex, birth date, and work location of an employee, or the start and end dates of a payroll period.

Dynamic Database Items

Dynamic database items are generated from your definitions of:

  • Elements

    The element name is the database item name prefix.

  • Balances

    The balance name followed by balance dimension name is the database item name.

  • Formula global values

    The global value name is the database item name.

  • Input values

    The element and input value names are the database item name prefix.

  • Flexfields

    The Generate Flexfield Database Items process creates database items for the contexts and segments of your registered HCM flexfields.

Array Database Items

There are also array database items. Array database items have an index type of NUMBER with indexes starting at 1 and increasing by 1 without gaps.

/* 1 is the starting index for an array database item. */
I = 1
WHILE DBI.EXISTS(I) LOOP
(
  V = DBI[I] /* Do some processing with element at index I. */
  I = I + 1 /* Array database items indexes go up in steps of 1. */
)

The default data value for a statement is used to set a default value in the case where an array database item could return a NULL value for an element. This is an extension of standard database item behavior. There can only be one default data value for a statement for each array database item and it must appear at the start of the formula.

An example of a default data value for a statement:

DEFAULT_DATA_VALUE FOR A IS 0
INPUTS ARE B, C

An example of an array database item usage error case:

/* Array database item A. */
A[1] = 1
 A = B
A.DELETE(1)
A.DELETE

Formula Operators: Explained

An expression may contain arithmetic operators, which determine how variables and literals are manipulated. For example, the operator + indicates that two items are added together. It is also used for string concatenation.

Types of Operators

The operator types are described in the following table.


Operator

Description

Example

+

Addition

A = B + 1

+

| |

String concatenation

A = 'Hello ' + 'World'

B = 'Hello ' || 'World'

-

Subtraction

A = B - 1

-

Unary minus

A = -B

*

Multiplication

A = B * C

/

Division

A = B / C

Using Operators

The arithmetic operators, subtraction, multiplication, and division, can only be used with numeric operands. The addition operator can be used with numeric or text operands. The operands can be variables, literals, or sub-expressions. A formula error occurs if:

  • The second operand of a division equals zero

  • The result of multiplication is too large

    What is too large is determined by the normal limits in the database. For string concatenation, if the result is longer than 255 characters, a formula error is raised.

Expressions are evaluated in order from left to right. The unary minus has precedence over the other operators because it applies directly to a single sub-expression. The multiplication and division operators take precedence over addition and subtraction. For example, the expression 1 + 2 * 3 evaluates to 7 rather than 9. Brackets can be used to change precedence. For example, (1 + 2) * 3 evaluates to 9.

Literals: Explained

Every piece of information that you can manipulate or use in a fast formula is a literal.

There are four types of literals:

  • Numeric

  • Text

  • Date

  • Array

Numeric Literals

Enter numeric literals without quotes. Precede negative numbers with a minus sign (-). Numbers may have a decimal component after a decimal point. Do not use exponents and floating point scientific notations. Do not use commas or spaces in a number.

Examples of numeric literals are:

  • 63

  • 3.55

  • -2.3

  • -.033

  • -.2

  • 10000

Text Literals

Enclose text literals in single quotes. They may contain spaces. You can represent the single quote character in a text constant by writing it twice (''). Note that this is not the same as the double quote (").

Examples of text literals are:

  • 'J. Smith'

  • 'P O''Donnell'

  • '1234'

  • 'Manager'

  • '12 Union Road'

  • 'The Bonus this year is 23%'

Date Literals

Enclose dates in single quotes and follow immediately with the word date, in brackets. Use the format YYYY-MM-DD"T" HH:MI:SS.FFF"Z", YYYY-MM-DD HH24:MI:SS, or DD-MON-YYYY. It is recommended that you use the first two formats if you want to compile the formula under different language settings.

Examples of date literals are:

  • '2010-11-04T00:00:00.000Z' (DATE)

  • '1989-03-12 00:00:00' (DATE)

  • '12-MAR-1989' (DATE)

Array Literals

An array holds multiple values that can be accessed using the corresponding index values. Arrays literals are defined only for an empty array of each type.

The array types are:

  • Array of date values indexed by a numeric index (EMPTY_DATE_NUMBER)

  • Array of number values indexed by a numeric index (EMPTY_NUMBER_NUMBER)

  • Array of text values indexed by a numeric index (EMPTY_TEXT_NUMBER)

  • Array of date values indexed by a text index (EMPTY_DATE_TEXT)

  • Array of numeric values indexed by a text index (EMPTY_NUMBER_TEXT)

  • Array of text values indexed by a text index (EMPTY_TEXT_TEXT)

Generating Flexfield Database Items: Explained

You configure registered HCM flexfields to add contexts and segments for your business requirements. After you deploy the flexfield, you can generate database items for the flexfield for use in your formulas and extracts by submitting the Generate Flexfield Database Items process from the Payroll Checklist or Payroll Calculation work areas.

You can generate DBIs for the following flexfields:

  • Descriptive flexfields

  • Extensible flexfields for single and multiple row routes

  • Key flexfields

The process generates DBIs at the enterprise level only. As a best practice, when you submit the process, skip the legislative data group parameter so that the process generates DBIs for use by any legislative data group.

You can determine which DBIs to generate by specifying or skipping the flexfield and context parameters.


Flexfield Parameter

Context Parameter

Result

Specify parameter

Skip parameter

Generate DBIs for all the contexts and related segments for a specified flexfield

Skip parameter

Skip parameter

Generate DBIs for all registered flexfields and their contexts.

The process creates database item names with this following structure:

<FLEXFIELD_CODE><CONTEXT_CODE><SEGMENT_CODE>

When you include the database item in a formula or extract, the application returns a value for the database item, based on the flexfield context, for the segments column in the underlying flexfield table. After you generate DBIs, compile any formulas using these DBIs.

Periodically, you may need to update a flexfield structure, for example to add a segment to capture additional data. If you previously generated DBIs for a flexflield, submitting the process deletes and regenerates its DBIs. After the process regenerates the DBIs, be sure to compile any formulas using them.

Formula Variable Data Types: How They Are Determined

The data type of a variable can be numeric, text or date. The data type determines the type of information the variable holds. You do not have to specify the variable type. Formulas determine the type from how you use the variable. For example, if you set a variable to 'J. Smith', this is interpreted as a text variable. The system also warns you if you try to perform any inconsistent operations, such as trying to add a number to a text string.

Settings That Affect Variable Data Types

A formula will fail to compile where variables are used inconsistently or incorrectly. Examples of such errors are:

  • The formula attempts to alter a database item value.

  • A variable is initially determined to be of one data type, but is later used as another data type. For example, the assignment C = 1 shows that C is a number variable. This is inconsistent when later in the formula C = 'Hello' is entered. In this case, C is being used as a text variable.

  • The compiler can determine that a variable has not been assigned a value when it is used. Note: If the compiler thinks there might be a problem, it generates code to raise an error if the variable is not initialized at the time of use.

  • A variable is determined to be a formula context but then is used as an ordinary local variable.

  • A variable is determined to be a local variable, but is then used as a context within a context handling statement.

How Variable Data Types Are Determined

The rules that determine the variable data type in a formula are processed in the following order:

  1. The variable can be an input you name in the input statement. For example:

    INPUTS ARE SALARY_AMOUNT,
    START_DATE (DATE),
    FREQUENCY (TEXT)
    

    If you do not specify the variable data type in the statement, the formula assumes it has data type number.

    The variable data type can be determined from a DEFAULT FOR statement such as:

    DEFAULT FOR A IS EMPTY_NUMBER_NUMBER /* A is a NUMBER_NUMBER array variable. */
    

    The type can also be determined from a DEFAULT_DATA_VALUE statement:

    DEFAULT_DATA_VALUE FOR B IS 0 /* B is a NUMBER_NUMBER database item. */
    

    The DEFAULT_DATA_VALUE statement applies to array database items. Array database items have a NUMBER index type and the type of default data value determines the array's value type.

  2. The formula searches the list of database items. If it is in the list, the data type is known.

  3. If the variable appears in a context handling statement then the formula searches the list of contexts. If it is in the list, then the formula knows the data type, otherwise an error is raised.

  4. If the variable is not a database item or a context, then it is treated as a local variable. The data type is determined by the way in which the variable is used. For example:

    A = 'abc' /* A is a TEXT variable. */
    

Generating Flexfield Database Items: Explained

You configure registered HCM flexfields to add contexts and segments for your business requirements. After you deploy the flexfield, you can generate database items for the flexfield for use in your formulas and extracts by submitting the Generate Flexfield Database Items process from the Payroll Checklist or Payroll Calculation work areas.

You can generate DBIs for the following flexfields:

  • Descriptive flexfields

  • Extensible flexfields for single and multiple row routes

  • Key flexfields

The process generates DBIs at the enterprise level only. As a best practice, when you submit the process, skip the legislative data group parameter so that the process generates DBIs for use by any legislative data group.

You can determine which DBIs to generate by specifying or skipping the flexfield and context parameters.


Flexfield Parameter

Context Parameter

Result

Specify parameter

Skip parameter

Generate DBIs for all the contexts and related segments for a specified flexfield

Skip parameter

Skip parameter

Generate DBIs for all registered flexfields and their contexts.

The process creates database item names with this following structure:

<FLEXFIELD_CODE><CONTEXT_CODE><SEGMENT_CODE>

When you include the database item in a formula or extract, the application returns a value for the database item, based on the flexfield context, for the segments column in the underlying flexfield table. After you generate DBIs, compile any formulas using these DBIs.

Periodically, you may need to update a flexfield structure, for example to add a segment to capture additional data. If you previously generated DBIs for a flexflield, submitting the process deletes and regenerates its DBIs. After the process regenerates the DBIs, be sure to compile any formulas using them.

Array Variables: Explained

Formulas have array variables holding date, number, or text values. If the data type cannot be determined then the data type is defaulted to number.

Arrays are similar to PL/SQL index-by tables. Do not specify the array size limit. Arrays are provided for convenience and excessive use and large arrays will result in excessive memory consumption.

The index types are either text or number. Text indexes are upper case unique. Gaps in index value sequences are permitted. Number indexes are truncated to remove any fractional part. An array may be iterated in index forwards or backwards. Methods are provided to get the first and last indexes and to get the next or prior index given an index. A method is also provided to test the existence of an index.

Array types are specified as <DATA TYPE>_<INDEX TYPE> giving: NUMBER_NUMBER, NUMBER_TEXT, DATE_NUMBER, DATE_TEXT, TEXT_NUMBER, and TEXT_TEXT. Arrays can be used for input, output, and local formula variables. Contexts cannot be array types. Formula functions cannot return arrays nor take array parameters. Methods for returning first, last, next, prior indexes take a default value to be used if the required indexes do not exist. These methods return the index data type. An attempt to delete a value at a nonexistent index does not cause an error. An attempt to reference an array value at a nonexistent index causes an error to be raised. The array method syntax does not work directly with the array literal values. For example, it is not possible to use a construct such as EMPTY_DATE_NUMBER.COUNT.

Array Methods

Examples of array method syntax:


Array Method

Description

Usage Example

<name> [ <index value> ]

Get the value for an index.

V = A[1]

<name> . FIRST( <default value> )

Get the first index for an array. The default value is returned if the array is empty.

I = A.FIRST(-1)

<name> . LAST( <default value> )

Get the last index for an array.

L = B.LAST(' ')

<name> . EXISTS( <index value> )

Conditional checking if a value exists at an index. The default value is returned if the array is empty.

IF A.EXISTS(1) THEN

<name> . NEXT( <index value> , <default index value> )

Get the next index given an index position. The default value is returned if there is no next index.

N = A.NEXT(1)

<name> . PRIOR( <index value> , <default index value> )

Get the prior index given the index position. The default value is returned if there is no prior index.

P = B.PRIOR('Two')

<name> , COUNT

Numeric method to count the array elements.

C = A.COUNT

<name , DELETE( <index value> )

Delete the element at an index position.

B.DELETE('three')

<name> , DELETE()

Delete all elements.

B.DELETE()

Iterating Through an Array

In the following example, A is an array variable with a NUMBER index. -1234 is known to be an invalid index for A so it is used as a default value when the FIRST and NEXT calls cannot find an index.

/* -1234 is not a valid index for A in this instance, so use as default. */
NI = A.FIRST(-1234)
WHILE A.EXISTS(NI) LOOP
(
  VA = A[NI] /* Do some processing with element at index NI. */
  NI = A.NEXT(NI,-1234) /* Go to next index. */
)

The following example does the same thing for array variable B with a TEXT index.

/* 'No Index' is not a valid index for A in this instance, so use as default. */
TI = B.FIRST('No Index')
WHILE B.EXISTS(TI) LOOP
(
  VB = B[TI] /* Do some processing with element at index TI. */
  TI = B.NEXT(TI, 'No Index') /* Go to next index. */
)
The following example iterates backwards from through an array C with a NUMBER index.
/* -1234 is not a valid index for C in this instance, so use as default. */
NI = C.LAST(-1234)
WHILE C.EXISTS(NI) LOOP
(
  VC = C[NI] /* Do some processing with element at index NI. */
  NI = C.PRIOR(NI,-1234) /* Go to prior index. */

Formula Contexts: Explained

A formula executes within an application-specific execution context. Formula context variables specify the formula execution context.

Examples of contexts are:

  • EFFECTIVE_DATE for the effective date the formula is executing

  • PAYROLL_ID for the running payroll

  • PERSON_ID identifying the person for whom the formula is executing

Context values act as SQL bind values when database item values are fetched from the database. They can also be passed into formula function calls.

Context Value Setting

The application code calling a formula usually sets all the context values. For some complex applications, such as the payroll run, the code only sets the contexts necessary to meet general processing requirements. A payroll run sets contexts for the legislative data group, date earned, the payroll being processed, the payroll relationship, payroll actions, and the person being processed. Payroll formulas also use additional contexts whose setting is country-specific. For example, the jurisdiction area and tax code context values are localization-specific and are set within formulas using a variety of mechanisms.

Formula Context-Handling Statements

If a variable appears in a context handling statement the formula searches the list of contexts. The variable must appear in the contexts list, otherwise the formula raises an error. The data type is held with the context list entry.

Formula context handling statements are described below.

  • CHANGE_CONTEXTS(assignment [,...]) - Context values may be changed within a formula using a context changing block, but after leaving the context changing block any changed context values are restored to their previous values. Inside the context changing block, formula function calls, database items, and called formulas use the new context values. Context changing blocks may be nested where context changes need to be applied in stages.

    For example:

    /*
     * Nested Context changes: DBI1 depends upon SOURCE_ID and SOURCE_TEXT. */
    CHANGE_CONTEXTS(SOURCE_TEXT = 'A')
    (
      /* SOURCE_TEXT = 'A' */
      X = DBI1
     
      /* Nesting used to change Contexts in stages. */
      CHANGE_CONTEXT(SOURCE_ID = 2)
      (
        /* SOURCE_TEXT = 'A', SOURCE_ID  = 2 */
        Y = DBI1
        
        /* Overriding a Context change. */
        CHANGE_CONTEXTS(SOURCE_TEXT = 'B',SOURCE_ID = 3)
        (
          /* SOURCE_TEXT = 'B', SOURCE_ID  = 3 */
          Z = DBI1
        )
      )
    )
    
  • CONTEXT_IS_SET(context) - Tests whether or not a context value is set.

    For example, the following code tests whether or not the AREA3 context is set.

    IF CONTEXT_IS_SET(AREA3) THEN
    
  • GET_CONTEXT(context, default value) - Returns a context's value if the context is set, otherwise it returns the default value specified in its second argument.

    For example:

    /* AREA1 is a context of type TEXT. */
    AREA1_VALUE = GET_CONTEXT(AREA1,' ')
    
    

Working Storage Area: Explained

The working storage area is a mechanism for storing global values across formulas. The values are accessed by name. The names are case-independent.

There are four working storage area call methods:

  • WSA_EXISTS

  • WSA_DELETE

  • WSA_SET

  • WSA_GET

The working storage area methods are described in the below table.


Method

Description

WSA_EXISTS(item [, type])

Test whether or not the item called item exists in the storage area. If type is specified then the item must be of the same type. The valid values for type are one of the strings: DATE, DATE_NUMBER, DATE_TEXT, NUMBER, NUMBER_NUMBER, NUMBER_TEXT, TEXT, TEXT_NUMBER, or TEXT_TEXT.

WSA_DELETE([item])

Delete the item called item. If a name is not specified then all storage area data is deleted.

WSA_SET(item, value)

Set the value for the item called item. Any existing item of the same name is overwritten.

WSA_GET(item, default-value)

Gets a value for the item called item. If there is no item called item then default-value is returned. The data type of default-value is the expected data type for item.

Calling a Formula from a Formula: Explained

A formula can be called from another formula. This enables some modularity in formula organization. The called formula name, and any formula input or output names are specified as TEXT values. The names are case-independent. There are two alternative approached to calling a formula: using a single call, or separate calls.

Consider the following aspects:

  • Validation of the Called Formula

  • Passing Contexts

  • Alternative Methods to Call a Formula

    • Using Separate Calls

    • Using a Single Self-Contained Call

  • Use Cases to Compare Methods

Validation of the Called Formula

When the formula runs, checks are performed to ensure the called formula can be executed, and whether the specified input and output data types are correct. You can use the IS_EXECUTABLE call to determine whether an executable formula with a specified name exists. The formula must be compiled and available for the specified legislative data group. Also, it must be valid as of the effective date of calling formula execution. Payroll code imposes extra restrictions based on formula type combinations.

Passing Contexts

Context values are inherited from the calling formula. You can also set or unset the context values explicitly in the nested formula call.

Alternative Methods to Call a Formula

There are two ways to call a formula from a formula:

  • Using a series of separate calls

  • Using a single self-contained call

Using Separate Calls

If you are using separate calls, there are three stages in calling a formula:

  1. Set the Inputs

    Use a SET_INPUT call for each formula input and context that you need to explicitly set for the formula call. You don't need to specify all formula inputs and contexts. To explicitly unset a context values, use the SET_INPUT call without passing the optional value parameter. Any extra inputs specified in SET_INPUT calls are ignored.

  2. Call the Formula

    Use an EXECUTE call to call a formula. Errors are raised if the formula is not executable, if the called formula is already running, or if an input variable's data type, as specified using SET_INPUT, does not match its actual data type within the formula.

  3. Get the Formula Outputs

    Use one or more GET_OUTPUT calls to fetch outputs from the last formula call. An error is raised if an output variable's data type, as specified using GET_OUTPUT, does not match its actual data type within the formula.

The following table summarizes the methods for calling a formula using separate calls.


Method

Description

SET_INPUT(input [,value])

The value parameter is optional. If it's provided, the specified input is set to this value. If it's not provided, the If a value is provided, the input is passed as unset to the formula. The data type of the value is the expected data type for the input.

EXECUTE(formula)

Executes the called formula.

GET_OUTPUT(output, default-value)

Gets the value of the output parameter after calling a formula. If there's no formula output called 'output' or it's not set, then the value specified in the default value parameter is returned. The data type of default value is the expected data type for output.

Note

Formula inputs set using SET_INPUT persist as long as no EXECUTE or GET_OUTPUT calls are made. Output values from a called formula persist as long as no SET_INPUT or new EXECUTE calls are made. Any saved input or output values are also removed when the calling formula exits.

Using a Single-Self Contained Call

The end result with this approach is the same as using separate calls except that:

  • Input values are cleared at the start so that prior SET_INPUT call values are not used.

  • Outputs are discarded at the end so that subsequent GET_OUTPUT calls just return the default values.

Use the CALL_FORMULA method as follows:

CALL_FORMULA(formula, [set statement, get statement])

A SET statement is a SET_INPUT call. A GET statement assigns a GET_OUTPUT call result to a variable in the calling formula. The execution order is:

  1. SET_INPUT calls

  2. EXECUTE call

  3. GET_OUTPUT assignments

The compiler generates code to execute in this order even if SET and GET statements are interspersed.

Use Cases to Compare Methods

The following table describes complex expressions used for called formula validation names, setting input values, and providing default output values.

Note

SET_INPUT or > statements have no effect if the calling formula has no formula input or context of the same name.


Use Case

Using Separate Calls

Using a Self-Contained Call

Execute a formula where the formula GET_RATES is executed

EXECUTE('GET_RATES')

Use within a CALL_FORMULA statement

'GET_RATES'

Set an input value in the called formula where you round up EXTRA_HOURS to 2 decimal places and set the input OVERTIME in the called formula.

The called formula should contain the statement:

INPUTS ARE OVERTIME
SET_INPUT
('OVERTIME'
,ROUNDUP(EXTRA_HOURS,2)
)

Use within a CALL_FORMULA statement

ROUNDUP(EXTRA_HOURS,2) >'OVERTIME'

Leave a formula input value unset inside the called formula, where RATE is not a formula context.

A SET_INPUTS statement is not required, but the following can be used:

SET_INPUT('RATE')

A SET statement is not required, but the following can be used :

> 'RATE'

Inherit a context value from the called formula.

For example, both the calling and called formula support the AREA1 context. We inherit the AREA1 context value from the calling formula in the calling formula.

No statements are required to do this.

No statements are required to do this.

Set a context value inside a called formula, where the called formula supports the AREA1 context and AREA1 has to be set to 'London' in the called formula.

SET_INPUT
('AREA1'
,'London'
)

'London' > 'AREA1'

Call a formula with an unset context value, where the called formula supports the AREA1 context and AREA1 has to be unset in the called formula.

SET_INPUT('AREA1')
> 'AREA1'

Get a formula output from the called formula.

Get BONUS_RATE output value into the RATE variable using the default value 0.0 if the BONUS_RATE output does not exist or was not set.

RATE = 
GET_OUTPUT
('BONUS_RATE'
,0.0
)

RATE <'BONUS_RATE' DEFAULT 0.0

Get a formula output from a called formula into an array

Get the BONUS_RATE output value into the RATES array variable at index position 'BONUS'. The default value 0.0 is used if the BONUS_RATE output does not exist or was not set.

RATES['BONUS'] = 
GET_OUTPUT
('BONUS_RATE'
,0.0
)

RATES['BONUS'] <'BONUS_RATE' DEFAULT 0.0

Calling a Formula from a Formula: Examples

These examples illustrate a formula called from another formula as a series of separate calls or as a single self-contained call. Aspects in both examples are:

  • The formula RATE_FORMULA is called to get a value for HOURLY_RATE.

  • The RATE_FORMULA has a text input of UNITS.

  • The UNIT input is set to 'Hourly' in the formula call.

  • The RATE_FORMULA returns the rate in the output variable of RATE.

  • The GET_OUTPUT call returns 0.00 if the RATE_FORMULA does not return RATE.

The following examples show modified versions of the wage formula.

Separate Calls

This example illustrates a formula call from separate calls.

SET_INPUT('UNIT', 'Hourly')
EXECUTE('RATE_FORMULA')
HOURLY_RATE = GET_OUTPUT('RATE',0.0)
WAGE = HOURS_WORKED * HOURLY_RATE
RETURN WAGE

Self-Contained Call

This example illustrates a formula called from a self-contained call.

CALL_FORMULA 
('RATE_FORMULA','Hourly' > 'UNIT' 
/* SET_INPUT('UNIT', 'Hourly') */
,HOURLY_RATE < 'RATE' DEFAULT 0.0 
/* HOURLY_RATE = GET_OUTPUT('RATE',0.0) */
)
WAGE = HOURS_WORKED*HOURLY_RATE
RETURN RATE