Formulas are used in DataRaptors to transform data from one format to another. In the Transform step of a DataRaptor, all formulas are executed. To define a Formula, specify the desired transformation and the location in the Extraction Step JSON where the output resides
1. IF Formula: This formula allows you to evaluate a condition and return a value based on the result. The syntax for this formula is: IF(condition, value_if_true, value_if_false)
For example, you could use this formula to create a new field that returns “Yes” if the value in another field is greater than 10, and “No” if it’s less than or equal to 10. The formula would look like this:
IF(Price > 10, "Yes", "No")2. CONCATENATE Formula: This formula allows you to combine two or more values or strings into a single field. The syntax for this formula is: CONCATENATE(string1, string2, …)
For example, you could use this formula to combine a first name and last name field into a full name field. The formula would look like this:
CONCATENATE(FirstName, " ", LastName)3. DATE Formula: This formula allows you to convert a text string that represents a date to a serial number that Vlocity recognizes as a date. The syntax for this formula is:
DATE(year, month, day)
For example, you could use this formula to convert a date field from text format to date format. The formula would look like this:
DATE(YearField, MonthField, DayField)4. VLOOKUP Formula: This formula allows you to search for a specific value in a table or range and return a corresponding value from another column. The syntax for this formula is:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
For example, you could use the VLOOKUP formula to find a product code in a table and return its price. The formula would look like this:
VLOOKUP(ProductCode, PriceTable, 2, FALSE)Here, ProductCode is the value you’re searching for, PriceTable is the table where you want to search, 2 is the column number from which you want to return the result, and FALSE specifies that you want an exact match.
5. SUMIF Formula: This formula allows you to sum the values in a range that meet a specified condition. The syntax for this formula is: SUMIF(range, condition, [sum_range])
For example, you could use this formula to sum the values in a field that are greater than 10. The formula would look like this:
SUMIF(PriceField, ">10")Here, PriceField is the range of cells you want to sum, and “>10” is the condition that specifies that you want to sum only the cells that contain a value greater than 10.
6. TRIM Formula: This formula allows you to remove leading and trailing spaces from a string. The syntax for this formula is:
TRIM(text)
For example, you could use this formula to remove spaces from the beginning and end of a field containing email addresses. The formula would look like this:
TRIM(EmailField)Here, EmailField is the field that contains the email addresses that you want to trim.
7. MID Formula: This formula extracts a specified number of characters from a string, starting at a specified position. The syntax for this formula is: MID(text, start_num, num_chars)
For example, you could use this formula to extract the first three characters of a field containing product codes. The formula would look like this:
MID(ProductCodeField, 1, 3)Here, ProductCodeField is the field containing the product codes, 1 is the starting position, and 3 is the number of characters to extract.
8. LEN Formula: This formula returns the number of characters in a string. The syntax for this formula is:
LEN(field)
Here, “field” is the name of the field that you want to count the characters of. This field can be a text or string field. The formula returns the number of characters in that field as an integer.
For example, if you have a field called “Product Description” that contains a text description of a product, you could use the LEN formula to count the number of characters in that field. The formula would look like this:
LEN(ProductDescription)If the value in the Product Description field was “This is a sample product description.”, the formula would return 33, because there are 33 characters in that string.
The LEN formula can be useful in many scenarios, such as when you want to set a limit on the number of characters that can be entered in a field, or when you want to count the number of characters in a field to use in a formula.
Useful links
https://help.salesforce.com/s/articleView?language=en_US&id=sf.os_function_reference.htm&type=5
