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

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

time_diffdays(a)

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

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

time_diff(a)

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

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. 

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.

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

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. 

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.

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