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 of 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 of 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 of 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 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.

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 prior to 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).

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).

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).

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 lower case 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