⇧ Back to Top

Calculate Field Values

+ (Add)

Description:Calculates the sum of two numeric values(NULL values are treated as 0s).
Use:value1 + value2 and replace each value with merge fields, expressions, or other numeric values.
Example:Amount + Maint_Amount__c + Services_Amount__c + 10.0
This formula calculates the sum of the product Amount, maintenance amount, and services fees. Maint amount and Service Fees are custom currency fields.

- (Subtract)

Description:Calculates the difference of two values(NULL values are treated as 0s).
Use:value1 - value2 and replace each value with merge fields, expressions, or other numeric values.
Example:Amount - Discount_Amount__c – 200
This formula calculates the difference of the product Amount less the Discount Amount. Discount Amount is a custom currency field.

* (Multiply)

Description:Multiplies its values(NULL values are treated as 0s).
Use:value1 * value2 and replace each value with merge fields, expressions, or other numeric values.
Example:Consulting_Days__c * 1200
This formula calculates the number of consulting days times 1200 given that this formula field is a currency data type and consulting charges a rate of $1200 per day. Consulting Days is a custom field.

/ (Divide)

Description:Divides its values(If the numerator is NULL, it is treated as 0) .
Use:value1 / value2 and replace each value with merge fields, expressions, or other numeric values.
Example:AnnualRevenue/ NumberOfEmployees
This formula calculates the revenue amount per employee using a currency field.

() (Open Parenthesis and Close Parenthesis)

Description:Specifies that the expressions within the open parenthesis and close parenthesis are evaluated first. All other expressions are evaluated using standard operator precedence.
Use:(expression1) expression2… and replace each expression with merge fields, expressions, or other numeric values.
Example:(Unit_Value__c - Old_Value__c) / New_Value__c calculates the difference between the old value and new value divided by the new value.

== (Equal)

Description:Evaluates if two values are equivalent.
Use:expression1 == expression2, and replace each expression with merge fields, expressions, or other values.
Example:Commission Amount
IF(Probability == 1, Amount*0.02, 0)
This formula calculates the 2% commission amount of an opportunity that has a probability of 100%. All other opportunities have a commission value of 0.

!= (Not Equal)

Description:Evaluates if two values aren’t equivalent.
Use:expression1 != expression2, and replace each expression with merge fields, expressions, or other values.
Example:IF(Maint_Amount__c + Services_Amount__c != Amount, "DISCOUNTED", "FULL PRICE")
This formula displays DISCOUNTED on product if its maintenance amount and services amount don’t equal the product amount. Otherwise, displays FULL PRICE. Note that this example uses two custom currency fields for Maint Amount and Services Amount.

< (Less Than)

Description:Evaluates if a value is less than the value that follows this symbol.
Use:value1 < value2 and replace each value with merge fields, expressions, or other numeric, text, date, datetime values.
Example:IF(AnnualRevenue < 1000000, 1, 2) assigns the value 1 with revenues less than one million and the value 2 to revenues greater than one million.

> (Greater Than)

Description:Evaluates if a value is greater than the value that follows this symbol.
Use:value1 > value2 and replace each value with merge fields, expressions, or other numeric, text, date, datetime values.
Example:IF(commission__c > 1000000, "High Net Worth", "General") assigns the High Net Worth value to a commission greater than one million. Note, this is a text formula field that uses a commission custom field.

<= (Less Than or Equal)

Description:Evaluates if a value is less than or equal to the value that follows this symbol.
Use:value1 <= value2 and replace each value with merge fields, expressions, or other numeric, text, date, datetime values.
Example:IF(AnnualRevenue <= 1000000, 1, 2) assigns the value 1 with revenues less than or equal to one million and the value 2 with revenues greater than one million.

>= (Greater Than or Equal)

Description:Evaluates if a value is greater than or equal to the value that follows this symbol.
Use:value1 >= value2 and replace each value with merge fields, expressions, or other numeric, text, date, datetime values.
Example:IF(Commission__c >= 1000000, "YES", "NO") assigns the YES value with a commission greater than or equal to one million. Note, this is a text formula field that uses a custom currency field called Commission.

&& (AND)

Description:Evaluates if two values or expressions are both true. Use this operator as an alternative to the logical function AND.
Use:(logical1) && (logical2) and replace logical1 and logical2 with the values or expressions that you want evaluated.
Example:IF((Price<100 && Quantity<5),"Small", null)
This formula displays Small if the price is less than 100 and quantity is less than five. Otherwise, this field is blank.

|| (OR)

Description:Evaluates if at least one of multiple values or expressions is true. Use this operator as an alternative to the logical function OR.
Use:(logical1) || (logical2) and replace any number of logical references with the values or expressions you want evaluated.
Example:IF((Color__c == "Red" || Size__c < 5),"Beautifual", "Normal")
This formula returns the category of a particular type of stone. If the color of the stone is Red or the size is less than 5, it is a “Beautiful” stone.

& (Concatenate)

Description:Connects two or more objects into a string.
Use:string1 & string2 and replace each string with merge fields, expressions, or other values.
Example:"Expense-" & Trip_Name__c & "-" & ExpenseNum__c
This formula displays the text Expense- followed by trip name and the expense number. This is a text formula field that uses an expense number custom field.

$User.Id

Description:Returns the Id of the currently running user in the current org.
Use:$User.Id

$User.UserName

Description:Returns the UserName of the currently running user in the current org.
Use:$User.UserName

$User.Email

Description:Returns the Email of the currently running user in the current org.
Use:$User.Email

$User.ProfileId

Description:Returns the ProfileId of the currently running user in the current org.
Use:$User.ProfileId

ADD_DAYS

Description:Returns the date that is the indicated number of days before or after a specified date.
Use:ADD_DAYS (date/datetime, num) and replace date with the start date and num with the number of days to be added.
Example:ADD_DAYS(StartDate, 5)
Adds 5 days to the start date. For example, if the start date is September 20, 2017, the resulting date is September 25, 2017.

ADD_DAYS

Description:Returns the date that is the indicated number of days before or after a specified date.
Use:ADD_DAYS (date/datetime, num) and replace date with the start date and num with the number of days to be added.
Example:ADD_DAYS(StartDate, 5)
Adds 5 days to the start date. For example, if the start date is September 20, 2017, the resulting date is September 25, 2017.

ADD_MONTHS

Description:Returns the date that is the indicated number of months before or after a specified date. If the specified date is the last day of the month, the resulting date is the last day of the resulting month. Otherwise, the result has the same date component as the specified date.
Use:ADD_MONTHS (date/datetime, num) and replace date with the start date and num with the number of months to be added.
Example:ADD_MONTHS(StartDate, 5)
Adds 5 months to the start date. For example, if the start date is September 20, 2017, the resulting date is February 20, 2018, If the start date is September 30, 2017, the resulting date is February 28, 2018.

AGG_AVG

Description:Returns the average value of a numeric field matching the query criteria(optional) on the aggregate object from the Source with the group field filtered by the values retrieved from the Source Object.
Use:AGG_AVG(aggregate_object_name, aggregate_field, group_field, group_values_field_on_source_object, [additional_criteria])
Example:Assuming the Source Object Name on the Executable is set as "Account", AGG_AVG("Opportunity", "Amount", "AccountId", "Id", "StageName='Closed Win')caculates the average Amount of the Opportunities grouped by the AccountId field whose Stage is "Closed Win" and the group field AccountId falls into Id values of the Account data retrieved from the Source.
Tips:Aggregate functions in DSP are bulkified, and multiple aggregate functions on the same Executalbe are combined before making SOQL calls to gain the best performance. Using aggregate functions in conjunction with the "Skip Record Update If No Changes?" field to efficiently avoid DMLs for large data calculations.

AGG_COUNT

Description:Returns the number of rows matching the query criteria(optional) on the aggregate object from the Source with the group field filtered by the values retrieved from the Source Object.
Use:AGG_COUNT(aggregate_object_name, aggregate_field_name, [group_field_name, group_field_value,]+ [additional_filters])
Example:Assuming the Source Object Name on the Executable is set as "Account", AGG_COUNT("Opportunity", "Id", "AccountId", Id, "StageName='Closed Win')caculates the number of the Opportunities grouped by the AccountId field whose Stage is "Closed Win" and the group field AccountId falls into Id values of the Account data retrieved from the Source.
Tips:Aggregate functions in DSP are bulkified, and multiple aggregate functions on the same Executalbe are combined before making SOQL calls to gain the best performance. Using aggregate functions in conjunction with the "Skip Record Update If No Changes?" field to efficiently avoid DMLs for large data calculations.

AGG_COUNT_DISTINCT

Description:Returns the number of distinct non-null field values matching the query criteria(optional) on the aggregate object from the Source with the group field filtered by the values retrieved from the Source Object.
Use:AGG_COUNT_DISTINCT(aggregate_object_name, aggregate_field_name, [group_field_name, group_field_value,]+ [additional_filters])
Example:Assuming the Source Object Name on the Executable is set as "Account", AGG_COUNT_DISTINCT("Opportunity", "Type", "AccountId", Id, "StageName='Closed Win')caculates the number of distinct non-null Type values on the Opportunities grouped by the AccountId field whose Stage is "Closed Win" and the group field AccountId falls into Id values of the Account data retrieved from the Source.
Tips:Aggregate functions in DSP are bulkified, and multiple aggregate functions on the same Executalbe are combined before making SOQL calls to gain the best performance. Using aggregate functions in conjunction with the "Skip Record Update If No Changes?" field to efficiently avoid DMLs for large data calculations.

AGG_MAX

Description:Returns the maximum value of a field matching the query criteria(optional) on the aggregate object from the Source with the group field filtered by the values retrieved from the Source Object.
Use:AGG_MAX(aggregate_object_name, aggregate_field_name, [group_field_name, group_field_value,]+ [additional_filters])
Example:Assuming the Source Object Name on the Executable is set as "Account", AGG_MAX("Opportunity", "Amount", "AccountId", Id, "StageName='Closed Win')caculates the max amount of Opportunities grouped by the AccountId field whose Stage is "Closed Win" and the group field AccountId falls into Id values of the Account data retrieved from the Source.
Tips:Aggregate functions in DSP are bulkified, and multiple aggregate functions on the same Executalbe are combined before making SOQL calls to gain the best performance. Using aggregate functions in conjunction with the "Skip Record Update If No Changes?" field to efficiently avoid DMLs for large data calculations.

AGG_MIN

Description:Returns the minimum value of a field matching the query criteria(optional) on the aggregate object from the Source with the group field filtered by the values retrieved from the Source Object.
Use:AGG_MIN(aggregate_object_name, aggregate_field_name, [group_field_name, group_field_value,]+ [additional_filters])
Example:Assuming the Source Object Name on the Executable is set as "Account", AGG_MIN("Opportunity", "Amount", "AccountId", Id, "StageName='Closed Win')caculates the minimum amount of Opportunities grouped by the AccountId field whose Stage is "Closed Win" and the group field AccountId falls into Id values of the Account data retrieved from the Source.
Tips:Aggregate functions in DSP are bulkified, and multiple aggregate functions on the same Executalbe are combined before making SOQL calls to gain the best performance. Using aggregate functions in conjunction with the "Skip Record Update If No Changes?" field to efficiently avoid DMLs for large data calculations.

AGG_SUM

Description:Returns the minimum value of a field matching the query criteria(optional) on the aggregate object from the Source with the group field filtered by the values retrieved from the Source Object.
Use:AGG_SUM(aggregate_object_name, aggregate_field_name, [group_field_name, group_field_value,]+ [additional_filters])
Example:Assuming the Source Object Name on the Executable is set as "Account", AGG_SUM("Opportunity", "Amount", "AccountId", Id, "StageName='Closed Win')caculates the sum amount of Opportunities grouped by the AccountId field whose Stage is "Closed Win" and the group field AccountId falls into Id values of the Account data retrieved from the Source.
Tips:Aggregate functions in DSP are bulkified, and multiple aggregate functions on the same Executalbe are combined before making SOQL calls to gain the best performance. Using aggregate functions in conjunction with the "Skip Record Update If No Changes?" field to efficiently avoid DMLs for large data calculations.

AND

Description:Determines if expressions are true or false. Returns TRUE if all expressions are true. Returns FALSE if any expression is false. Use this function as an alternative to the operator && (AND).
Use:AND(logical1, logical2...) and replace any number of logical references with the expressions you want evaluated.

BASE64_ENCODE

Description:Encode a String value to BASE64 format.
Use:BASE64_ENCODE(string)

BASE64_ENCODE

Description:Encode a string to BASE64.
Use:BASE64_ENCODE(string)

BASE64_DECODE

Description:Decode a BASE64 format to the original string.
Use:BASE64_DECODE(encoding)

BLANK_VALUE

Description:Determines if an expression has a value and returns a substitute expression if it doesn’t. If the expression has a value, returns the value of the expression.
Use:BLANK_VALUE(expression, substitute_expression) and replace expression with the expression you want evaluated; replace substitute_expression with the value you want to replace any blank values.
ExampleExample 1
BLANK_VALUE(Department, “Undesignated”)
This formula returns the value of the Department field if the Department field contains a value. If the Department field is empty, this formula returns the word Undesignated.
Example 2
BLANK_VALUE(Payment_Due_Date__c, StartDate)
This formula returns the contract start date whenever Payment Due Date is blank. Payment Due Date is a custom date field.

CONTAINS

Description:Compares two arguments of text and returns TRUE if the first argument contains the second argument. If not, returns FALSE.
Use:CONTAINS(string, compare_string) and replace text with the string that contains the value of compare_string.
IF(CONTAINS(Product_Type__c, "part"), "Parts", "Service")
This formula checks the content of a custom text field named Product_Type__c and returns Parts for any product with the word “part” in it. Otherwise, it returns Service. Note that the values are case-sensitive, so if a Product_Type__c field contains the text “Part” or “PART,” this formula returns Services.
Tips:This function is case-sensitive so be sure your compare_string value has the correct capitalization.

CONTAINS_IGNORE_CASE

Description:Compares two arguments of text and returns TRUE if the first argument contains the second argument ignoring case. If not, returns FALSE.
Use:CONTAINS_IGNORE_CASE(string, compare_string) and replace text with the text that contains the value of compare_text.
IF(CONTAINS_IGNORE_CASE(Product_Type__c, "part"), "Parts", "Service")
This formula checks the content of a custom text field named Product_Type__c and returns Parts for any product with the word “part” in it(case insensitive). Otherwise, it returns Service. Note that the values are case-sensitive, so if a Product_Type__c field contains the text “Part” or “PART,” this formula returns Services.
Tips:This function is case-insensitive.

DATE

Description:Returns a date value from year, month, and day values you enter.
Use:DATE(year,month,day) and use year with a four-digit year, month with a two-digit month, and day with a two-digit day.
Example:DATE(2005, 01, 02) creates a date field of January 2, 2005.

DAY

Description:Returns the day-of-month component of a Date.
Use:DAY(date/datetime)
Example:DAY(DATE(2024,1,25) returns 25.

DAYS_BETWEEN

Description:Returns a integer value that is the difference between two dates.
Use:DAYS_BETWEEN(date1, date2) 
Example:DAYS_BETWEEN(Birthdate__c, TODAY()) calculates days since the Birthdate__c.

ENDS_WITH

Description:Determines if string ends with specific characters and returns TRUE if it does, case sensitive.
Use:ENDS_WITH(string, compare_string)
Example:IF(ENDS_WITH (Product_type__c, "ICU"), "Medical", "Technical")
This example returns the text Medical if the text in any Product Type custom text field ends with ICU. For all other products, it displays Technical.

ENDS_WITH_IGNORE_CASE

Description:Determines if string ends with specific characters and returns TRUE if it does, case insensitive.
Use:ENDS_WITH_IGNORE_CASE(string, compare_string)
Example:IF(ENDS_WITH_IGNORE_CASE (Product_type__c, "icu"), "Medical", "Technical")
This example returns the text Medical if the text in any Product Type custom text field ends with icu, ignoring case. For all other products, it displays Technical.

ESCAPE_HTML4

Description:Escapes the characters in a String using HTML 4.0 entities. It is equal to Apex String class's escapeHtml4()
Use:ESCAPE_HTML4(string) and replace expression with a text value, merge field, or expression.

ESCAPE_XML

Description:Escapes the characters in a String using XML entities. It is equal to Apex String class's escapeXml()
Use:ESCAPE_XML(string) and replace expression with a text value, merge field, or expression.

HAS_PERMISSION

Description:​​Determines if the current running user has a custom permission in the current org.
Use:HAS_PERMISSION(custom_permission_name)
Example:HAS_PERMISSION("abc") checks if the current user has the custom permission whose name is "abc" in the current org.

IF

Description:​​Evaluate the condition specified in the first argument; if it's true, return the value_if_true; if not, provide the value_if_true instead.
Use:IF(logical_test, value_if_true, value_if_false)
Example:IF(Revenue__c > 10000, "High", "Medium") checks if the Revenue__c of the source record is greater than 1000, if it's true, return "High"; otherwise return "Medium".

INDEX_OF

Description:​​Returns the first index of substring in the full string, case sensitive.
Use:INDEX_OF(string, substring, [index]).
Example:INDEX_OF("abcdbcdefg", "bcd") returns 1.

INDEX_OF_IGNORE_CASE

Description:​​Returns the first index of substring in the full string, case insensitive.
Use:INDEX_OF_IGNORE_CASE(string, substring, [index]).
Example:INDEX_OF_IGNORE_CASE("abcdbcdefg", "BcD") returns 1.

IS_BLANK

Description:​​Determines if an expression has a value and returns TRUE if it does not. If it contains a value, this function returns FALSE.
Use:IS_BLANK(expression) and replace expression with the expression you want evaluated.
Example:IF(IS_BLANK(Maint_Amount__c), 0, 1)

IS_FIRST_IN_BATCH

Description:​​Determines if a field's value of the currently being evaluated record first appears in the batch. Typically this function can be used in the "In Scope Filter" to filter in the scoped source records in a batch, or in the field mappings to conditionally evaluate values.
Use:IS_FIRST_IN_BATCH(field_name) and replace field_name with the field name of the source object.
Example:IS_FIRST_IN_BATCH("Name") returns true if the current source record's Name first appears in the batch.

IS_NUMBER

Description:This function tries to determine if a string value is a decimal by converting it to a Decimal via Decimal.valueOf in APEX, and returns TRUE if it's convertible.
Use:IS_NUMBER(string)
Example:OR(LEN(Bank_Account_Number__c) <> 10, NOT(IS_NUMBER(Bank_Account_Number__c)))

LAST_INDEX_OF

Description:​​Returns the last index of substring in the full string, case sensitive.
Use:LAST_INDEX_OF(string, substring, [end_position]).
Example:LAST_INDEX_OF("abcdbcdefg", "bcd") returns 4.

LAST_INDEX_OF_IGNORE_CASE

Description:​​Returns the last index of substring in the full string, case insensitive.
Use:LAST_INDEX_OF_IGNORE_CASE(string, substring, [end_position]).
Example:LAST_INDEX_OF_IGNORE_CASE("abcdbcdefg", "BcD") returns 4.

LEFT

Description:Returns the specified number of characters from the beginning of a text string.
Use:LEFT(string, num_chars) and replace string with the field or expression you want returned; replace num_chars with the number of characters from the left you want returned.
Example:TRIM(LEFT(LastName, 5)) & "-" & TRIM(RIGHT(SSN__c, 4))
This formula displays the first five characters of a name and the last four characters of a social security number separated by a dash. Note that this example uses a text custom field called SSN.

LEN

Description:Returns the number of characters in a specified text string.
Use:LEN(string) and replace string with the field or expression whose length you want returned.
Example:LEN(PartNumber__c)
This formula returns the number of characters in a Product Code field.

MAP_VALUE_CONTAINS

Description:Evaluate each substring against the value provided in the first parameter, returning the corresponding value if the string includes the substring, or a default value if it does not.
Use:MAP_VALUE_CONTAINS(string, default_return_value, [compare_substring, return_value]+)
Example:MAP_VALUE_CONTAINS(Description, "Business", "Platnium", "Partner", "Individual", "Person") checks the Description field's value, and if it contains "Platnium" returns "Partner", if it contains "Individual", returns "Person", otherwise return "Business".

MAP_VALUE_CONTAINS_IGNORE_CASE

Description:Evaluate each substring against the value provided in the first parameter, case insensitive, and returning the corresponding value if the string includes the substring, or a default value if it does not.
Use:MAP_VALUE_CONTAINS_IGNORE_CASE(string, default_return_value, [compare_substring, return_value]+)
Example:MAP_VALUE_CONTAINS_IGNORE_CASE(Description, "Business", "platnium", "Partner", "individual", "Person") checks the Description field's value, and if it contains "platnium" returns "Partner", if it contains "individual", returns "Person", otherwise return "Business". The string comparison is case insensitive.

MAP_VALUE_EQUALS

Description:Evaluate each compare_value against the value provided in the first parameter, returning the corresponding value if the value equals the compare_string, or a default value if it does not. This function takes any type of parameters and compare uses the "=" sign underneath, other than just strings, which is different than other MAP_VALUE_ functions.
Use:MAP_VALUE_EQUALS(value, default_return_value, [compare_value, return_value]+)
Example:MAP_VALUE_EQUALS(Level__c, "Business", 1, "Partner", 2, "Person") checks the Level__c field's value, and if it equals 1 returns "Partner", if it equals 2, returns "Person", otherwise return "Business".

MAP_VALUE_STARTS_WITH

Description:Evaluate each substring against the value provided in the first parameter, returning the corresponding value if the string starts with the substring, or a default value if it does not.
Use:MAP_VALUE_STARTS_WITH(string, default_return_value, [compare_substring, return_value]+)
Example:MAP_VALUE_STARTS_WITH(Description, "Business", "Platnium", "Partner", "Individual", "Person") checks the Description field's value, and if it starts with "Platnium" returns "Partner", if it starts with "Individual", returns "Person", otherwise return "Business".

MAP_VALUE_STARTS_WITH_IGNORE_CASE

Description:Evaluate each substring against the value provided in the first parameter, case insensitive, and returning the corresponding value if the string starts with the substring, or a default value if it does not.
Use:MAP_VALUE_STARTS_WITH_IGNORE_CASE(string, default_return_value, [compare_substring, return_value]+)
Example:MAP_VALUE_STARTS_WITH_IGNORE_CASE(Description, "Business", "platnium", "Partner", "individual", "Person") checks the Description field's value, and if it starts with "platnium" returns "Partner", if it starts with "individual", returns "Person", otherwise return "Business". The string comparison is case insensitive.

MAX

Description:Returns the highest number from a list of numbers.
Use:MAX(num1, num2,…) and replace number with the fields or expressions from which you want to retrieve the highest number.
Example:Service Charge
MAX(0.06 * Total_Cost__c, Min_Service_Charge__c)
In this example, the formula field calculates a service charge of 6% of the total cost or a minimum service charge, whichever is greater. Note that Min Service Charge is a custom currency field with a default value of $15. However, you could make it a formula field if your minimum service charge is always the same amount.

MIN

Description:Returns the lowest number from a list of numbers.
Use:MIN(num1, num2,…) and replace number with the fields or expressions from which you want to retrieve the lowest number.
Example:401K Matching
MIN(250, Contribution__c /2)
This example formula determines which amount to provide in employee 401K matching based on a matching program of half of the employee's contribution or $250, whichever is less. It assumes you have custom currency field for Contribution.

MONTH

Description:Return the month value of the provided date or datetime value.
Use:MONTH(date/datetime)

NOT

Description:Returns FALSE for TRUE and TRUE for FALSE.
Use:NOT(logical) and replace logical with the expression that you want evaluated.
Example:IF(NOT(Status == "Open"), ClosedDate, ADD_DAYS(CreatedDate, 3)), checks to see if the Status is NOT Open and if so, return the ClosedDate, otherwise return the CreatedDate plus 3 days, as the Expected Close Date.

NOW

Description:Returns a date/time representing the current moment.
Use:NOW()
Example:IF(Status == "Open", ADD_DAYS(CreatedDate, 3)), ClosedDate)
This formula checks to see if the Status is open and if so, return CreatedDate plus 3 days, otherwise return the ClosedDate, as the Expected Close Date.
Tips:
  • Do not remove the parentheses.
  • Keep the parentheses empty. They do not need to contain a value.
  • Use a date/time field in a NOW function instead of a date field. 
  • Use TODAY if you prefer to use a date field.
  • Dates and times are always calculated using the user’s time zone.
  • Use ADD_DAYS to add days to a datetime field.

OR

Description:Determines if expressions are true or false. Returns TRUE if any expression is true. Returns FALSE if all expressions are false. Use this function as an alternative to the operator || (OR).
Use:OR(logical1, logical2...) and replace any number of logical references with the expressions you want evaluated.

RANDOMIZE

Description:Masks the input value randomly based on the data types.
Use:RANDOMIZE(text/number/date/boolean) and replace the value of the expression randomly.
Example:RANDOMIZE(Classified__c) checks the Classified__c characters one by one and replace a number char with a random number char and an English char with a random English char. For example, if the value is “abcd1200”, the result could be “jadj8374”.
RANDOMIZE(SSN__c) checks the SSN__c in characters one by one and replace a number character with a random number. For example, if the SSN__c is 873-98-0000, the result could be “292-77-7312”.
RANDOMIZE(Revenue) checks the Revenue value and replace a random numeric that is less than the Revenue.
RANDOMIZE(Birthdate) checks the Birthdate value and replace a random date that is less than 1000 days prior to the Birthdate. For example, if the Birthdate is 2001-02-01, the result could be 1999-03-25.
RANDOMIZE(Is_From_CA__c) generate a random Boolean value for the Is_From_CA__c.
Tips:Both SCRAMBLE and RANDOMIZE functions cannot be set in the mapping if the target connection is a production. An exception will be thrown if that happens when saving the mappings or during the execution.

RANDOM_ITEM

Description:Return a random item from the provided values in the parameters.
Use:RANDOM_ITEM(item1, item2, item3….)
Example:RANDOM_ITEM("a", "b", "c", "x", "y", "z") returns either "a", "b", "c", "x", "y", "z" randomly.

REPLACE

Description:Substitutes new text for old text in a text string.
Use:REPLACE(string, old_string, new_string)
Example:REPLACE(Name, "Coupon", "Discount") returns the name of an opportunity that contains the term “Coupon” with the opportunity name plus “Discount” wherever the term “Coupon” existed.
REPLACE(Email, LEFT(Email, FIND("@", Email)), "www.") finds the location of the @ sign in a person's email address to determine the length of text to replace with a “www.” as a means of deriving their website address.
Tips:
  • Each term provided in quotes is case-sensitive.
  • If the old_text appears more than once, each occurrence is replaced with the new_text value provided, even when that results in duplicates.

ROUND

Description:Returns the nearest number to a number you specify, constraining the new number by a specified number of digits.
Use:ROUND(number, num_digits) and replace number with the field or expression you want rounded; replace num_digits with the number of decimal places you want to consider when rounding.
Example:ROUND (1.5, 0) = 2
ROUND (1.2345, 0) = 1
ROUND (-1.5, 0) = -2
ROUND (225.49823, 2) = 225.50
Simple DiscountingROUND(Amount-Amount* Discount_Percent__c,2)
Use this formula to calculate the discounted amount of an opportunity rounded off to two digits. This example is a number formula field on opportunities that uses a custom percent field called Discount Percent.
Tips:
  • Enter zero for num_digits to round a number to the nearest integer.
  • DSP automatically rounds numbers based on the decimal places you specify. For example, a custom number field with two decimal places stores 1.50 when you enter 1.49999.
  • DSP uses the round half-up rounding algorithm. Half-way values are always rounded up. For example, 1.45 is rounded to 1.5. –1.45 is rounded to –1.5.
  • The decimal numbers displayed depend on the decimal places you selected when defining the field in the custom field wizard. The num_digits represents the number of digits considered when rounding.

SCRAMBLE

Description:Mix items within the current batch, and pick one value randomly.
Use:SCRAMBLE(value)
Example:SCRAMBLE(FirstName) returns the FirstName of a random record within the execution batch.
Tips:Both SCRAMBLE and RANDOMIZE functions cannot be set in the mapping if the target connection is a production. An exception will be thrown if that happens when saving the mappings or during the execution.

SKIP_ASSIGNMENT

Description:If a target field's mapping evaluates as SKIP_ASSIGNMENT(), DSP skips the assignment and removes the field from the request payload.
Use:SKIP_ASSIGNMENT()
Example:IF(Email == NULL, SKIP_ASSIGNMENT(), SUBSTRING_AFTER(Email, "@")) returns the domain of the Email if Email is not NULL, otherwise skips the assignment.

STARTS_WITH

Description:Determines if text begins with specific characters and returns TRUE if it does. Returns FALSE if it doesn't.
Use:STARTS_WITH(string, compare_string) and replace text, compare_text with the characters or fields you want to compare.
Example:IF(STARTS_WITH (Product_type__c, "ICU"), "Medical", "Technical")
This example returns the text Medical if the text in any Product Type custom text field begins with ICU. For all other products, it displays Technical.

SUBSTRING

Description:Returns a new String that begins with the character at the specified zero-based startIndex and extends to the character at endIndex - 1. It is equal to the Apex: String.substring(startIndex, endIndex)
Use:SUBSTRING(string, start_index, end_index)

STARTS_WITH_IGNORE_CASE

Description:Determines if text begins with specific characters and returns TRUE if it does, ignoring case; Returns FALSE if it doesn't.
Use:STARTS_WITH_IGNORE_CASE(string, compare_string)
Example:IF(STARTS_WITH_IGNORE_CASE(Product_type__c, "icu"), "Medical", "Technical")
This example returns the text Medical if the text in any Product Type custom text field begins with icu, ignoring case. For all other products, it displays Technical.

SUBSTRING

Description:Returns a new String that begins with the character at the specified zero-based startIndex and extends to the character at endIndex - 1. It is equal to the Apex: String.substring(startIndex, endIndex)
Use:SUBSTRING(string, start_index, end_index)

SUBSTRING_AFTER

Description:Returns the substring that occurs after the first occurrence of the specified separator. It is equal to the Apex: String.substringAfter(separator)
Use:SUBSTRING_AFTER(string, separator)

SUBSTRING_AFTER_LAST

Description:Returns the substring that occurs after the last occurrence of the specified separator. It is equal to the Apex: String.substringAfterLast(separator)
Use:SUBSTRING_AFTER_LAST(string, separator)

SUBSTRING_BEFORE

Description:Returns the substring that occurs before the first occurrence of the specified separator. It is equal to the Apex: String.substringBefore(separator)
Use:SUBSTRING_BEFORE(string, separator)

SUBSTRING_BEFORE_LAST

Description:Returns the substring that occurs before the last occurrence of the specified separator. It is equal to the Apex: String.substringBeforeLast(separator)
Use:SUBSTRING_BEFORE_LAST(string, separator)

SUBSTRING_BETWEEN

Description:Returns the substring that occurs between the two specified Strings. It is equal to the Apex: String.substringBetween(open, close)
Use:SUBSTRING_BETWEEN(string, open, close)

TODAY

Description:Returns the current date as a date data type.
Use:TODAY()
Example:DAYSBETWEEN(TODAY(), Sample_date_c) calculates how many days in the sample are left.
Tips:
  • Do not remove the parentheses.
  • Keep the parentheses empty. They do not need to contain a value.
  • Use a date field with a TODAY function instead of a date/time field. Last Activity Date is a date field whereas Created Date and Last Modified Date are date/time fields.
  • See NOW if you prefer to use a date/time field.
  • Dates and times are always calculated using the user’s time zone.

TO_BLOB

Description:Convert a String value to the Apex Blob type. It is equal to the Apex: Blob.valueOf()
Use:TO_BLOB(string)

TO_BOOLEAN

Description:Converts a string value into boolean anywhere formulas are used.
Use:TO_BOOLEAN(string) and replace string with the field or expression you want to convert to boolean format.
Example:Expected Boolean
TO_BOOLEAN("true") returns the expected a boolean value TRUE where the input type is a string.
Tips:
  • If the input value is NULL, the function will return a NULL value instead of FALSE

TO_DATE

Description:Returns a date value for a date/time or text expression.
Use:TO_DATE(string/datetime) and replace expression with a date/time or string value, merge field, or expression.
Example:Closed Date
TO_DATE(ClosedDate) displays a date field based on the value of the Date/Time Closed field.
Date Value
TO_DATE("2005-11-15") returns November 15, 2005 as a date value.
Tips:
  • If the field referenced in the function isn't a valid text or date/time field, the formula field throws an exception.
  • When entering a date, surround the date with quotes and use the following format: YYYY-MM-DD, that is, a four-digit year, two-digit month, and two-digit day.
  • If the expression doesn't match valid date ranges, such as the MM isn't between 01 and 12, an exception will be thrown.
  • Dates and times are always calculated using the user’s time zone.

TO_DATETIME

Description:Returns a datetime value for a text expression.
Use:TO_DATETIME(string) and replace expression with a string value, merge field, or expression.
Example:
TO_DATETIME("yyyy-MM-ddTHH:mm:ss.SSSZ") converts a string value in the format to a Datetime type.An input value example: "2002-10-09T19:00:00Z"

TO_DECIMAL

Description:Converts a text string to a decimal number.
Use:TO_DECIMAL(string) and replace parameter with the field or expression you want converted into a decimal.
Example:
TO_DECIMAL("25.3") converts the string value to the decimal type.

TO_INTEGER

Description:Converts a text string to a integer number.
Use:TO_INTEGER(string/decimal/double/float/integer) and replace parameter with the field or expression you want converted into an integer.
Example:
TO_INTEGER("25") converts the string value to the integer type.
Tips:
  • If the input is a string value, the string value must represent an integer.
  • If the input is a decimal, double, float or integer, the result will be the integer part of the input value.

TO_LOWER_CASE

Description:Converts all letters in the specified text string to lowercase. Any characters that are not letters are unaffected by this function. Locale rules are applied if a locale is provided.
Use:TO_LOWER_CASE(string, [locale]) and replace string with the field or text you wish to convert to lowercase, and locale with the optional two-character ISO language code or five-character locale code, if available.
Example:MYCOMPANY.COM
TO_LOWER_CASE("MYCOMPANY.COM") returns “mycompany.com.”
Applying Turkish Language Locale Rules
The Turkish language has two versions of the letter “i”: one dotted and one dotless. The locale rules for Turkish require the ability to capitalize the dotted i, and allow the dotless I to be lowercase. To correctly use the TO_LOWER_CASE() function with the Turkish language locale, use the Turkish locale code tr in the TO_LOWER_CASE() function as follows:
TO_LOWER_CASE(text, "tr")

TO_STRING

Description:Converts a percent, number, date, date/time, or currency type of value into text anywhere formulas are used. Also, converts picklist values to text in approval rules, approval step rules, workflow rules, escalation rules, assignment rules, auto-response rules, validation rules, formula fields, field updates, and custom buttons and links.
Use:TO_STRING(value) and replace value with the field or expression you want to convert to text format. Avoid using any special characters besides a decimal point (period) or minus sign (dash) in this function.
Example:Expected Revenue in Text
TO_STRING(ExpectedRevenue) returns the expected revenue amount of an opportunity in text format without a dollar sign. For example, if the Expected Revenue of a campaign is "$200,000," this formula calculates “200000.”
Tips:
  • The returned text is not formatted with any currency, percent symbols, or commas.
  • Values are not sensitive to locale. For example, 24.42 EUR is converted into the number 24.42.
  • Percents are returned in the form of a decimal.
  • Dates are returned in the form of YYYY-MM-DD, that is, a four-digit year and two-digit month and day.
  • Date/time values are returned in the form of YYYY-MM-DD HH:MM:SSZ where YYYY is a four-digit year, MM is a two-digit month, DD is a two-digit day, HH is the two-digit hour, MM are the minutes, SS are the seconds, and Z represents the zero meridian indicating the time is returned in UTC time zone.

TO_TIME

Description:Returns a datetime value for a text expression.
Use:TO_TIME(string) and replace expression with a string value, merge field, or expression.

TO_UPPER_CASE

Description:Converts all letters in the specified text string to uppercase. Any characters that are not letters are unaffected by this function. Locale rules are applied if a locale is provided.
Use:TO_UPPER_CASE(string, [locale]) and replace string with the field or text you wish to convert to uppercase, and locale with the optional two-character ISO language code or five-character locale code, if available.

TRIGGER_FLIPPER

Description:Flip the assigned boolean field to a default value in a before insert/update trigger and track it in memory, if the default_value is different than the field's current value or evaluated_value if provided.
Use:TRIGGER_FLIPPER(default_value, [evaluated_value]) flips the assigned boolean field to a default value and tracks the flipness in the memory; this makes sure the field is always set to a default value during save, and the TRIGGER_IS_FLIPPED can be used to determine whether the field was flipped or not later.
Example:TRIGGER_FLIPPER(false, Priority == "Urgent" && RecordType.DeveloperName == "Gold_Customer_Case") returns false(default_value) and if the assigned field's value was the opposite - true or the record meets the criteria - Priority is "Urgent" and the RecordType's DeveloperName is "Gold_Customer_Case", DSP will track it in memory, and the method TRIGGER_IS_FLIPPED(field_name) will be able to tell whether the field was flipped or not. These two functions are particularly useful when designing capability oriented automations, where having a boolean field such as "Create_Task__c", when it is checked, flip it back to false so that after every save, it is always set to false as default value; but if it was checked for whatever reasons, fire the Create Task automation after checking TRIGGER_IS_FLIPPED("Create_Task__c") returns true.

TRIGGER_IS_CHANGED

Description:Checks the provided field_names, if any field is changed in the update DML operation, returns true.
Use:TRIGGER_IS_CHANGED(field_name...)
Example:TRIGGER_IS_CHANGED("Type", "Amount") returns true if the record is updated and either Type or Amount field is changed.

TRIGGER_IS_CHANGED_FROM

Description:Checks the provided field_name, if the field is changed in the update DML operation, and the old value is among one of the provided old_value, returns true.
Use:TRIGGER_IS_CHANGED_FROM(field_name, old_value...)
Example:TRIGGER_IS_CHANGED_FROM("Type", "Customer", "Partner") returns true if the record is updated and the Type is changed from either "Customer" or "Partner".

TRIGGER_IS_CHANGED_TO

Description:Checks the provided field_name, if the field is changed in the update DML operation, and the old value is among one of the provided old_value, returns true.
Use:TRIGGER_IS_CHANGED_TO(field_name, new_value...)
Example:TRIGGER_IS_CHANGED_TO("Type", "Vendor", "Partner") returns true if the record is updated and the Type is changed to either "Vendor" or "Partner".

TRIGGER_IS_FLIPPED

Description:Checks if the provided field_name was flipped by the TRIGGER_FLIPPER function in a before insert/update trigger Executable prior to this evaluation.
Use:TRIGGER_IS_FLIPPED(field_name)
Example:TRIGGER_IS_FLIPPED("IsActive__c") returns true if the IsActive__c field was flipped to a default_value in a before trigger prior to the current evaluation.

TRIGGER_OLD_VALUE

Description:Returns the old value of the specified field in an update trigger.
Use:TRIGGER_OLD_VALUE(field_name)

TRIM

Description:Removes the spaces and tabs from the beginning and end of a text string.
Use:TRIM(string) and replace text with the field or expression you want to trim.
Example:TRIM(LEFT(LastName,5))& "-" & RIGHT(FirstName, 1) returns a network ID for users that contains the first five characters of their last name and first character of their first name separated by a dash.

VALUE_IN

Description:Checks if a value is equal to any of the compare_values.
Use:VALUE_IN(value, compare_value...)
Example:VALUE_IN(SUBSTRING_AFTER(Email, "@"), "nvidia.com", "amd.com", "tesla.com") returns true if the email's domain equals to either "nvidia.com", "amd.com" or "tesla.com".

VLOOKUP

Description:Returns a value by looking up a related value on a custom object similar to the VLOOKUP() Excel function.
Use:VLOOKUP(lookup_object_name, return_field_name, [lookup_field_name, lookup_field_value,]+ [additional_clause]) 
Replace field_name_to_return with the field that contains the value you want returned, lookup_field_name with the field name on the related object that contains the value you want to match, and lookup_field_value defines the field’s value that is extracted from the retrieved source data you want to match. additional_clause is optional, if provided, DSP add addtional SOQL clause to the generated vlookup SOQL query.
Example:VLOOKUP("Contact", "Account.AccountNumber", "Email", Primary_Email__c, "RecordType.DeveloperName = 'Customer'") tries to lookup the Account relationship's Account Number of the matched Contact by checking the Primary_Email__c of the source data against the Contact's Email field, with the addtional critiria where the RecordType is "Customer", in the target connection.

YEAR

Description:Returns the year value of a date or datetime value.
Use:YEAR(date/datetime)

APEX CLASS

If all the above operators and functions can not meet your needs of transformation, you can also build your own logic in apex class by implmenting the package interface pushtopics.TargetValueMapper, and adding the expression {!yourApexClassName} to the "Map with Source" field. Two methods are required to be implemented.

srcFields()

This method tells DSP to make sure the fields used in the Apex class is added to the SOQL string when querying against the source org.

getTargetFieldValue

This method is where the custom logic is implemented to generage the target field's value based on the srcRecod and current fieldMapping record as input parameters.

Below is an example of using custom Apex class to implement the transformation.

    
    /*
    * This class is an example of using a field value(Source_Record_Type_Name__c) 
    * from the source record to get the Record Type Id in the current org.
    * 
    */
   public class AccountRecordTypeFinder implements pushtopics.TargetValueMapper{

       private static Map<String, Schema.RecordTypeInfo> currentOrgAccountRecordTypesMap = 
           Schema.SObjectType.Account.getRecordTypeInfosByName();

       public String[] srcFields(){

           return new String[]{'Source_Record_Type_Name__c'};
       }

       public Object getTargetFieldValue(Map<String, Object> srcRecord, 
                                         pushtopics__FieldMapping__c fieldMapping){

           String srcRecordTypeName = (String)srcRecord.get('Source_Record_Type_Name__c');

           Schema.RecordTypeInfo currentOrgRecordType = 
               currentOrgAccountRecordTypesMap.get(srcRecordTypeName);

           return currentOrgRecordType?.getRecordTypeId();
       }
   }