Examples of Formula Field

Examples of Formula Field

Examples of Formula Field

Formula Fields can be complicated sometimes. So, here are examples that will help you understand the usage of these types of fields in the CRM.

  1. IF ELSE Statement Example: If field name contains 'Golf Only 1 Meal', then return 265 ; Else if field name .contains 'Golf Only 2 Meals' return 295 else if field name contains 'Single Occ' return 665 or return 0
    Formula: If(Contains(${Contacts.fieldname}, 'Golf Only 1 Meal'),265,If(Contains(${Contacts.fieldname},'Golf Only 2 Meals'),295,If(Contains(${Contacts.fieldname},'Single Occ'),665,0 ) ) )
  2. Example: 
    A manually entered Level; say A,B,C
    A manually entered File Received Date (Start Date)
    A formula field, that calculates a deadline date, based on the level and the File Received Date fields. This is called ''due date''.
    The calculation for Due Date -
    • If ''Level'' = A then the ''Due Date'' = ''File Received Date'' + 14 days
    • If ''Level'' = B then the ''Due Date'' = ''File Received Date'' + 10 days
    • If ''Level'' = C then the ''Due Date'' = ''File Received Date'' + 5 days
    Formula: If(Contains(${Leads.level},'A'),Adddate(${Leads.Start Date},14,'DAY'),If(Contains(${Leads.level},'B'),Adddate(${Leads.Start Date},10,'DAY'),If(Contains(${Leads.level},'C'),Adddate(${Leads.Start Date},5,'DAY'),Now())))
  3. Example: To calculate the age based on the Date of Birth details.

    Formula:
    • Age in Years =
      Year(Now())-Year(${Leads.DOB})- If(Month(Now())*100+Dayofmonth(Now())>=Month(${Leads.DOB})*100+Dayofmonth(${Leads.DOB}) ,0 ,1 )
    • Age in Months = (Datecomp(Now(),${Leads.DOB}))/(60*24*30)
  4. Create a matrix or a combination of two or three fields.
    Example: 
    A Product has number fields namely StyleColor and Size.
    Another field Product Matrix, should give the output as a combination of Style, Color and Size values.

    Formula: 
    Concat(Tostring(${Products.Style}),Tostring(${Products.Colour}),Tostring(${Products.Size}))
    Return Type: String
  5. Change the return type of the drop-down value as a number and calculate the values of two or more drop-down fields.
    Note that the drop-down values are considered as string values, and not number values.

    Example: 
    Drop-down 1 Values - 10, 20, 30
    Drop-down 2 Values - 5, 10, 15
    Formula (number) field needs to calculate - (Drop-down 1*Drop-down 2)+Drop-down 2

    Formula: 
    (Tonumber(Drop-down 1)*Tonumber(Drop-down 2))+Tonumber(Drop-down 2)
    Return Type: Number


    • Related Articles

    • Building Formula Fields

      Building Formula Fields The CRM formula fields enable you to define fields that can populate dynamically calculated data based on the values returned from other standard or custom fields. For instance, an insurance company may need to determine, if ...
    • Creating Formula Fields

      Creating Formula Fields Create Numeric Formula Field Create String Formula Field Create DateTime Formula Field Create Boolean Formula Field Formula Syntax Reference Formula fields are used to calculate different types of values, including numeric ...
    • Functions in Formula Fields

      Functions in Formula Fields Function performs a specific operation on values that you specify and generates a new value. To compose a function, you need to type the function name, followed by a set of parentheses(). The CRM formula type custom field ...
    • Data types in Formula Fields

      Data types in Formula Fields Data type is the kind of data that can be held and stored while evaluating an expression. Data types are the defined set of values and the allowable operations on those values. They represent either the function, ...
    • Field Level Customisation

      Field Level Customisation Standard Fields Custom Fields Formula Fields Special Fields Field-level customisation enables organising the field requirements of any organisation. By default, the CRM provides some standard fields and allows adding more ...