Skip to main content

Formulas are used in a variety of ways in lists and reports.

  • 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 account or their telephone number
  • Select different languages for item texts (with the Translations module)
  • Apply truth values (True, False)
  • Calculate with or round currencies
  • Create a formula

    Formulas process the database references that are stored in the list. Before you start building a formula, make sure to define the goal or intended use of the list and formula very clearly. It is best to draw up a sketch in advance.

    1. Add a new formula to a list.
    2. Enter a unique and speaking name in the Header column.
    3. Double-click to open the new formula.
    4. Specify the result data type of the formula. This determines whether the result will be shown as
      a) Picture (Picture)
      b) Date (Date)
      c) Text (String)
      d) Time (Time)
      e) Boolean value (Truth value -> true, false)
      f) Number (longint-> integer; real -> number with decimal separator)
      .
    5. Enter the formula in the input field (see formula catalogue) or use one of the ready-made functions (right).
    6. Insert the list fields 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 conversion. The result, i.e. the expected data type, must match the result type of the formula.
      Example Convert number to text:
      String([Count (number)];"###.##0,00")
      Converts the result of the "Number" field into the result type Text. A format mask can be specified using the second parameter (here e.g. 2,500.00)
    7. Select Apply to single data rows from the dropdown box below the input window for lists that are processed statistically. This means the formula takes effect before summarising.
    8. Check the formula.
  • Use helpful 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 matching of conditions (x = y)

    #

    		 Is not equal - for reverse comparison of conditions (x # y)

    >

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

    <

    		 Is smaller than... "x < y" (smaller 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)

    		 Carriage return sets breaks

    Char(9)

    		 Sets a tab