You are here: Home / Extras / Formulas and calculating / Use for addresses and properties

Formulas and calculating

Use for addresses and properties

Calculation (Maths)

A calculation formula always begins with an equals sign “=”, as many know it 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 editor, the comma “,” must be written as a dot “.”.
Here is an example: =[cold rent]+[additional costs]+[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 longish name “ind…” is the name of a self-created field. You can therefore specify formulas for system fields and for user-defined fields.

There are the basic arithmetic operations (+ – * /) and there is a period before the dash. You can use all number fields and 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 based on field values. For this a formula of the formula logic is used as parameter.
    Formula Structure: _calculate(text(DATEADD(today();10;"d");"DD.MM.YYYY"))
    Examples: _calculate([purchase price]*3) or _calculate(IF([purchase price]<1000000;[purchase price]*3;[purchase price]*4))

Functions

Formula functions allow you, for example, to calculate an average value from several number fields or to shorten text fields. At the moment there are four different areas which can be edited via functions. These are:

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

functions To create a formula function, you must select the corresponding module and category under Tools >> Settings >> Administration >> Input fields and create a new individual field there 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 the like. Altogether there are 7 text functions and a new text operator “& “,by which text fields can be connected. Below you will find the respective formulas with examples. All examples refer to the field “Property title” with the content “Flat greened plot”

  • LENGTH :
    You can use the LENGTH formula to display the length of the contents of a text field. The result is output as an integer.
    Formula structure: LENGTH([field name])
    Example: LENGTH([objekttitel]) The length of the properties title is displayed.
    Result: 26
  • LEFT :
    The LEFT formula allows you to output a certain number of characters from the left. This is done in text form.
    Formula structure: LEFT([field name];number of characters)
    Example: LEFT([objekttitel];5) The first 5 characters of the properties title are output.
    Result: flat
  • RIGHT :
    The RIGHT formula is the opposite of the LEFT formula. It works the same way.
    Formula structure: RIGHT([field name];number of characters)
    Example: RIGHT([objekttitel];10) 10 characters are output from the right.
    Result: Lot
  • MID :
    The MID formula allows you to output text from the center.
    Formula structure: MID([Field name];First character;Number of characters)
    Example: MID([objekttitel];7;9) From the 7th character onwards the next 9 characters will be output.
    Result: green
  • SEARCH :
    With SEARCH you can search in a text field for terms, character strings and spaces.
    Formula structure: SEARCH("Search term";[Field name];Optional: First character)
    Example: SEARCH(" ";[objekttitel]) The system searches for the first blank character in the properties title.
    Result: 6
    Example: SEARCH(" ";[objekttitel];7) From the 7th character onwards, the system searches for the next blank character.
    Result: 16
  • SEARCH_MULTISELECT :
    Use SEARCH_MULTISELECT to search a multiselect field for values.
    Formula structure: SEARCH_MULTISELECT("search term";[field name of the multiselect field])
    Example: SEARCH_MULTISELECT("loads";[elevator])
    It is searched for the multiselect value “loads”, i.e. freight elevator, in the multiselect field “elevator”.
    Result: 0, if “search term” was not found (so = value is not included). Depending on the data type of the field, the 0 is not displayed on the graphical user interface, but the field remains empty.
    Result: 0, if no multiselect field was specified for [Field name of multiselect field]. Depending on the data type of the field, the 0 is not displayed on the graphical user interface, but the field remains empty.
    Result: 1, if “search term” was found (i.e. “value is contained”).
  • TRIM :
    TRIM allows you to “trim away” spaces before and after the text. Since all text fields in onOffice enterprise are trimmed from the outset, the example consists of a composite formula function.
    Formula structure: TRIM([field name])
    Example: TRIM(MID([objekttitel];6;11)) First, cut out the property title using the function MID ” greened ” and remove the pre- and post-blanks using TRIM.
    Result: “greened”
  • TEXT :
    With the TEXT function you can, for example, output a date field as text. In the example, the date field [erstellt_am] is used. This field is created when the properties 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 outputs the date field in a different notation, based on the macro _Date.
    Result: 19/02/17
  • Operator “&” The operator “&” can be
    used, for example, to connect several text fields or number fields.
    Formula Structure: [Field name]&[Field name] or [Field name]&"Text" example
    : [objekttitel]&" "&[objekttitel] displays the property title 2 times in a row, separated by a space.
    Result: flat greened plot flat greened plot

2. Numeric functions

Numeric functions allow you to calculate an average value from several numeric fields or to round numeric values. There are 5 numerical functions available. Below you will find the respective formula with examples. All examples refer to the field “purchase price” with the value 162.990,35. To calculate the mean value, the field “Market value” with the value 170,000.00 is also used

  • ROUNDUP :
    A value can be rounded up using the ROUNDUP formula.
    Formula structure: ROUNDUP([field name];number of decimal places which are rounded)
    Example: ROUNDUP([purchase price];1) The purchase price is rounded up to one decimal place.
    Result: 162,990.40
  • ROUNDDOWN :
    Number fields can be rounded down using the ROUNDDOWN formula.
    Formula structure: ROUNDDOWN([field name];number of decimal places which are rounded)
    Example: ROUNDDOWN([purchase price];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([field name];number of decimal places to be rounded)
    Example: ROUND([purchase price];1) One decimal place is rounded.
    Result: 162,990.40
  • FIXED :
    The formula FIXED allows you to shorten numbers by decimal places or to output more decimal places. Please note that the FIXED formula can only be applied in a text field and therefore it is not possible to calculate further with the result.
    Formula Structure: FIXED([field name];number of decimal places to be output)
    Example: FIXED([purchase price];4) A total of 4 decimal places are output.
    Result: 162,990.3500
    Example: FIXED([purchase price];0) No decimal places are issued from the purchase price.
    Result: 162,990
  • AVERAGE :
    AVERAGE allows you to average two or more number fields.
    Formula Structure: AVERAGE([field name];[field name2])
    Example: AVERAGE([purchase price];[market value]) The mean value of the two number fields purchase price and market value is determined.
    Result: 166,495.18
    Calculator result: 166,495.175
  • SUM :
    You can use the SUM formula to calculate the sum of several individual fields.
    Formula structure: SUM([field name1];[field name2];[field name3])
    Example: SUM([purchase price];[market value])
    Result: 332,990.35
  • MIN :
    The MIN formula can be used to determine the minimum value from a pool of values.
    Formula structure: MIN([Field name1];[Fendname2];[Field name3]) The lowest value from the 3 fields is output.
    Example: MIN([purchase price];[market value]) The minimum value of the purchase price and the market value of the property is calculated.
    Result: 162,990.35.
  • MAX :
    By using the MAX formula, the maximum value can be determined from a pool of values.
    Formula structure: MAX([Field name1];[Field name2];[Field name3]) The highest value from the 3 fields is output.
    Example: MAX([purchase price];[market value])
    Result: 170,000.00
  • POWER:
    The POWER function can be used to calculate powers.
    Formula structure: POWER([Field name1];[Field name2]) or POWER(Number1,Number2) The first parameter specifies the basis, the second parameter specifies the exponent.
    Example: POWER(5;2)
    Result: 25
  • LOG:
    Logarithms can be calculated using the LOG function.
    Formula structure: LOG([field name1];[field name2]) or LOG(number;base). The first parameter specifies the logarithm and, 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

Another application area of the formula functions is date fields. Here you can display the time between 2 dates or you can calculate a defined number of days, months or years on one 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 used. Today is 01.03.2017

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

  • TODAY :
    The current date can be output using the TODAY formula.
    Formula structure: TODAY()
    Example: TODAY() Today’s date is output.
    Result: 01.03.2017
  • WEEKDAY :
    The formula WEEKDAY can be used to output the weekday of a date field.
    Formula Structure: WEEKDAY([field name])
    example: WEEKDAY([erstellt_am]) The day of the week of the field “created on” is displayed in the field.
    Result: Thursday
  • MONTH :
    The formula MONTH displays the month of the field to be calculated.
    Formula structure: MONTH([field name])
    Example: MONTH([erstellt_am]) The month of the field “created on” is displayed in the field.
    Result: February
  • DATEADD :
    Using the DATEADD formula, you can use a parameter (d,m or y) to add a number of days, months or years to a date field.
    Formula Structure: DATEADD([field name];number;"parameters")
    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") One year is added.
    Result: 09.02.2018
  • DATEDIF :
    With DATEDIF you can display the time between two date fields in days(d), months(m) or years(y).
    Formula Structure: DATEDIF([field name];[field name2];"parameter")
    Example: DATEDIF([erstellt_am];[order to];"d") The time between “created on” and “order to” is displayed in days.
    Result: 750
    Example: DATEDIF([erstellt_am];[auftragbis];"m") The months between the two data are output.
    Result: 24
    Example: DATEDIF([erstellt_am];[auftragbis];"y") The years between the two dates are to be displayed.
    Result: 2
  • TEXT :
    Using the TEXT function, you can, for example, output a date field as text. In the example, the date field [erstellt_am] is used. This field is created when the properties 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 outputs the date field in a different notation, based on the macro _Date.
    Result: 19/02/17

4. Logic Functions

IF Logic

It is also possible to integrate IF Queries. All fields (including text fields) can be used as conditions. The syntax is based on the Excel function “If()”: IF(Condition true;Then;Otherwise). The value of the field for which the formula is entered is filled with the contents of the Then or Otherwise condition.
Here is an example: IF([Field name1]>0;[Field name2]+[Field name3];[Field name4]+[Field name8]) AND/OR

Logic You

can also use conditions such as AND/OR . You can use the AND condition, for example, to specify that a result is not entered in the formula field until the AND conditions have been resolved. With the OR condition, it is sufficient if one of the queried parameters is correct

  • AND :
    Formula structure: AND([field name];[field name])
    Composite formula structure: IF(AND([Field name]="Field parameter1";[Field name2]="Field parameter2");"Output1";"Output2")
    In this case, formulas are resolved from the inside out. This means that only if field parameter1 and field parameter2 are correct does the output of “Output1” occur. If the parameters do not match the default, “Output2” will be displayed.
    Example:
    IF(AND([marketing type]="purchase";[property type]="land");"land for sale";"")
    Result: Case 1: Marketing type and property type comply with the specifications. Field is filled with “Land for sale”.
    Result: Case 2: No match or only one match. In this case, output2 is output, i.e. an empty field.
  • OR :
    Formula structure: OR([field name1];[field name2])
    Compound formula structure: IF(OR([field name1]="field parameter1";[field name2]="field parameter2");"output1";"output2") The formula is
    also resolved from the inside out in this case. If field parameter1 or field parameter2 are correct, “Output1” is output. If neither the first parameter nor the second parameter are correct, “Output2” occurs.
    Example: IF(OR([newsletter_aktiv]="0";[newsletter_aktiv]="2");"Don't send newsletter";"")
    Result Case 1: If the single select field Newsletter is filled with No (0) or with Cancellation(2) the text ‘Don’t send newsletter’ is displayed.
    Result Case 2: If another value is set, nothing is displayed in the field

NOT logic

You can use NOT logic, for example, to 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([field name1]="field parameter");"output"
    Compound formula structure: IF(NOT([field name1]="field parameter1");"output1")