You are here: Home / Extras / Formulas and calculating / Use for contacts and property

Formulas and calculating

Use for contacts and property

Arithmetic (Math)

A calculation formula always begins with an equal sign “=”, as many people know from Excel. If you want to use the value from a field for the calculation, you must specify the field name in square brackets.
Please note that if you work with decimal numbers in the Formula Builder, the comma “,” must be written as a period “.
Here is an example:=[kaltmiete]+[nebenkosten]+[ind_1078_Feld_ObjPreise33]+3.57

The field name is not the one you see in the software, but the entry in the Field column. The elongated name “ind…” is the name of a field you have created yourself. You can specify formulas for system fields and for fields you create yourself.

There are the basic arithmetic operations (+ – * /) and the dot before the dash. As a special case, thereis_calculate.
You can use all number fields and also checkbox fields for the calculation. These are fields of type Decimal, Float, Integer, Int, TINYINT and Boolean.

_calculate

The formula element _calculate is a macro. The _calculate macro can be used to calculate values using field values. A formula of the formula logic is used as parameter for this.
Formula structure: _calculate(text(DATEADD(today();10;"d");"DD.MM.YYYY"))
Example (kaufpreis == purchase price): _calculate([kaufpreis]*3) or _calculate(IF([kaufpreis]<1000000;[kaufpreis]*3;[kaufpreis]*4))

The formulas are a practical thing that you can also use in macros, especially the “if macros”. To do this, you must also include the formula or field value with the macro _calculate().

Example:
_if(_calculate[kaufpreis]<1000000;”_kaufpreis”; “Price on request”)

The _calculate macro outputs the values in English formatting, with a dot as decimal separator. To output the values in German formatting with a comma as a decimal separator, use the FIXED parameter. FIXED can also be used to determine the number of decimal places.

Example (kaufpreis == purchase price):

_calculate([kaufpreis]) -> 233333.00
_calculate(FIXED([kaufpreis])) -> 233.333,00
_calculate(FIXED([kaufpreis];0)) -> 233.333

Function

Using calculation functions, you can, for example, calculate an average value from several number fields or shorten text fields. There are currently four different areas which can be edited using functions. These are:

  1. Text functions
  2. Numerical functions
  3. Date functions
  4. Logic functions

To create a formula function, you have to select the corresponding module and category under Tools >> Settings >> Administration >> Input Fields and create a new individual field in which you can store a formula function.

1. Text functions

Text functions allow you to shorten text fields or search for specific words, strings or similar. In total there are 7 text functions and a new text operator“&” by which text fields can be connected. In the following you will find the respective formulas with examples. All examples refer to the field “property title” with the content “flat greened property”.

  • LENGTH :
    The LENGTH formula allows you to display the length of the contents of a text field. The result is output as an integer.
    Formula structure: LENGTH([Feldname])
    Example: LENGTH([objekttitel]) The length of the property title is displayed.
    Result: 21
  • LEFT :
    The formula LEFT allows you to output a certain number of characters from the left. This takes place in text form.
    Formula structure:LEFT([Feldname];Anzahl Zeichen)
    Example: LEFT([objekttitel];5) The first 5 characters of the property title will be displayed.
    Result: flat
  • RIGHT :
    The formula RIGHT is the opposite of the formula LEFT. The mode of operation is the same.
    Formula structure: RIGHT([Feldname];Anzahl Zeichen)
    Example: RIGHT([objekttitel];10) It will output 10 characters from the right.
    Result: d property
  • MID :
    The formula MID allows you to output text from the center.
    Formula structure: MID([Feldname];Erstes Zeichen;Anzahl Zeichen)
    Example: MID([objekttitel];7;9) Starting with the 7th character, the next 9 characters are output.
    Result: eened pro
  • SEARCH :
    With SEARCH you can search for terms, strings and spaces in a text field.
    Formula structure: SEARCH("Suchbegriff";[Feldname];Optional: Erstes Zeichen)
    Example: SEARCH(" ";[objekttitel]) The first space in the property title is searched for.
    Result: 5
    Example: SEARCH(" ";[objekttitel];7) Starting with the 7th character, the next space is searched for.
    Result: 16.
  • SEARCH_MULTISELECT :
    You can use SEARCH_MULTISELECT to search a multiselect field for values.
    Formula structure: SEARCH_MULTISELECT("Suchbegriff";[Feldname des Multiselectfeldes])
    Example: SEARCH_MULTISELECT("lasten";[fahrstuhl])
    The system searches for the multiselect value “load”, i.e. freight elevator, in the multiselect field “elevator”.
    Result:  0, if “search term” was not found (i.e. = value is not included) On the graphical user interface, depending on the data type of the field, the 0 is not displayed but the field remains empty.
    Result: 0, if no multiselect field was specified for [field name of multiselect field]. On the graphical user interface, depending on the data type of the field, the 0 is not displayed but the field remains empty.
    Result: 1, if “search term” was found (i.e. “value is contained”)
  • TRIM :
    With TRIM you can “trim away” spaces before and after the text. Since in onOffice enterprise all text fields are trimmed from the beginning, the example consists of a compound formula function.
    Formula structure: TRIM([field name])
    Example: TRIM(MID([objekttitel];6;11)) First, the MID function is used to cut out ” greened ” from the property title and TRIM is used to remove the before and after spaces.
    Result: “greened”
  • TEXT :
    With the TEXT function you can, for example, output a date field as text. The example uses the date field [erstellt_am]. This field is created when the property is created and cannot be changed. Example 19.02.2017
    Formula structure: TEXT([field name];”text”)
    Example: TEXT([erstellt_am];"DD/MM/YY") This formula displays the date field in a different notation, based on the _Datummacro.
    Result: 19/02/17
  • Operator “&” :
    The operator “&” can be used to connect several text fields or number fields with each other, for example.
    Formula structure: [Feldname]&[Feldname] oder [Feldname]&"Text"
    Example: [objekttitel]&" "&[objekttitel] outputs the property title twice in a row, separated by a space.
    Result: flat greened property flat greened property

2. Numbers Functions

By means of number functions it is possible to calculate an average value from several number fields or to round numerical values. There are 5 number functions available. In the following you will find the respective formula with examples. All examples refer to the field “Purchase price” with the value 162,990.35. For the calculation of the average value, the field “Market value” with the value 170,000.00 is added.

  • ROUNDUP :
    A value can be rounded up or down using the ROUNDUP formula.
    Formula structure: ROUNDUP([field name];number of decimal places, which are rounded)
    Example: ROUNDUP([kaufpreis];1) The purchase price shall be rounded up to one decimal place.
    Result: 162,990.40.
  • ROUNDDOWN :
    Number fields can be rounded off with the ROUNDDOWN formula.
    Formula structure: ROUNDDOWN([Feldname];Anzahl Nachkommastellen, welche gerundet werden)
    Example: ROUNDDOWN([kaufpreis];1) The purchase price is rounded down to the first decimal place.
    Result: 162,990.30.
  • ROUND :
    The ROUND formula is used for commercial rounding.
    Formula structure: ROUND([Feldname];Anzahl der zu rundenden Nachkommastellen)
    Example: ROUND([kaufpreis];1) It is rounded to one decimal place.
    Result: 162,990.40.
  • FIXED :
    Using the formula FIXED, you can shorten numbers by decimal places or output more decimal places. Please note that the FIXED formula can only be used in a text field and it is therefore not possible to continue calculating with the result. FIXED also outputs the value in German formatting with a comma as a decimal separator.
    Formula structure: FIXED([Feldname];Anzahl der Nachkommastellen welche ausgegeben werden sollen)
    Example: FIXED([kaufpreis];4) A total of 4 decimal places are output.
    Result: 162.990,3500
    Example: FIXED([kaufpreis];0) No decimal places are output from the purchase price.
    Result: 162,990.
  • AVERAGE :
    AVERAGE allows you to average two or more number fields.
    Formula structure: AVERAGE([Feldname];[Feldname2])
    Example: AVERAGE([kaufpreis];[verkehrswert]) The average value of the two numerical fields purchase price and market value is determined.
    Result: 166.495.18
    Result calculator: 166,495.175.
  • SUM :
    You can use the formula SUM to calculate the sum of several individual fields.
    Formula structure: SUM([Feldname1];[Feldname2];[Feldname3])
    Example: SUM([kaufpreis];[verkehrswert])
    Result: 332,990.35.
  • MIN :
    Using the formula MIN, the minimum value can be determined from a pool of values.
    Formula structure: MIN([Feldname1];[Fendname2];[Feldname3]) the lowest value from the 3 fields is output.
    Example: MIN([kaufpreis];[verkehrswert]) The minimum purchase price and market value of the property is calculated.
    Result: 162,990.35.
  • MAX :
    By using the formula MAX the maximum value can be determined from a pool of values.
    Formula structure: MAX([Feldname1];[Feldname2];[Feldname3]) the highest value from the 3 fields is output.
    Example: MAX([kaufpreis];[verkehrswert])
    Result: 170,000.00.
  • POWER:
    The POWER function allows potencies to be calculated.
    Formula structure: POWER([Feldname1];[Feldname2]) o. POWER(Zahl1;Zahl2) The first parameter gives the base, the second parameter gives the exponent.
    Example: POWER(5;2)
    Result: 25.
  • LOG:
    Logarithms can be calculated using the LOG function.
    Formula structure: LOG([Feldname1];[Feldname2]) oder LOG(Zahl;Basis). The first parameter specifies the logarithmand, the second parameter the base. The second parameter base is optional.LOG(number) without a base calculates the natural logarithm.
    Example: LOG(64;2)
    Result: 6.

3. Date Functions

Date fields are another application area of the formula functions. Here you can display the time between 2 dates or add a defined number of days, months or years to a date. A total of 5 different formulas can be used. For all examples the field “created on” with the value 09.02.2017 is used. For the examples in which the time between 2 dates is calculated, the field “Order to” with the value 01.03.2019 is included. Today is the 01.03.2017.

Please note that the date functions are always output in YYYY-MM-DD format and can only calculate in this format. A different display is possible using the TEXT function. Example: _calculate(text(DATEADD(today();10;”d”);”DD.MM.YYYY”)

  • TODAY :
    The TODAY formula can be used to output today’s date.
    Formula structure: TODAY()
    Example: TODAY() Today’s date is displayed.
    Result: 2021-06-24
  • NOW:
    The NOW formula can be used to output today’s date with time.
    Formula structure: NOW()
    Example: NOW() Today’s date with time is output.
    Result: 6/24/2021 12:07:40 PM
  • WEEKDAY :
    The formula WEEKDAY can be used to output the weekday of a date field.
    Formula structure: WEEKDAY([Feldname])
    Example: WEEKDAY([erstellt_am]) The weekday of the “created on” field is displayed in the field.
    Result: Thursday
  • MONTH :
    The formula MONTH displays the month of the field to be calculated.
    Formula structure: MONTH([Feldname])
    Example: MONTH([erstellt_am]) The month of the field “created on” is displayed in the field.
    Result: February
  • DATEADD :
    The DATEADD formula allows you to add a number of days, months or years to a date field using a parameter (d,m or y).
    Formula structure: DATEADD([Feldname];Anzahl;"Parameter")
    Example: DATEADD([erstellt_am];13;"d") 13 days are added to the date “created on”.
    Result: 22.02.2017
    Example: DATEADD([erstellt_am];2;"m") The date “created on” plus 2 months.
    Result: 09.04.2017
    Example: DATEADD([erstellt_am];1;"y") Add one year.
    Result: 09.02.2018
  • DATEDIF :
    With DATEDIF you can output the time between two date fields in days(d), months(m) or years(y).
    Formula structure: DATEDIF([Feldname];[Feldname2];"Parameter")
    Example: DATEDIF([erstellt_am];[auftragbis];"d") The time between “created on” and “job to” is displayed in days.
    Result: 750
    Example: DATEDIF([erstellt_am];[auftragbis];"m") The months between the two dates are displayed.
    Result: 24
    Example: DATEDIF([erstellt_am];[auftragbis];"y") The years between the two dates should be displayed.
    Result: 2.
  • TEXT :
    With the TEXT function you can, for example, output a date field as text. The example uses the date field [erstellt_am]. This field is created when the property is created and cannot be changed. Example 19.02.2017
    Formula structure: TEXT([field name];”text”)
    Example: TEXT([erstellt_am];"DD/MM/YY") This formula displays the date field in a different notation, based on the _Datummacro.
    Result: 19/02/17

4. Logic Functions

IF Logic

It is also possible to include IF queries. All fields (including text fields or key fields) can be used as conditions. The syntax is based on the Excel function “If()”: IF(Bedingung wahr;Dann;Sonst). The value of the field in which the formula is entered is filled with the contents of the Then or Otherwise condition.
Here is an example: IF([Feldname1]>0;[Feldname2]+[Feldname3];[Feldname4]+[Feldname8]).

To check key fields (single-select or multi-select fields), the selection of the key field in the condition must be checked.
An example of single-select fields: IF([Schlüsselfeld]="Feld";Dann;Sonst).
An example multiselect fields: IF(SEARCH_MULTISELECT("Suchbegriff";[Feldname des Multiselectfeldes])=1;Dann;Sonst).

AND/OR logic

You can also use conditions like AND/OR . This allows you to use the AND condition, for example, to specify that a result is only entered in the formula field when the AND conditions are resolved. For the OR condition it is sufficient if one of the queried parameters is correct.

  • AND :
    Formula structure: AND([Feldname];[Feldname])
    Composite formula structure: IF(AND([Feldname]="Feldparameter1";[Feldname2]="Feldparameter2");"Ausgabe1";"Ausgabe2")
    In this case, formulas are dissolved from the inside to the outside. This means that only if field parameter1 and field parameter2 are correct will “Output1” be output. If the parameters do not match the default, “Output2” is displayed.
    Example:
    IF(AND([vermarktungsart]="kauf";[objektart]="grundstueck");"Grundstück zu verkaufen";"")
    Result: Case one: The type of commercialization and the type of property are in accordance with the specifications. Field is filled with “Land for sale”.
    Result: Case two: No match or only one match. In this case, output2 is output, that is, an empty field.
  • OR :
    Formula structure: OR([field name1]; [field name2])
    Composite formula structure: IF(OR([Feldname1]="Feldparameter1";[Feldname2]="Feldparameter2");"Ausgabe1";"Ausgabe2")
    Also in this case the formula is resolved from the inside out. For example, if field parameter1 or field parameter2 are correct, “Output1” is output. If neither the first parameter nor the second parameter is correct, the result is “Output2”.
    Example: IF(OR([newsletter_aktiv]="0";[newsletter_aktiv]="2");"Keinen Newsletter senden";"")
    Result Case 1: If the Singleselect field Newsletter is filled with No (0) or with Rejection(2) the text ‘Don’t send newsletter’ is displayed.
    Result case 2: If a different value is set, nothing is displayed in the field.

NOT Logic

Using NOT logic, you can, for example, exclude one or more field contents and thus influence the result. This means that the field contents lead to a result which is not mentioned in the NOT.

  • NOT :
    Formula structure: NOT([Feldname1]="Feldparameter");"Ausgabe"
    Composite formula structure: IF(NOT([Feldname1]="Feldparameter1");"Ausgabe1")

Examples from daily work

You want to extract single words from a field consisting of two or three words. An example would be salutation, first and last name. Please note that the formulas listed below only work for three words in the field.

First word:
=LEFT([Feld mit Wörtern];SEARCH(" ";[Feld mit Wörtern];1))
For explanation: about the formula is found by left the first space. Anything to the left of the space is then placed in the calculation field.

Second word:
=MID([Feld mit Wörtern];SEARCH(" "; [Feld mit Wörtern]; 1) + 1;SEARCH(" "; [Feld mit Wörtern]; SEARCH(" "; [Feld mit Wörtern];1) + 1)-SEARCH(" "; [Feld mit Wörtern];1))

Third word:
=RIGHT([Feld mit Wörtern];LENGTH([Feld mit Wörtern])-SEARCH(" ";[Feld mit Wörtern];SEARCH(" ";[Feld mit Wörtern];1)+1))