Create Custom Fields with Formulas for Conditional Logic

1. Log in to Vanillasoft as an Admin.
   

   • If you are logged in as a Caller, switch to Admin from the top menu.

   ---

2. Open the target Project from the Admin homepage.
   

   ---

3. Open the Field Customization menu, then the Custom Fields submenu, and select the Custom Fields section. 
   

   ---

4. For a new field, enter the desired name in Add New Field, then click Add. 
   

   • To modify an existing field, click to highlight the field name in the list of existing fields, then continue with the next steps.

   ---

5. Ensure the target field is highlighted in the list. 
   

   ---

6. Open the Field Type dropdown and select Formula. 
   

   ---

7. In the Formula text field, enter your formula using the references below. 
   

   ---

8. Click Save. 
   

Purpose: Return age in years from a date like Date of Birth.

AGE([DateOfBirth])

With logic:

IIF(AGE([DOB]) >= 65, "Senior", "Non-Senior")

CASE WHEN AGE([DOB]) < 18 THEN "Underage" ELSE "Adult" END

Purpose: Return the next occurrence of a date like a birthday or renewal.

NEXT_ANNIVERSARY([DOB])

Days until the next anniversary:

DATEDIFF(day, TODAY(), NEXT_ANNIVERSARY([JoinDate]))

Due within 30 days:

CASE WHEN DATEDIFF(day, TODAY(), NEXT_ANNIVERSARY([RenewalDate])) < 30
     THEN "Due Soon"
     ELSE "Not Due"
END

IIF([Score] >= 80, "Qualified", "Not Qualified")

IIF([OptIn] = 1, "Subscribed", "Unsubscribed")

IIF([Gender] = "Female", "Ms.", "Mr.")

IIF([Country] = "USA", "Domestic", "International")

IIF(AGE([DOB]) >= 18, "Adult", "Minor")

CASE
  WHEN [Score] >= 90 THEN "Excellent"
  WHEN [Score] >= 75 THEN "Good"
  WHEN [Score] >= 60 THEN "Average"
  ELSE "Poor"
END

CASE
  WHEN [Country] = "USA" THEN "USD"
  WHEN [Country] = "Canada" THEN "CAD"
  ELSE "Other"
END

CASE
  WHEN [Status] = "New" THEN "Pending"
  WHEN [Status] = "Contacted" THEN "In Progress"
  WHEN [Status] = "Closed" THEN "Done"
  ELSE "Unknown"
END

CASE
  WHEN [Industry] = "Healthcare" THEN "Group A"
  WHEN [Industry] = "Education" THEN "Group B"
  ELSE "Other"
END

CASE
  WHEN LIKE([Title], "%President%") THEN "Executive"
  WHEN LIKE([Title], "%Manager%")   THEN "Mid-Level"
  ELSE "Staff"
END

• STR(number) - Convert a number to text
  STR([AnnualRevenue]) 

  ---

• LTRIM(text), RTRIM(text) - Remove leading or trailing spaces
  LTRIM(RTRIM([FirstName])) 

  ---

• TRY_CAST(expression AS datatype) - Safely convert types. Returns NULL on failure
  TRY_CAST([Zip] AS INT) 

  ---

• DATEPART(part, date) - Extract part of a date
  DATEPART(year, [HireDate]) 

  ---

• DATEDIFF(part, start, end) - Difference between two dates
  DATEDIFF(day, [LastCallDate], TODAY())

• Use double quotes for text like "text".
• Match the output type to where the field is used.
• TRY_CAST returns NULL if conversion fails. Account for that in your logic.
• Test on sample records before deploying to all users.

If the formula is not valid, you may see:

There is an error in your formula. Please check for missing fields, incorrect syntax, or unsupported operations. Make sure all functions and field references are valid before saving.

• Confirm each field name in brackets matches the field exactly. Example: [DateOfBirth].
• Check that quotes and parentheses are balanced.
• Remove unsupported operators or nested functions that are not allowed.