Vlocity Facts #39 || DataMapper - Functions and Formulas

In Data Mapper we can use functions and formulas to manipulate our data or we can change the out come of the values according to our requirement.
In this post we will go through some of the formulas which we used in daily requirements.

1. Consider the following example: we have a list of products and need to filter them depending on their color or name. By using FILTER() function we can separate the list which has Green products.

FILTER(LIST(Products),'Color == "Green"')

Result:

2. We are getting list of contacts or cases and we need to display those records by sorting with DSC or ASC. By using SORTBY() we can sort the list by using this formula.

 SORTBY(LIST(Nephews), 'Name','[:DSC]')

Result: 

Note: we can use [:DSC] only in Data Mapper Transform.

3. We can use SUM(), AVG(), MIN(), MAX() functions to return the Sum/Avg/Min/Max of the list or individual values.

Result: 

4. We can executes a SOQL query to produce a JSON list of values. The query cannot return more than one column. For more details check this SOQL Query post [In which i have mention couple of scenarios].

QUERY("Select Name from Account ORDER BY CreatedDate Limit 5")

5. If we need to display the number of open cases or closed cases related to Account or Contact. By using COUNTQUERY() we can get those details.
I'm passing Account Name to get no: of cases which are related to that Account.

COUNTQUERY ( "SELECT COUNT() FROM Case Where Account.Name = '{0}'",'United Oil & Gas Corp.')

Result: 

6. We can SPLIT() the string depending on the token. After splitting the string, the output will be a list of values.

SPLIT("Vlocity Omnistudio Salesforce", " ")

Result: 

7. We can split the string and get the particular value of the string by using SUBSTRING().

SUBSTRING(%PhoneNumber%, 8)

Here I'm trying the split the phone number according to my requirement.
Result: 

8. Let us consider when we are doing authentication we will try to display the phone number or email with some asterisk(*) or any other combination to not display the exact values in some requirements. By using CONCAT() we can achieve those requirements.

CONCAT(%PhoneNumberFirst%,"**",%PhoneNumberMiddle%,"**",%PhoneNumberLast%)

By using 7th point response i have combined and got this result.
Result: 

9. We can use IF() and check the expressions and return the response based on the conditions. If PhoneNumber is empty it will return the text what i have mentioned or else will get the combined string.

IF(%PhoneNumber% ==' ', 'no phone number related to this record.', CONCAT(%PhoneNumberFirst%,"**",%PhoneNumberMiddle%,"**",%PhoneNumberLast%)) 

Result: 

One more example for IF condition. Passing Country Name and adding the dialing code to that phone number.

Result: