Skip to main content

Formulas are used in a variety of ways in list management.

  • Convert formats (e.g. number to text or date to number)
  • List conditions (e.g. If then else, Case of)
  • Replace, delete or add characters (e.g. turn 67365 into postcode 6)
  • Calculate with the date or display only the day, month or year of a date
  • Select a specific contact person at an address or their telephone number
  • Select different languages for item texts (with multilingualism module)
  • Working with truth values (True, False)
  • Calculate or round with currencies
  • Create a new formula

    Formulas process the database references that are stored in the list. Before you start with the formula, the goal or the desired result of the list and formula must be clearly defined. It is best to create a sketch in advance.

    1. Add a new formula to the corresponding list.
    2. Enter a clear and descriptive name in the Heading column.
    3. Double-click to open the new formula.
    4. Specify the result data type of the formula. This determines whether the result is displayed as
      a) Picture (Picture)
      b) Date (Date)
      c) Text (string)
      d) Time (Time)
      e) Truth value (Boolean - > true, false)
      f) Number (longint- > integer; real - > number with decimal separator)
      is issued.
    5. Enter the formula in the input field (see formula catalogue) or use one of the ready-made functions (right).
    6. Insert the fields from the list that are used as formula operators into the formula.
      Note: The fields must be identical to the result data type or converted to the same format using functions (e.g. string). Text and number cannot be combined in a formula without harmonisation. The result, i.e. the expected data type, must match the result type of the formula.
      Example Convert number to text:
      String([number (number)];"###.##0,00")
      Converts the result of the "Number" field into the result type Text. The desired format mask can be specified with the second parameter (here e.g. 2,500.00)
    7. Select Apply results of individual fields from the dropdown box for lists that are processed statistically so that the formula with the contained references takes effect before the summary.
    8. Check the formula.
  • Use formula operators

    Operators are used to process or combine formula contents.

    OperatorDescription

    +

    		 Combines text or adds numerical values ("Text1" + "Text2")

    -, *, /, +

    		 For the calculation of numerical values

    =

    		 Is equal for the matching of conditions (x = y)

    #

    		 Not equal for the reverse comparison of conditions (x # y)

    >

    		 Greater than... "x > y" (greater than or equal to: > =)

    <

    		 Less than... "x < y" (less than or equal to: <=)

    ;

    		 Semicolon for separating parameters

    " "

    		 Apostrophe before and after entered text ("abcde")

    ( )

    		 Brackets for structuring the components of a formula

    :

    		 Ternary operator (separator between then and else parameters)

    ?

    		 Question mark before and after entered times (?00:00:00?) or ternary operator (condition)

    !

    		 Exclamation mark before and after an entered date (!00.00.0000!)

    { }

    		 Indicates placeholders in service functions (placeholders do not necessarily have to be filled)

    Char(13)

    		 Sets a carriage return

    Char(9)

    		 Sets a tab