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
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
 On the Payroll Calculation Tasks page, click Manage Fast Formulas to open the Manage Fast Formulas page.
 On the Manage Fast Formula page, click the Create icon to create a new formula.

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 Asof Date
1Jan2010
 Click Continue.

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'
 Click Compile.
 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
 On the Payroll Calculation Tasks page, click Manage Fast Formulas to open the Manage Fast Formulas page.
 On the Manage Fast Formula page, click the Create icon to create a new formula.

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
1Jan2010
 Click Continue.

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
 Click Compile.
 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 
Incorrect Statement Order 

Misuse of 
Occurs when any of these conditions occurs:

Misuse of 
An 
Missing 
A database item with defaulting specified must have
a 
Misuse of 
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 
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 

A condition that eventually becomes false, or an 
Misuse of Context 
A variable is used as a context, or a context is used as a variable. For example, 
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 nonarray 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 nonarray 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. 

Raised when a database item unexpectedly returns a 
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 
A formula function returned a 
Too Many Iterations 
A single 
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 
An invalid data type was specified in the 
Incorrect Data Type For Stored Item 
When retrieving an item using 
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.