Formula Builder

Formula Builder

Formula Builder helps you create, modify and test formulas to calculate salaries.  Here are more detailed instructions for building formulas when calculating different earning types .

First open the Open Earnings Types window in Settings l Personnel l Payroll l Earnings Types. Then click the button (income tax relief formula, payroll tax relief formula contributions relief formula).

You can use the Formula Builder window to create formulas in an easy and straight-forward manner.

 1. The box for entering formula

Here you can enter formulas in two ways:

• without specifying an SQL expression - It is the classical approach for creating formulas.

HINT For detailed treatment see: Create Formula for Meals Allowance in Formula Builder

• with SQL expression

HINT For detailed treatment see: Create Formula Using an SQL Expression in Formula Builder

WARNING By entering SQL expressions you should observe the following: SQL expression begins and ends with a delimiter ~S~ a formula can contain any number of SQL expressions an SQL expression cannot contain logical conditions ([..:..]..) an SQL expression cannot contain temp tables with only one # an SQL expression can contain variables (# ... #) #) Logical conditions can contain SQL expressions.

You can resize the window anyway you want. Place the pointer on the border line (see above), left-click and hold. Then move up (to make the window smaller) or down (to expand the window). If you click the line, the formula box is displayed in the entire window. Click it again to roll back.

2. Variables

Variables available for salary formulas are divided into:

• Program parameters
• Average salaries
• Administration Panel
• Payroll preparation
• Variables for contributions
• Single employee
• Employment record
• Position
• Grade
• Periodic rating
• Factor net
• Cost centers
• GE variables (only available in the GE edition)
• Subjects
• Finished work analysis (only available in the MF edition)
• Fixed Assets
• Rounding
• Date variables

3. Parameters

Besides numbers you can use the following operands:

Operand	Description
/	Divide by
*	Multiply by
-	Subtract from
+	Add to
C	Delete
\	Integer division -  You can also enter operand by using the shortcut Alt Gr + Q.
|	Remainder in integer division -  You can also enter operand by using the shortcut Alt Gr + W.
( )	Round brackets - Use them to define the precedence of operations. There must always be an equal number of opening and closing round brackets. Otherwise an error is returned (for more information see Opening or closing round bracket missing!).
#	You must put a hash sign in front and after each variable.
>	Greater than - used in conditional statements.
<	Less than - used in conditional statements.
=	Equal to - used in conditional statements.
	[ ]  Use square brackets in conditional statements for "If Than": Use then in conditional statements when for example Condition 1 is fulfilled than Value 1 otherwise use Else. Square brackets - Use them to define the precedence of operations. There must always be an equal number of opening and closing round brackets. Otherwise an error is returned (for more information see Opening or closing square bracket missing!). For more information see Using Conditional Statements in Formulas).
	Let us take a look at the Round function (;). Use this variable to set number of decimal places for rounding.

An example of using a round function for comparing two rounded calculations:

HINT For detailed treatment of using Round see: Rounding

4. Pay Slip

The Pay Slip field and the Use in Formula button appear only with variables used for referencing pay slip previous periods.

For example, to reference variable Del11 in pay slip number 09-P00-000006 enter it as follows: #Del11-09-P00-000006# (the Net variable - and select a pay slip from the Pay Slip). Finally, click the Use in Formula button.  

HINT For detailed treatment see: Referencing Variables in Pay Slip from Previous Period

5. Contribution

To make the Contribution field and the Use in Formula button available, select Variables for Contributions on the right.

HINT For detailed treatment see: Referencing Variables for Contributions

6. Payroll Preparation Variables

To make the Earnings Type, Field and Varaible fields available, select Payroll preparation.

HINT For detailed treatment see: Create Formula for Meals Allowance in Formula Builder

The bottom section displays descriptions of variables used in a formula.

Earnings Type	Enter or select an earnings type for payroll.
Variable	Enter or select a variable to reference fields for payroll. The following are available: Hours Standardized Hours Value (gross value) Factor Net Base Credit Employee Contributions Employer Contributions Days
Field	Enter or select a value to reference fields for payroll. The following are available: U - Hours R - Standardized Hours V - Value (gross value) F - Factor N - Net O - Base K - Credit PD - Employee Contributions PP - Employer Contributions D - Days
Interval	Select to repeat the formula calculation for all the pay slips.  When selected, the Pay Slip is greyed out.
Period	Check and choose the period.  If current period should be used, select 00, previous month 01, etc.  When selecting the current period (00), the Period field is automatically enabled.
	To include variable in the formula click the Use in Formula or the  button above.

Warning When selecting variables, the Field lookup becomes inactive and is greyed out. On the other hand, when selecting values in the Field lookup, the Variable field becomes inactive and is greyed out. That is, you cannot set both fields.

HINT For current Period to be used select 00 from drop down list.   Note that, only finished pay slips for this period will be taken into account.  By selecting the current period, the Period field gets automatically enabled.

 7. Test

Click the Test tab or button to open the corresponding panel.

The value and test result are displayed in the bottom section.

WARNING You can only test formulas which do not include SQL expressions.  You cannot test or validate an SQL expression using Formula Builder.