Home  >   Articles   >  Automation - Workflows - Vtiger Expressions
Articles in this section

Automation - Workflows - Vtiger Expressions

Learn to update field values by means of built-in functions.
R
Ruba
18 Oct, 2024 - Updated 2 months ago
Table of Contents

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

Note: To add a space between the two strings, use single quotes with a white space between them. For example, 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). 

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

Note

  • If only the number of days is given as input=t and a Date field is not provided, then the current date is considered for computation. For example, sub_days(3) works similar to sub_days(current date, 3).
  • The add_time(), and sub_time() functions also work similar to add_days() and sub_days().
 

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

Operator

Description

==

Equals to

!=

Not equal to

>

Greater than

<

Lesser than

>=

Greater than or equal to

<=

Lesser than or equal to

 
  • 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
 

Checking for empty fields

  • To check for empty integer or currency fields, compare with 0. For example, if Field==0.
  • For other fields, compare with 0 or NULL. For example, if Field==NULL.
  • Note: if Field==’ ‘ does not work. 
 

Other rules

  • To enable a checkbox (or set it to Yes), use 1. To disable a checkbox (or set it to No), use 0.
  • Field values are case-sensitive in workflow expressions. For example, if the field City has the value Bangalore, you cannot use city==’bangalore’ in an expression. You must use city==’Bangalore’.
  • To empty a field’s value, use ‘ ‘ or NULL. For example, if contact_name==’John’ description==NULL.  

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
 
Was this article helpful?
7  out of  13  found this helpful.
Comments 0
Be the first to comment
© Copyright 2023 Vtiger. All rights reserved.