Formulas and rules
Column values can be calculated using formulas and rules. To define rules and formulas, right-click the column name in edit mode to open the shortcut menu. You can use the shortcut menu to define a value for the whole column or to define formulas and rules. This feature is available for productionManager Advanced.
Opening the context menu
Add rule
When creating a rule, start by defining a condition (1). To do so, select a column name, then specify an operator - such as equal, unequal, greater (or equal), or less (or equal) - and enter the desired value. Then specify which values should be assigned to the column if the condition applies (2). You can add further conditions to a rule using "+ And" (3). Additional rules can be added via "+ Add rule" (4). In the "Otherwise" field (5), enter the value to be used if none of the defined rules apply. If you want to remove a rule, simply click on the red cross (6) next to it.
For example, you can use a rule to define the label layout of parts.
Add new rule
Add formula
With the formula and rule editor integrated in productionManager, you can set formulas and rules to automatically calculate the values of columns, check conditions or process texts. In the dialog for formula creation, select the required columns for the calculation from the list of "Available columns" and add them to the formula with a double click. Click "Confirm" to save the formula which will then be applied to the corresponding column.
Formulas offer a wide range of applications. Below, the options for creating formulas are explained in detail, each accompanied by examples.
Note: In general, formulas distinguish between numbers and strings as data types. Numbers must not be entered with thousands separators, as a dot is used as the decimal seperator. Strings are indicated by " ".
Functions
Different functions can be used in formulas. These are typically identified by () following the function name, which are used to pass parameters for evaluation. Multiple parameters must be seperated by a semicolon ( ; ) or comma ( , ).
Note: You can nest and combine functions as often as you wish, as long as you follow the syntactic rules. Spaces and line breaks are not taken into account when functions are calculated.
Mathematical functions
The standard mathematical basic and comparison functions are available for processing numbers.
| Basic functions | +, -, *, / |
| Comparison functions | Less than: < Note: Comparison functions return either 0 for a false or 1 for a true statement. All comparison operators can also be applied to strings. |
Example:
An example for the use of basic functions is the calculation of the finished dimensions of a part. If it has not already been provided by an imported bill of materials, it can simply be calculated from the dimensions of the board when it has been cut plus the thickness of its edges. For example, the finished widht can be calculated using the following formula:
Finished width = [width] + [edge thickness right] + [edge thickness left]
Example: formula for finished width
Logical functions
Besides the mathematical functions the following logical functions are available:
| AND | Both conditions must be satisfied |
| OR | At least one of the two conditions must be satisfied |
| XOR | Exactly one of the two conditions must be satisfied |
| NOT | Negates the argument |
String functions
In addition to the comparison operators, the following functions are available for character strings.
| Function | Description | Example |
|---|---|---|
| & | Concatenation of two strings | "HOMAG" & " Maschine" = "HOMAG Maschine" |
| UCASE("String") | Conversion of all lowercase letters to uppercase letters | UCASE("machine") = "MACHINE" |
| LCASE("String") | Conversion of all uppercase letters to lowercase letters | LCASE("HOMAG") = "homag" |
| LEFT("String"; Number) | Specified number of characters from the left | LEFT("Holzhaus"; 4) = "Holz" |
| RIGHT("String"; Number) | Specified number of characters from the right | RIGHT("Holzhaus"; 4) = "haus" |
| MID("String"; Number; Number) | Substring with start position and optional end position | MID("HOMAG wood working systems"; 6; 4) = "wood" |
| LEN("String") | Length of the string | LEN("HOMAG") = 5 |
| ISEMPTY("String") | 1 if the string is empty, otherwise 0 | ISEMPTY("") = 1 |
| FIND("String1"; "String2"; Number) | Searches the string for the occurence of another string. Optionally, a start position can be specified to skip characters. | FIND("HOMAG wood working systems"; "wood";) = 6 |
| RFIND("String1"; "String2"; Number) | Similar to FIND(), however the search is performed from end to beginning | |
| REPLACE("String"; "String old"; "String new") | Searches for and replaces all instances of the old string with a new string within the specified text | REPLACE("HOMAG wood working systems"; "systems"; "machines") = "HOMAG wood working machines" |
| INSERT("String"; Number; "String new") | Inserts the new string into the existing string at the desired position | INSERT("HOMAG ", 6, "machine") = "HOMAG machine" |
| LTRIM("String") | Removes all spaces at the beginning of the string | LTRIM(" HOMAG") = "HOMAG" |
| RTRIM("String") | Removes all spaces at the end of the string | RTRIM("HOMAG ") = "HOMAG" |
Note: Upper and lower case characters are generally distinguished.
Conditional expressions
The formula edior supports two constructs for creating conditional expressions: the IF-THEN-ELSE construct and the SWITCH-CASE-DEFAULT construct.
Both options allow, to varying degrees, the execution of a specific action depending on the value of a column.
The IF-THEN-ELSE construct
The IF-THEN-ELSE construct examines a condition and, depending on whether it is satisfied or not, executes one of two alternative options.
IF condition THEN command ELSE alternative command
If the condition is met (value unequal to 0), the value of the command expression is returned. Otherwise, the value of the alternative command expression is returned.
Note: Simple IF-THEN-ELSE constructs can more easily be implemented using rules. Only if the expressions to be executed themselves contain nested expressions, it is not possible to use a rule.
Example changing the alignment of parts:
IF [Width]>[Length] AND [Grain]="NoGrain" THEN [Length] ELSE [Width]
Example: formula for changing the alignment of parts
Example additional notes depending on the materials' properties:
IF ([Thickness] <= 16 AND LEFT([Material];2) = "P2" AND [Length]>800) THEN "Additional bottom beam" ELSE ""
Example: formula for additional notes depending on the materials' properties
Example file path for setting up the scanner on the CNC machine:
IF [CNC program 1] <> "" THEN "\\HOMAG\CENTATEQ\"&[CNC program 1] ELSE ""
Example: formula for file path
The SWITCH-CASE-DEFAULT construct
The SWITCH-CASE-DEFAULT construct allows you to compare a reference against multiple possible cases and execute different commands depending on the value. When there are several comparisons, this construct is clearer than using multiple nested IF-THEN-ELSE statements.
SWITCH reference
CASE comparatorX THEN commandX
CASE comparatorA CASE comparatorB THEN commandA
DEFAULT else
The construct sequentially compares the value of the selected reference column with all declared comparison values. When a match is found, the corresponding action is executed. This allows you to conveniently compare the same value against multiple cases without having to specify it multiple times.
If the reference paramter is to be compared with an entire range of values, intervals can follow the keyword CASE. An interval is specified as follows: {start ... end}.
Note: The types, e.g., number or string of the comparison values must be identical to that of the reference value, while the types of the return values can be chosen freely. A string reference can therefore only be compared to other strings not to numbers.
Example label layout:
SWITCH [Customer name]
CASE "Smith" THEN "Label_Smith"
CASE "Miller" THEN "Label_Miller"
CASE "Nelson" THEN "Label_Nelson"
DEFAULT "Standard_Label"
Example: formula to define the label layout
Example determination of the production route
SWITCH [Article group]
CASE "Lid" CASE "Shelve" CASE "Carcass side"
THEN "SAWTEQ-EDGETEQ-DRILLTEQ-Assembly"
CASE "Back Panel"
THEN "SAWTEQ-Assembly"
CASE "Front"
THEN "SAWTEQ-EDGETEQ-CENTATEQ-Assembly"
CASE "Front varnished"
THEN "SAWTEQ-EDGETEQ-DRILLTEQ-Surface-Assembly"
DEFAULT "SAWTEQ-EDGETEQ-DRILLTEQ-Assembly"
Example: definition of the production route through a formula
Note: Conditional expressions may be nested and combined as often as desired, provided the syntactical rules are followed. This means, for example, that an IF-THEN-ELSE construct may itself contain another such construct or logical operators.
Conversion and special functions
| Function | Description | Example |
|---|---|---|
| STR(expression) | Converts the result of the expression into a string. | STR(123) = "123" STR(4+5) = "9" |
| VAL(expression) | Converts the result of the expression into a number. If the string does not begin with a digit, sign or decimal seperator, 0 is returned. Note: The evaluation of the string is terminated as soon as an invalid character is encountered. | VAL("123") = 123 VAL("-0.5") = -0.5 VAL("Text") = 0 |
| VARDEF(variable name) | Checks, whether the variable name is defined. If it is defined, 1 is returne, otherwise 0. | X=100 VARDEF("X") = 1 VARDEF("Y") = 0 |
STR() and VAL() accept both strings and numbers as parameters, allowing the result of the function, which may contain different types- to be used with type safety.
Detailed technical explanations on how to create formulas can be downloaded from the top right of this page.