Calculated column

The number will be used to refer to the calculated column in the layout tables, and must be in the interval 1 - 1 000 000.

0 - Float (Quantity)

Floating-point values are shown with floating decimals by default. If you require fixed decimals, the number of decimals can be specified with the Format command (in the Column group in the Design tab).

1 - Date

When using date fields in a formula, they represent the number of days since December 31st, 1979.

2 - Time

When using time fields in a formula, they represent the number of minutes since midnight.

3 - String

Selected when the output from the formula is a string value.

Example: @Order (Your ref.)<>""

The formulas may include:

• Macro functions (with the prefix @),
• Numbers (integers or floating point numbers),
• Arithmetic operators (+, -, *, / and %),
• Relational operators (=, >, <, >=, <= and <>),
• Logical operators (!, && and ||),
• Conditions and partial expressions (?, :),
• Brackets (in an unlimited number of levels) and
• Tilde (~) for rounding.

Macro functions

The formula editor is initiated by typing the * character in, or double-clicking on, a table cell or form field. In the formula editor that opens, you can enter a formula by typing the @ symbol. A list of available macro functions is displayed.

Select the macro function you want to use, and using the macro parameter editor, you can select macro function parameters interactively. For example, if you select @FreeText(), a macro list of free text entries is displayed to choose from.

• @Column takes a field name as parameter, and can be chosen from the Field name dialog box that pops up if you double click on the macro, or select the macro and click the Field button. In this dialog box, you can choose among all columns in the table, and its underlaying tables. Several fields can be selected at the same time.
• @Date () and @Time () represent the current date and time.
• @SystemInteger, @SystemDecimal and @SystemText represent system dependent variables, and @CompanyInteger, @CompanyDecimal and @CompanyText company dependent variables. They all take a variable name as parameter, and optionally an element number. Element numbers should be in the interval 1 - 12 for integer and decimal variables, and 1 - 2 for text variables. Element numbers default to 1 if they are omitted. The Namespace for the variables are stated in a separate column in the table.

When you choose macro functions via lookup, they will be added to the end of the field (after a space if the field contains anything from before), instead of replacing the old content in the field. The field pointer will remain in the field, ready for editing.

Here are some examples of calculations with macros:

Calculated column

• @Column (Free 1) * @Column (Entered amount)

This field will show the value in the Free 1 field multiplied by the value in the Entered amount field. The field will be blank if nothing is entered in the Free 1 field.

Factors from general ledger

• @Column (Entered amount) * @Column (General ledger account [Account no.->Account no.] (Gr. 1))

This field will show a multiplication between the value in the Entered amount field and the value in the Gr. 1 field from the General ledger account table.

Calculated column 2

• @Column (Free 1) <> 0?@Column (Free 1) * @Column (Entered amount):1

If the value in the Free 1 field is different from 0, this field will show the value in the Free 1 field multiplied by the value in the Entered amount field.

If the value in the Free 1 field is 0, this field will show the value 1.

The ? following the first expression states the value to show if the expression is true; @Column (Free 1) * @Column (Entered amount). The : states the value to show if the expression is false; value 1.

Calculated column 3

• !@Column (Free 1) = 0?@Column (Free 1) * @Column (Entered amount):1

If the value in the Free 1 field is different from 0, this field will show the value in the Free 1 field multiplied by the value in the Entered amount field.

If the value in the Free 1 field is 0, this field will show the value 1.

The expression starts with !, which means NOT IDENTICAL. The ? following the first expression states the value to show if the expression is true; @Column (Free 1) * @Column (Entered amount). The : states the value to show if the expression is false; value 1.

This gives the same result as Calculated column 2.

Text fields act like a normal text field allowing to type, select text ranges and copy/paste text, while most of the formulas are editable as free text, macro functions are being displayed as tags and can’t be edited as free text. They are focused when moving the cursor with arrow keys or mouse clicks. When a macro function is focused, the macro editor is displayed.

Expression

When you use relational operators and/or logical operators, the expressions will return with 1 if true, or 0 if false.

Evaluation order

Parentheses will be evaluated first, and the operators will have the following priority:

1. Multiplicative operators (*, / and %)
2. Additive operators (+ and -)
3. Rounding (~)
4. Relational operators (=, >, <, >=, <= and <>)
5. Negation (!)
6. Conjunction (&&)
7. Disjunction (||)
8. Conditions and partial expressions (? and :)

Operators at the same level are evaluated from left to right.

Rounding the results inside the formula can be included with a tilde character (~) and a number. For example, ~1 would round the result to one decimal. Add brackets around the value and rounding parameters. Example: (10,55~1) = 10,60

The logical NOT operator (!) evaluates only the expression it is directly connected to, which means that a correct use of parentheses is important.

Examples

You have product no. HOURS - when this product is used on an order line the value in the Empl. no. field should be shown, otherwise not.

Valid use of ! in the formula (parentheses enclosing the whole expression to be evaluated):

• !(@Column (Product no.)="HOURS") ? 0 : @Column (Empl. no.)

Alternative formulas that gives the same result:

• @Column (Product no.)<>"HOURS" ? 0 : @Column (Empl. no.)
• @Column (Product no.)="HOURS" ? @Column (Empl. no.) : 0

The following formulas with ! won’t work (since the !@Column (Product no.) part will be evaluated before the =):

• !@Column (Product no.)="HOURS" ? 0 : @Column (Empl. no.)
• (!@Column (Product no.)="HOURS") ? 0 : @Column (Empl. no.)

Relevant error messages

In case you enter an incorrect formula or expression, the error is displayed below the text field.

The name can be seen in the Manage columns dialog box for the Unhide (Ctrl+U) command (in the table menu under View) for the table stated in the Table no. field. The name is used as the column heading, and can be overridden in Visible columns list.

You can select a table by looking up in this field.