Deposit formulas with parameters

The laboratory database provides a formula system for calculating parameters.

1. formula system: vector notation

Example: A = B + C and D = A * 2

The letters A-D represent the abbreviation of the respective parameter => v['Abbreviation']. This is defined in the parameter management in the second place under "Abbreviation" and is mandatory for the vector notation (see instruction: Parameter anlegen).

To map these two formulas in the laboratory database, enter at parameter A the formula v['B']+v['C'] (the vector notation v['B'] stands for the parameter B). The calculated result of a formula (in this case parameter A) can also be reused in a formula itself (e.g. in the formula D = v['A']*2).

In the following image you can see how the values for A and D (12 and 24) are automatically calculated from the values entered for B and C (8 and 4).

2. formula system: matrix notation

Up to 10 columns can be stored for one parameter. The matrix notation starts counting at 0, so this results in the columns 0 to column 9. With the matrix notation you can use the values of all columns in your formulas.

In the next image you see the parameter TS with four columns (Ergebnis, Schale, Einwaage, Auswaage).

Schale, Einwaage and Auswaage are entered and the result is calculated with the following formula: ((Auswaage - Schale) / (Einwaage - Schale)) * 100

If you want to use the value of column 0 (result) in a formula, you can use the vector notation v['TS'] or use the matrix notation m['TS']['0'] (the result is the same).

If you want to use the value of columns 1, 2,... (Schale, Einwaage,...) hen the matrix notation is to be used (m['TS']['1'] for Schale, m['TS']['2'] for Einwaage,...).

Example: Ergebnis = ((Auswaage - Schale) / (Einwaage - Schale)) * 100

In this example the result is standing in column 0, Schale in column 1, Einwaage in column 2 and Auswaage in column 3.

This results in the following notation for this formula: TS = ((m['TS']['3'] - m['TS']['1']) / (m['TS']['2'] - m['TS']['1'])) * 100

2a. Simplified formula system: parameter-internal calculations

If you only need to perform calculations within one parameter, the simplified notation with this(x) can be used. With this simplification, you can easily transfer the created formula to other parameters without having to adapt the matrix notation for each parameter.

this(x) with x= column number.

This results in the following notation for the above formula: TS = (this(3) - this(1)) / (this(2) - this(1))) * 100

2b. Simplified formula system: Calculations with target values

If you want to calculate with nominal values in your formulas, enter sollwert(x) with x= column number. This formula then has access to the target value of this parameter stored for the respective column x. (Instruction: How to Sollwerte hinzufügen.)

3. create formula

Formulas are created in the Edit Parameters mask.

In the image above, the formula is 100 - m['TS']['1'], i.e. 100 minus TS value of the 1st column.

In this example, if we enter the value 12 in the Eingabe column in the sample detail view, we automatically get the result 88 in the Report column (see image below).

4. Available functions in parameter formulas

Function	Description	Example
abs	Absolute value	`abs(-2) = 2`
avg	Average	`avg(3, 3, 6) = 4`
geomittel	Returns the geometric mean.	`geomittel(3, 3, 6) = 3,78`
median	Median	`median(3, 3, 6) = 3`
min	Determine minimum value	`min(1, 3, 5, 6, 7) = 1`
max	Determine maximum value	`max(1, 3, 5, 6, 7) = 7`
stabw	`stabw(number 1, number 2,...)` Determines the standard deviation	`stabw(1, 1.5, 2) = 0,50`
sum	`sum(number 1, number 2,...)` Adds the related arguments	`sum(1, 3, 5, 6, 7) = 22`
produkt	`produkt(Zahl 1, Zahl 2,...)` Multiplies the related arguments	`produkt(1, 3, 5, 6, 7) = 630`
potenz	`potenz(Basis, Exponent)`Exponentiates the related arguments	`potenz(2, 5) = 32`
sqrt	Square root	`sqrt(9) = 3`
exp	Exponential function	`exp(5.7) = 298.87`
log	natural logarithm	`log(12) = 2.4849`
log10	Logarithm to base 10	`log10(100) = 2`
if	`if(condition, then, else)` If the condition is TRUE, the then-part is returned. If not, the else part.	`if(2 > 3, 1, 2) = 2` `if(5 > 3 && 9 < 12, 'passed', 'failed') = passed`
ifs	`ifs(condition, then, condition, then, [...], else)` If the condition is TRUE, the then-part is returned. If not, it is checked, whether the next condition is TRUE and the next then-part is returned. If none of the conditions is TRUE, the else-part is returned.	`ifs( v['pH'] < 3, 'very acidic', v['pH'] < 6, 'acidic', v['pH'] < 8, 'neutral', 'alkaline')`
ifselected	`ifselected(parameter short, then, else)` If the parameter with this parameter abbreviation is selected for the sample, the then part is returned. If not, the else part.	`ifselected('pH', v['pH']*100, 'pH-Value missing')`
links	`links(Text, number of characters)`Cut character from left	`links('123-456', 3) = 123`
rechts	`rechts(Text, number of characters)` Cut character from right	`rechts('123-456', 3) = 456`
replace	`replace(text, old text, new text)` Replaces old text with new text in a string	`replace(123456, 3, '-') = 12-456`
enthaelt	`enthaelt(Text, Search term)`The function checks whether the search term is contained in the text.	`enthaelt('Hello World', 'World') = true`
beginntmit	`beginntmit(Text, Search term)` The function checks whether the text begins with the search term.	`beginntmit('Hello World', 'World') = false`
endetmit	`endetmit(Text, Search term)`The function checks whether the text ends with the search term.	`endetmit('Hello World', 'World') = true`
klein	`klein(Text)` Replaces upper case letters with lower case letters.	`klein('ABcd') = 'abcd'`
gross	`gross(Text)` Replaces lower case letters with upper case letters.	`gross('ABcd') = 'ABCD'`
verketten	`verketten(Text, Text,...)`Allows the concatenation of several texts or numbers	`verketten('abc ', 3) = abc 3`
laenge	`laenge(Text)` calculates how many characters occur in the text	`laenge('+++') = 3`
sin	Sine of the angle	`sin(60) = -0.3048...`
cos	Cosine of the angle	`cos(3.1415) = -1`
tan	Tangent of the angle	`tan(4) = 1.16`
asin	Arcsine of the parameter (the inverse function of `sin()`)	`asin(-1) = -1.571...`
acos	Arcus cosine of the parameter (the inverse function of `cos()`)	`acos(-0.5) = 2.094...`
atan	Arcus tangent of the parameter (the inverse function of `tan()`)	`atan(1) = 0.785...`
round	`round(Number, Number of digits)` Rounds a floating point value	`round(3.4) = 3` `round(1234.567, 2) = 1234.57` `round(1234.567, -2) = 1200`
aufrunden	`aufrunden(Number, Number of digits)` Round up a floating point value	`aufrunden(3.4) = 4` `aufrunden(1234.567, 2) = 1234.57` `aufrunden(1234.567, -2) = 1300`
abrunden	`abrunden(Number, Number of digits)` Round off a floating point value	`abrunden(3.4) = 3` `abrunden(1234.567, 2) = 1234.56` `abrunden(1234.567, -2) = 1200`
vrunden	`vrunden(number, multiple)` Returns a number rounded to the desired multiple.	`vrunden(10, 3) = 9` `vrunden(1.3, 0.2) = 1.4` `vrunden(12.34, 0.05) = 12,35`
roundsig	`roundsig(number, number of significant digits)` Rounds a number to the defined significant digits.	`roundsig(0.012345, 3) = 0,0123` `roundsig(12345, 3) = 12300`
wert	`wert(Text)` Transforms text into a decimal number.	`wert('< 0,25 mg/l') = 0.25`
fmod	`fmod(Dividende, Divisor)` Remainder of a floating point division	`fmod(5.7, 1.3) = 0.5` weil 4 * 1.3 + 0.5 = 5.7
rand	`rand(min, max)` Generates a random number	`rand(5, 15) = 11`
numberformat	`numberformat(number, decimal places, decimal point, thousands separator)` The number is displayed with the number of decimal places and decimal sperator.Optionally, a thousands separator can also be defined. Caution: The number is saved as a string. If you want to continue calculating with this number, the comma is not recognized as a decimal separator and must be changed by `replace` to a point in the calculation.	`numberformat(1995, 2, ',', '.') = 1.995,00`
exponentialformat	`exponentialformat(number, decimal places)` The number is displayed in exponential notation (Scientific Notation).	`exponentialformat(1995, 1) = 2,0 x 10³`
istzahl	`istzahl(Value)`This function checks whether the value is a number.	`istzahl(12.34) = TRUEistzahl(12,34) = TRUEistzahl('hello') = FALSE`
finden	`finden('searchtext',value)`This function finds a piece of text and returns the position. If nothing is found, an empty string '' is returned.	`finden('.', '1234.de') = 5finden('-',12-34) = 3finden('world', 'hello world') = 7`

The call sign ! provides the negation for all functions. It can thus be used, for example, for a query likeistzahl(Value), which outputs TRUE if it is a number, the negation!istzahl(Wert) can be used so that FALSE is output.

You will find further functions in the instructions Rechnen mit Zahlenreihen

5. operators for the if-function

Example: Warning at a pH value lower than 7 or bigger then 13:

if(v['pH'] < 7 || v['pH'] > 13, 'ph-value not OK', 'ph-value OK')

Here, two relational operators (less than 7, greater than 13) have been linked together or (||is the logical operator Oder).

Comparison operators

Example	Name	Result
`A == B`	Equal	Returns TRUE if A is equal to B.
`A != B`	Not equal	Returns TRUE if A isnot equal to B.
`A < B`	Smaller then	Returns TRUE if A is smaller then B.
`A > B`	Bigger then	Returns TRUE if A is bigger then B.
`A <= B`	Smaller or Equal	Returns TRUE if A is smaller or equal to B.
`A >= B`	Bigger or equal	Returns TRUE if A is bigger or equal to B

Logical operators

Example	Name	Result
`A && B`	AND	Returns TRUE if both A and B are TRUE.
`A \|\| B`	OR	Returns TRUE if both A or B are TRUE.
`A xor B`	Either Or	Returns TRUE if A or B is TRUE, but not both.
`!A`	Not	Returns TRUE if A is not TRUE.

Example: Nesting two if functions:

if(v['pH'] < 7, 'ph-value to low', if(v['pH'] > 13, 'pH-Value to high', 'pH-Value OK'))

Here, two if functions are nested inside each other. If the pH value is less than 7, 'pH value too low' is output.If the pH value is greater than or equal to 7, the second if function takes effect and checks whether the pH value is greater than 13. If yes, 'pH value too high' is output, if not, 'pH value OK'.

In this form, any number of if functions can be nested within each other.

Example: Nesting of three qualitative queries:

if(v['colour'] == 'red', 'The colour is red.', if(v['colour'] == 'yellow', 'The colour is yellow.',

if(v['colour'] == 'orange', 'The colour is orange.', 'The colour is undefined.'))

Last change: 02.09.2024

Allgemeines

Einführungsphase

Mitarbeiter

Aufträge

Proben

Probenvorlage

Berichte

Berichtstabellen Editor

Kunden

Kundenzone (optional)

Anlagen

Angebote

Rechnungen

Parameter

Rechnen mit Parametern

Schnittstellen

Webservice

Transformationscode

Prüfpläne / Grenzwerte / Spezifikationen

Dokumentenlenkung

Prüfmittel

Material

Mitarbeiterschulungen

8D-Report

8D-Report anlegen

Sonstiges

Barcode / QR-Code Scanner

PDF-Vorlagen

Fragen und Antworten

Lieferantenbewertung

Dateiverwaltung

Auswertungen

Prozesse

Prozessmanagement