Authenticate Emails with SPF, DKIM, and SenderID Auto forward your emails to Vtiger Managing User Profiles Automate Outgoing Emails in Email Settings Automation - Approvals Automation - Assignment Rules Set up Multi-path Workflows Creating a Scheduled Workflow Automation - Scheduler Creating a Workflow Webforms in Vtiger CRM Set up a Workflow Action to Create Event Set up a Workflow Action to Create Records Set up a Workflow Action to Create Task Set up a Workflow Action to Invoke Custom Function Set up a Workflow Action to Create an SMS Task Set up a Workflow Action to Send Mails Set up a Workflow Action to Update Fields Automation - Workflow Action - Webhook Configuration - Business Hours Configuration - Company Details Configuration - Consents Configuration - Customer Portal Maps in Vtiger CRM Configuration - Usage Details Configuring Encrypted Data Fields in Vtiger CRM Configure Picklist Dependencies Considerations for Deactivating Vtiger Users Control Fields and Record Displays using Configuration Editor Create Reminders for Records and Inbox Create a field of a Grid type Dealing with Currencies and Taxes Enable Desktop Notifications on Chrome Web Browsers Vtiger CRM Add-ons IMAP Configuration - 2-way sync between Vtiger and IMAP providers Setting up Autopay & Payment Gateways Inventory - Tax Management Inventory - Terms and Conditions Login to Vtiger on SSO SAML using ADFS Mailroom Functionality for Different Scenarios Manage Multiple Currencies Marketing and Sales - Deal to Project Mapping Marketing and Sales - Lead Conversion Data Mapping Marketing and Sales - Profile Scoring Module Management - Labels Editor Module Management - Module Numbering Configure My Preferences Feature My Preferences - Calendar Settings Tags in Vtiger My Preferences - Notification Preferences SAML Support in Vtiger CRM Vtiger Mailroom Settings - Configure Module Settings Settings - Create Dynamic Fields and Layouts Module Management - Creating a Relationship Between Modules Settings - Customize Records and Fields for your Business Settings - How to set email autoresponder to Webform submission? Settings - Left Menu Settings - Manage Global Picklists in Vtiger Settings - Set up your Support Team Settings - Start Up Page Settings - Working with Picklist Values Module Management - Module Builder Support - SLA Policies Troubleshooting Login Issues Add-Edit Unsubscribe Links in your Email Template User Management - Authentication User Management - Encrypted Field Access Logs User Management - Groups User Management - Login History User Management - Profiles User Management - Roles User Management - Settings Log User Management - Sharing Rules User Management - Users User Management - Vtiger Support Access Vtiger Buzz - Chrome Extension for Notifications Vtiger Implementation wizard Vtiger Language Support Configuring Websense Trackers Websense - Widgets Module Management - Module Layouts & Fields Generate and Manage Third-party App Passwords in Yahoo Configuring App Passwords for Gmail Outgoing Server and Yahoo Mail Configuration - Storage Guard Customizing your Self-Service Portal Theme Using CSS Styles Automation - SMS Reply Actions Adding Hidden Fields to a Webform Configuring Dependent Fields and Blocks for Modules Auto forward Emails from Microsoft Office 365 Adding Custom Module Login Page Customization Settings - Personalize Module Layouts Module Management - Modules Duplicate Prevention in Modules Formula Fields Creating Custom Filters Adding a local DNS Entry

Introduction

In Workflows’ Update Fields and Create Record actions, you have the option to update field values by means of built-in functions. The functions perform computations such as concatenation of strings, addition and subtraction of days, etc.

This article explains in detail each function provided by Vtiger. To skip to the summarized explanation of the functions, click here. 

 To learn more about the Update Fields action, click here.

To learn more about the Create Records action, click here.

Functions

Listed below are all the functions provided by Vtiger.

concat(a,b)

 This function combines two fields. You can use this, especially to combine Text fields.

For example, if you have a field called Full name, you can combine the first name and the last name using concat(firstname,lastname). 

The concat() function can combine any number of strings separated by commas. For Example, to form the Billing Address field, the format is concat(Street,’ ‘, City,’ ‘,Pincode).
 

time_diffdays(a,b)

 This function takes Date fields as input. It calculates the difference in time in days between the two fields.

For example, you want to know the Support Period of a contact. The difference can be calculated using time_diffdays(support_end_date,support_start_date). 

 The Support End Date is subtracted from the Support Start Date to fetch the result.

time_diffdays(a)

This function provides the difference between the current date and a Date field.

   For example,

• You want to know how many days have passed since a case was created. For this, you must use the function time_diffdays(createdtime). In the background, the function operates this way - time_diffdays(current date - created time).
• To update the Age field in Leads or Contacts depending on the Date Of Birth, the function is (time_diffdays(birthday))/365. 

time_diff(a,b)

 This function takes Date fields as input and calculates the difference in time between the fields in seconds. 

For example, to update the Elapsed Time field depending on the Created Time and Modified Time of a record, you must use time_diff(modifiedtime,createdtime). The function performs the subtraction as follows - time_diff(modifiedtime - created time). 

time_diff(a)

This function gives the time difference (in seconds) between the current date and a Date field.

For example, you want to know how many seconds have passed since an internal ticket was created. For this, you must use the function time_diff(createdtime). In the background, the function operates this way - time_diff(current date - created time).

time_diffweekdays(a,b)

This function takes Date fields as input and calculates the difference in time (in days) between two fields. The calculation is based only on your working days as configured in your business hours. 

For example, you want to know the Support Period of a contact. You can calculate it using the Support Start Date and Support End Date fields. The difference can be calculated using time_diffweekdays(support_end_date,support_start_date).

time_diffweekdays(a)

This function provides the difference between the current date and a Date field.  The calculation is based only on your working days as configured in your default business hours.

For example, you want to know how many weekdays have passed since a case was created. For this, you must use the function time_diffweekdays(createdtime). In the background, the function operates this way - time_diffdays(current date - created time).

add_days()

This function allows users to compute a new date. It takes two inputs - a date and a number - separated by commas. It adds the number to the date and results in a new date.

For example, you want to provide a customer with a support period of 60 days and update the end date in the Support End Date field. The function would be add_days(start_date,60), where the first parameter is a Date field and the second parameter is the number of days.

So, add_days(2020-08-16,60) will result in the date 2020-10-15.

add_weekdays()

This function allows users to compute a new date, taking only weekdays into account. It takes two inputs - a date and a number - separated by commas. It adds the number to the date and results in a new date. It starts when the default business hours are set.

For example, you want to update the Actual Closing Date by adding ‘n’ number of days to the Expected Close Date. You can use this function - add_weekdays(closingdate,n).

sub_days()

This function allows users to compute a new date by taking two inputs - a date and a number separated by commas. It deducts the number from the date. 

For example, to send an email to a customer ‘n’ days before their Support End Date, you can create and update a custom field called Notification Date. The function would be sub_days(support_end_date,n).
  

day_of_date (date_field)

This function is used to extract the day portion (i.e., the day of the month) from a date field within a database.

For example, when a customer asks about the day a particular record was created, this function will help you give the exact day (Example: Monday).

date_format (date_field, format)

This function computes a date field in a particular format like dd_mm_yy, mm_dd_yy, or yyyy_mm_dd. You can specify the field where you want this date format to appear.

add_time(timefield,minutes)

This function computes a new time by adding the specified time and minutes.

For example, consider that an educational institution is conducting an online test. To update the end time of the exam, the function can be used as follows - add_time(exam_start_time, 180).

substring(field, start, length)

This function allows you to extract a portion of text from a string field within a database or a programming language.

For example, assume you have a string field called full_name containing the value John Doe. If you use the substring function with the parameters substring(full_name, 1, 4), you will be able to extract the name John because it starts at position 1 and extracts 4 characters from that position.

replace(field, search, replace)

This function allows you to replace occurrences of a specified substring (the search parameter) within a string field (the field parameter) with another substring (the replace parameter).

For example, assume you have a string field called product_description containing the value Black T-shirt. If you use the replace() function with the parameters replace(product_description, 'Black', 'Red'), it would replace the substring Black with Red, resulting in the string Red T-shirt.

sub_time(timefield,minutes)

This function computes a new time by subtracting the specified minutes from the specified time.

For example, if you conduct a recruitment drive in your company, you can set up candidates’ login time by using the function sub_time(interview_start_time, 90).

today

This function fetches the current date. When you pick this function from the workflow Expressions, it appears as get_date(‘today’).

For example, if you need to update the Payment Received date when an invoice Status is changed to Paid, then use get_date('today').

tomorrow

This function fetches the date of the next day. When you pick this function from the workflow Expressions, it appears as get_date(‘tomorrow’).

For example, if the Status of a project is changed to Initiated on the current day, then the Start Date of the project is updated with tomorrow’s date. So, the expression would be get_date('tomorrow').

yesterday

This function fetches the date of the previous day. When you pick this function from the workflow Expressions, it appears as get_date(‘yesterday’).

power(base,exponent)

This function is used to calculate the power of an integer, such as the square or cube of an integer.

For example, 2^3=8 can be simulated using the function power(2,3)

uuid(type_number)

A universally unique identifier or UUID, is a 128-bit unsigned integer. It is usually represented as a hexadecimal string, split into five groups with dashes. The most widely - known and used types of UUIDs are defined by RFC4122. A UUID, when coded in hexadecimal string format looks like the following: 

‘ebb5c735-0308-4e3c-9aea-8a270aebfe15’.

roundoff(field,precision)

This function is used to round off a number to the specified precision. 

For example, Amount = 4350.68 is rounded off to 4351 when the function roundoff(amount,0).

uppercase(field)

This function will convert the text 'field' value to upper case. 

For example, this function converts the sentence ‘Welcome to vtiger crm!’ to ‘WELCOME TO VTIGER CRM!’

Note: This function applies only to Text and Textarea fields.

if-else statements

To avoid creating multiple workflows to update a single field in a record, you can make use of the if-else statement.

For example, to update the Probability field as a deal’s Sales Stage changes, the expression is as follows:

if sales_stage=='Closed Lost' then 0 else if sales_stage=='Prospecting' then 10 else if sales_stage=='Perception Analysis' then 50 else if sales_stage=='Proposal or Price Quote' then 60 else if sales_stage=='Agreement' then 80 else 100 end.

Rules for Creating Expressions

• Define text field values inside the single quotes (‘ ’). For example, first_name==‘John’.
• Write expressions in lowercase letters.
• Use arithmetic operators to perform Addition (+), Subtraction (-), Multiplication (*), and Division (/). 
  • You can perform these operations between two or more fields, values, or a combination of fields and values.
• Use only the following relational operators in expressions.
  • Note: Using the assignment operator (=) does not check for the if condition and directly executes the last else condition. You must use only the relational operator (==).

  

• Use binary values for the checkbox fields.
  • 0 for False or No.
  • 1 for True or Yes.
• Support for AND and OR:
  • AND: All the conditions must match. 
    • For example, If the Sales Stage of a Deal is Qualification and Type is Existing Business, then set the Probability to 70.
      if sales_stage==‘Qualification’ AND type==‘Existing Business’ then 70 end
  • OR: At least one of the specified conditions must match. 
    • For example, If the Sales Stage of a Deal is Dormant or Not Sales Ready, then set Deal Priority to Low.
      if sales_stage==‘Dormant’ OR sales_stage==‘Not Sales Ready’ then ‘Low’ end

Summary

Sl. No.	Function Name	Description
1.	concat(a,b)	Combines the two strings ‘a’ and ‘b’
2.	time_diffdays(a,b)	Gives the difference between Date field ‘a’ and Date field ‘b’ (a minus b) in days
3.	time_diffdays(a)	Gives the difference between the current date and the Date field ‘a’ in days
4.	time_diff(a,b)	Gives the difference between Date field ‘a’ and Date field ‘b’ in seconds
5.	time_diff(a)	Gives the difference between the current date and the Date field ‘a’ in seconds
6.	time_diffweekdays(a,b)	Gives the difference between Date field ‘a’ and Date field ‘b’ (a minus b) in weekdays, based on the business hours configured
7.	time_diffweekdays(a)	Gives the difference between the current date and a Date field ‘a’ in weekdays, based on the business hours configured
8.	add_days(a,b)	Adds the number of days in ‘b’ to the Date field in ‘a’, resulting in a date
9.	add_weekdays(a,b)	Adds the number of days in ‘b’ to the Date field in ‘a’ considering only weekdays, resulting in a date
10.	sub_days(a,b)	Subtracts the number of days in ‘b’ from the Date field in ‘a’, resulting in a date
11.	add_time(timefield,minutes)	Adds the ‘minutes’ to the ‘timefield’, resulting in a new time
12.	sub_time(timefield,minutes)	Subtracts the ‘minutes’ from the ‘timefield’, resulting in a new time
13.	today	Gives the current date
14.	tomorrow	Gives the date of the next day
15.	yesterday	Gives the date of the previous day
16.	power(base,exponent)	Calculates the power of the ‘base’ with respect to the ‘exponent’
18.	roundoff(field,precision)	Rounds the value in ‘field’ to the specified precision
19.	uppercase(field)	Converts the value in 'field' (text) to uppercase