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 Zoho 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 Style, Color 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