Cloud Documentation
Advanced Search


Using Fast Formula
Close Window

 

This guide also applies to on-premise implementations

Table of Contents

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.

Called Formula Not Found

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.