Cloud Documentation

Using Fast Formula

This guide also applies to on-premise implementations

Show All | Collapse

# 2 Writing Formulas

This chapter contains the following:

Writing a Fast Formula using Expression Editor: Worked Example

Writing a Fast Formula Using Formula Text: Worked Example

Formula Performance Improvements: Explained

Formula Compilation Errors: Explained

Formula Execution Errors: Explained

FAQs for Writing Formulas

## Writing a Fast Formula using Expression Editor: Worked Example

This example demonstrates how to create a fast formula that groups executive workers for reporting and processing. All executive workers are in department EXECT_10000. Once the formula is created it will be added as object group parameters so that only those workers in department EXECT_10000 are used in processing.

The following table summarizes key decisions for this scenario:

Decisions to Consider

In This Example

Is the formula for a specific legislative data group?

Yes, InVision

What is the formula type for this formula?

Payroll Relationship Group

### Creating a Fast Formula Using the Expression Editor

1. On the Payroll Calculation Tasks page, click Manage Fast Formulas to open the Manage Fast Formulas page.
2. On the Manage Fast Formula page, click the Create icon to create a new formula.
3. On the Create Fast Formula page, complete the fields as shown in this table.

Field

Value

Formula Name

Executive Payroll Relationship Group

Type

Payroll Relationship Group

Description

Executive Workers

Legislative Data Group

Vision LDG

Effective As-of Date

1-Jan-2010

4. Click Continue.
5. In the Formula Details section, click Add After to add a row to enter the fields in this table.

Conjunction

Database Item Name

Data Type

Operand

Literal Value

IF

DEPARTMENT

Character

=

'EXECT_10000'

Then

SELECT_EMP

Character

=

'YES'

ELSE

SELECT_EMP

Character

=

'NO'

6. Click Compile.
7. Click Save.

## Writing a Fast Formula Using Formula Text: Worked Example

This example demonstrates, using the text editor, how to create a fast formula that returns the range of scheduled hours for managers and a different range for other workers.

The following table summarizes key decisions for this scenario:

Decisions to Consider

In This Example

Is the formula for a specific legislative data group?

No, this is a global formula that can be used by any legislative data group.

What is the formula type for this formula?

Range of Scheduled Hours

Are there any contexts used in this formula?

No

Are there any database item defaults?

Yes, ASG_JOB

Are there any input value defaults?

No

What are the return values?

MIN_HOURS, MAX_HOURS, FREQUENCY

### Creating a Fast Formula Using the Text Editor to Determine a Manager's Scheduled Hours

1. On the Payroll Calculation Tasks page, click Manage Fast Formulas to open the Manage Fast Formulas page.
2. On the Manage Fast Formula page, click the Create icon to create a new formula.
3. On the Create Fast Formula page, complete the fields as shown in this table.

Field

Value

Formula Name

Manager Range of Scheduled Hours

Formula Type

Range of Scheduled Hours

Description

Manager's Range of Hours

Effective Start Date

1-Jan-2010

4. Click Continue.
5. Enter the following formula details in the Formula Text section:
```/* DATABASE ITEM DEFAULTS BEGIN */
DEFAULT FOR asg_job IS ' '
/* DATABASE ITEM DEFAULTS END */
JOB_1 = ASG_JOB
IF JOB_1 = 'Manager' then
(MIN_HOURS = 25
MAX_HOURS = 40
FREQUENCY = 'H')
else
(MIN_HOURS = 20
MAX_HOURS = 35
FREQUENCY = 'H')
return MIN_HOURS, MAX_HOURS, FREQUENCY
```
6. Click Compile.
7. Click Save.

## Formula Performance Improvements: Explained

When writing formulas there are a number of techniques to follow to ensure your formulas are easy to read, use, understand, and process efficiently.

### Variable Names and Aliases

To improve readability, use names that are brief yet meaningful. Use aliases if the names of database items are long. Name length has no effect on performance or memory usage.

### Inputs Statements

Use INPUTS statements rather than database items whenever possible. It speeds up the running of your payroll by eliminating the need to access the database for the input variables.

An example of inefficient formula without INPUTS statement is:

```SALARY = SALARY_ANNUAL_SALARY / 12
RETURN SALARY
```

An example of efficient use of INPUTS statements is:

```INPUTS ARE ANNUAL_SALARY
SALARY = ANNUAL_SALARY / 12
RETURN SALARY
```

### Database Items

Do not refer to database items until you need them. People sometimes list at the top of a formula all the database items the formula might need, thinking this helps the formula process more quickly. However, this in fact slows processing by causing unnecessary database calls.

An example of an inefficient use of database items is:

```S = SALARY
A = AGE
IF S < 20000 THEN
IF A < 20 THEN
TRAINING_ALLOWANCE = 30
ELSE
TRAINING_ALLOWANCE = 0
```

An example of an efficient use of database items is:

```IF SALARY < 20000 THEN
IF AGE < 20 THEN
TRAINING_ALLOWANCE = 30
ELSE
TRAINING_ALLOWANCE = 0
```

The first example always causes a database fetch for AGE whereas the second only fetches AGE if salary is less than 20000.

### Balance Dimensions

Wherever possible, use balance dimensions for single assignments only in formulas. Multiple assignments require more calculation time, leading to slower processing time. The number of multiple assignments in a payroll is not normally high, and the presence of a small number does not lead to any significant increase in overall processing time. However, there could be a problem if you unnecessarily link balance dimensions for multiple assignments into general formulas.

## Formula Compilation Errors: Explained

Compilation errors display in the Manage Fast Formulas page when you compile the formula. The formula compiler returns line numbers starting at 1 from the beginning of a formula, and character positions starting at 1 from the beginning of a line in its error messages. The compiler aborts compilation when an error is encountered.

### Common Compilation Errors

This table lists the type and description of several common formula compilation errors.

Formula Error

Description

Syntax Error

The formula text violates the grammatical rules for the formula language. An example is using `IF1` instead of `IF` for an `IF` statement.

Incorrect Statement Order

`ALIAS`, `DEFAULT`, or `INPUT` statements come after other statements.

Misuse of `ASSIGNMENT` Statement

Occurs when any of these conditions occurs:

• An `ASSIGNMENT` assigns a value to a database item.

• A context is assigned a value externally to a `CHANGE-CONTEXTS` statement.

• A non-context variable is assigned a value within a `CHANGE-CONTEXTS` statement.

Misuse of `ALIAS` Statement

An `ALIAS` statement may only be used for a database item.

Missing `DEFAULT` Statement

A database item with defaulting specified must have a `DEFAULT` statement.

Misuse of `DEFAULT` Statement

A DEFAULT statement is specified for a variable other than an input or database item.

Uninitialized Variable

The compiler detects that a variable is uninitialized when used. The compiler cannot do this in all cases. This error often occurs when you want to use a database item, but a database item is not available in the formula.

Missing Function Call

A function call is not recognized. The combination of return type, function name, and parameter types does not match any available function.

Incorrect Operator Usage

An instance of a formula operator use does not match the permitted uses of that operator.

For example, the + operator has two permitted uses. The operands are both of data type `NUMBER`, or both of data type `TEXT`.

Inconsistent Data Type Usage

A formula variable is being used as if it is of more than one data type. Or a database item or context is being used with the wrong data type.

For example, Variable A is assigned a `NUMBER` value at the start of the formula, but a `TEXT` value later in the formula.

`EXIT` Statement Not Within `WHILE` Loop

A condition that eventually becomes false, or an `EXIT` call for exiting the loop does not exist.

Misuse of Context

A variable is used as a context, or a context is used as a variable.

For example, `AREA1` is assigned a value as an ordinary variable, but later in the formula `AREA1` used as a context in a `GET_CONTEXT` call.

## Formula Execution Errors: Explained

Fast formula execution errors occur when a problem arises while a formula is running. The usual cause is a data problem, either in the formula or in the application database. These errors contain the formula line number where the error occurs.

### Formula Execution Errors

This table lists the type and description of each formula execution error.

Formula Error

Description

Uninitialized Variable

Where the formula compiler cannot fully determine if a variable or context is initialized when it is used, it generates code to test if the variable is initialized.

When the formula executes and the variable or context is not initialized an error is raised.

Divide by Zero

Raised when a numeric value is divided by zero.

No Data Found

Raised when a non-array type database item unexpectedly fails to return any data. If the database item can return no data then it should allow defaulting.

This error is also raised from within a formula function. The cause is an error in the formula function code.

Too Many Rows

Raised when a non-array type database item unexpectedly returns more than a single row of data. The cause is an incorrect assumption made about the data being accessed.

This error can also be raised from within a formula function. The cause is an error in the formula function code.

`NULL` Data Found

Raised when a database item unexpectedly returns a `NULL` data value. If the database item can return a `NULL` value then defaulting is allowed.

Value Exceeded Allowable Range

Raised for a variety of reasons, such as exceeding the maximum allowable length of a string.

Invalid Number

Raised when an attempt is made to convert a non numeric string to a number.

User Defined Function Error

Raised from within a formula function. The error message text is output as part of the formula error message.

External Function Call Error

A formula function returned an error, but did not provide any additional information to the formula code. The function might have output error information to the logging destination for the executing code.

Function Returned `NULL` Value

A formula function returned a `NULL` value.

Too Many Iterations

A single `WHILE` loop, or a combination of `WHILE` loops, has exceeded the maximum number of permitted iterations. The error is raised to terminate loops that could never end. This indicates a programming error within the formula.

Array Data Value Not Set

The formula attempted to access an array index that has no data value. This is an error in the formula code.

Invalid Type Parameter for `WSA_EXISTS`

An invalid data type was specified in the `WSA_EXISTS` call.

Incorrect Data Type For Stored Item

When retrieving an item using `WSA_GET`, the items actual data type does not match that of the stored item. This is an error within the calling formula.

The called formula could not be resolved when attempting to call a formula from a formula. This could be due to an error in the calling formula, or because of installation issues.

Recursive Formula Call

An attempt was made to call a formula from itself. The call could be directly or indirectly via another called formula. Recursive formula calling is not permitted.

Input Has Different Types In Called and Calling Formulas

When calling a formula from a formula, the actual formula input data type within the called formula does not match the data type specified from the calling formula.

Output Has Different Types In Called and Calling Formulas

When calling a formula from a formula, the actual formula output data type within the called formula does not match the data type specified from the calling formula.

Too Many Formula Calls

There are two many formula from formula calls. This is due to a problem with the formulas.

## FAQs for Writing Formulas

### When do I run the Compile Formula process?

If you need to compile many fast formulas at the same time, you can run the Compile Formula process on the Submit a Process or Report page. Also, if you make any changes to a function after you have compiled a formula that uses it, you need to recompile the formula for the changes to take effect.

### What's the difference between a formula compilation error and an execution error?

Compilation errors occur in the Manage Fast Formulas page when you compile the formula. An error message explains the nature of the error. Common compilation errors are syntax errors resulting from typing mistakes.

Execution errors occur when a problem arises while a formula is running. The usual cause is a data problem, either in the formula or in the application database.