Formula Operators and Functions
Working with formulas. Find out which operators can be used in which formulas and what each one does.
|+(Add)||Calculates the sum of two values.|
|-(subtract)||Calculates the difference of two values.|
|*(Multiply)||Multiplies its values.|
|/(Divide)||Divides its values.|
|^(Exponentiation)||Raises a number to a power of a specified number.|
|( ) (Open Parenthesis|
and Close Parenthesis)
|Specifies that the expressions within the open parenthesis and close parenthesis are evaluated first. All other expressions are evaluated using standard operator precedence.|
= and == (Equal)
|Evaluates if two values are equivalent. The = and == operator are interchangeable.|
|< > and != (Not Equal)||Evaluates if two values are not equivalent.|
|< (Less Than)||Evaluates if a value is less than the value that follows this symbol.|
|> (Greater Than)||Evaluates if a value is greater than the value that follows this symbol.|
|<= (Less Than or Equal)||Evaluates if a value is less than or equal to the value that follows this symbol.|
= (Greater Than or Equal)
|Evaluates if a value is greater than or equal to the value that follows this symbol.|
|Evaluates if two values or expressions are both true. Use this operator as an alternative to the logical function AND.|
|Evaluates if at least one of multiple values or expressions is true. Use this operator as an alternative to the logical function OR.|
|CONCAT||CONCAT(text1,text2) Join text1 with text2|
Date and Time Functions
|MONTH||MONTH(date) Returns the month, a number between 1 (January) and 12 (December)|
|DATE(Long timestamp, Boolean isGMT) converts timestamp(millisecond) to a Date, If isGMT=true returns a date in the GMT time zone. Else returns a date in the local time zone of the current user. If the time zone cannot be determined, GMT is used.|
|DATEADD(date, nbOfDays) return a new date which the specified nb of days has been added/substracted to/from the date if the nb of days is positive/negative respectively. date must be in the format: "dd.mm.yyyy" | "dd-mm-yyyyy" | "dd/mm/yyyy" | "yyyymmdd" and nbOfDays is numeric Eg: DATEADD("10.10.2010",30); DATEADD("10.10.2010", -30)|
|DATA DIFF||DATEDIFF(date1Text, date2Text) return the number of days between two dates. If the date1 occurs after the date2, the return value is negative. date1 and date2 text must be in the format: "dd.mm.yyyyy" | "dd-mm-yyyy" | "dd/mm/yyyy" | "yyyymmdd" Eg: DATEDIFF("10.10.2010",TODAY()); DATEDIFF("10.10.2011″,"31.10.2011")|
|DATE||DATETIME(Long timestamp, Boolean isGMT) converts timestamp(millisecond) to a Datetime, If isGMT=true returns a datetime in the GMT time zone. Else returns a datetime in the local time zone of the current user. If the time zone cannot be determined, GMT is used.|
|DAY||DAY("YYYYYMMDD") Returns the day of the month, a number between 1 and 31|
|CDATE||CDATE(dateText, dateFormat) Convert dateText to a Date based on a specified date format. dateFormat is ["x.y.z" | "x-y-z" | "x/y/z"] where [x,y,z] are: "d" for day; "m" for month; "y" for year. For example : CDATE("12/31/2010", "m/d/y")|
|CDATETIME||CDATETIME(datetimeText, dateFormat, isGMT[true/false]) Convert datetimeText to a DateTime based on a specified dateFormat and in GMT time zone if isGMT true, else in local one. dateFormat is ["x.y.z"" | "x-y-z" | "x/y/z"] where [x,y,z] are: "d" for day; "m" for month; "y" for year. Eg: CDATETIME("12/31/2010 12:00:00", "m/d/y", true)|
|TODAY||TODAY([integer]) Returns the current date if integer is optional, else return current date +/- integer|
|YEAR||YEAR(date) Returns the year of a date|
|CASE||CASE(expression, value1, result1, value2, result2,...,else_result) Checks an expression against a series of values. If the expression compares equal to any value, the corresponding result is returned. If it is not equal to any of the values, the else-result is returned|
|IF||IF(logical_test, value_if_true, value_if_false) Checks whether a condition is true , and returns one value if TRUE and another value if FALSE|
|ISNULL||ISNULL(expression) Checks whether an expression is null (blank) and returns TRUE or FALSE|
|NOT||NOT(boolean) Changes FALSE to TRUE or TRUE to FALSE|
|EMERGENCY||NOTHING() Returns the nothing value|
|BEGINNING||BEGINS(text,compare_text) Checks if text begins with specified characters and returns TRUE if it does. Otherwise returns FALSE|
|LEN||LEN(text) Returns the number of characters in a text string|
|LOWER||LOWER(text) Converts all letters in the value to lowercase|
|LPAD||LPAD(text, padded_length [, pad_string]) Pad the left side of the value with spaces or the optional pad string so that the length is padded_length|
|MAX||MAX(number,number,...) Returns the greatest of all the arguments|
|MID||MID(text, start_num, num_chars) Returns character from the middle of a text string, given a starting position and length|
|MIN||MIN(number,number,...) Returns the least of all the arguments|
|REPLACE||REPLACE(text, old_text, new_text) Substitutes new_text for old_text in a text string. Use REPLACE when you want to replace specific text in a text string|
|RIGHT||RIGHT(text, num_chars) Returns the specified number of characters from the end of a text string|
|RPAD||Right side of the value with spaces or the optional pad string so that the length is padded_length|
|SUBSTITUTE||SUBSTITUTE(text, old_text, new_text) Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string|
|SUBSTR||SUBSTR(text,startIndex,endIndex) Get a substring from a string text from an index to an index|
|TRIM||TRIM(text) Removes all spaces from a text string except for single spaces between words|
|UPPER||UPPER(text) Converts all letters in the value to uppercase|
|LEFT||LEFT(text, num_chars) Returns the specified number of characters from the start of a text string|
|LASTWORD||LASTWORD(text) Returns the last word in the specified text. For example: LASTWORD("Toto Tata Titi") return Titi|
|LASTINDEXOF||LASTINDEXOF(Text, compareText) Returns the last index of a specified text in a Text|
|CONTAINS(text, compare_text) Checks if text contains specified characters, and returns TRUE if it does. Otherwise, returns FALSE|
|FIND||FIND(text, search_text [, start_num]) Returns the position of the search_text string in text|
|ENDS||ENDS(text,compare_text) Checks if text ends with specified characters and returns TRUE if it does. Otherwise returns FALSE|
|REPLACELINEBREAK||REPLACELINEBREAK(text, replacement) Replaces each line break in text with the specified literal replacement|
|VLOOKUP||VLOOKUP(Standard/CustomObjectName,FieldToRetrieve,FieldCondition1,Condition1,FieldCondition2,Condition2,...) Retrieve a field value from custom/standard object|
|APEXCLASS||APEXCLASS("ApexClassName") The ApexClassName must be surrounded by double quotes. this class must be extended and and override the method execute of base class IFormulaBase. For example: APEXCLASS("YourApexClass") return text|
|GETCURRENTUSERNAME||GETCURRENTUSERNAME() Returns the context user's full name. The format of the name depends on the language preferences specified for the organization|
|GETCURRENTUSERID||GETCURRENTUSERID() Returns the context user's ID|
|DEFAULT RECORD TYPE NAME||DEFAULTRECORDTYPENAME(sObjectName) Returns the name of default record type of sObject|
|DEFAULT RECORD TYPEID||DEFAULTRECORDTYPEID(sObjectName) Returns the ID of default record type of sObject|
Commonly used formula, with Examples
Result in the target field
|"Target||Always provides the word ?Example? in the target field||example|
|Field1 = ?A?||Field1 & Field2||Unites the content of two fields||AB|
|Field1 = ?A?|
Field2 = ?B?
|Field1 &?? & Field2||Unites the content of two fields with spaces||A B|
|Active_Product = ?0?, ?1?, ?2?||CASE (Active_Product, ??1??, true, NOTHING())||If Active_Product = 1, indicates the value ?true?; otherwise does not display any value||0 = ?? 1 = ?true? 2 = ??|
|DATE_Field = ?05.08.2018 09:51:40?||IF(ISNULL(DATE) | LEN(DATE)<19,NOTHING(),||Converts the data format||05-08-2018|
|DATE_Field= ?01/05/2018?||Converts the data format||05.01.2018|
|VAT = ?19? TURNOVER = ?300?||(VAT/100)*TURNOVER||Calculates turnover tax (57?) on the basis of the turnover (300?) and the VAT rate (19%)||57|
|Pick list key = ?1?,?2?,?3?||CASE(?A?, "1", "B", "2","C", "3", other)||Converts the Foreign Keys from external picklists into Text for picklists in Salesforce.||1 = ?A?|
2 = ?B?
3 = ?C?
else = ?other?
|Pick list key = ?1?,?2?,?3?||CASE(?A?, "1","B", "2","C"", "3","D","4″, NOTHING())||Converts the Foreign Keys from external picklists into Text for picklists in Salesforce.||1 = ?A?|
2 = ?B?
3 = ?C?
else = ??
|1234567890||LEFT(TEXT,5)||Reproduces only the left 5 characters||12345|
|1234567890||RIGHT(TEXT,5)||Reproduces only the right 5 characters||67890|
|1.000.000,0||IF(CONTAINS(KLIMK,"."), SUBSTITUTE(SUBSTITUTE(KLIMK, ".", ""),",","."),KLIMK)||Removes the separation character commas and replaces the decimal point separation char||1000000.00|
|Source_No = 512||VLOOKUP (Account, ID, Client_Nr__c, Source_Nr)||Adds the Salesforce ID of the Account to the target field whose Client No__c is consistent with the Source No (512)||Lookup Account ID = ?0012000000IL6xiAAD?|
|Source_Email = ?email@example.com?||VLOOKUP (User, ID, email, Source_Email)||Adds the Salesforce ID of the user into the target field whose email address is consistent with with the Source_Email (firstname.lastname@example.org)||Lookup User ID = ?0012000000IL6xiAAD?|
IF(logical_test, value_if_true, value_if_false)
Checks whether a condition is true , and returns one value if TRUE and another value if FALSE
IF with String
IF(language__c = "German", 'D', 'E')
IF(ISNULL(SALESFORCEID), VLOOKUP(Lead, Id, SAP_ID_REF__c, E101STRUC_IDENTIFICATIONKEY#IDENTIFICATIONCATEGORY:Z_SF#.IDENTIFICATIONNUMBER ) SALESFORCEID)
Get a substring from a string text from an index to an index
REPLACE(text, old_text, new_text)
Substitutes new_text for old_text in a text string. Use REPLACE when you want to replace specific text in a text string
someone who wants to map the description field of an object. He gets a ";" as sign for a line break. Now he wants to replace the ";" with a break. What's the best way to do this?
REPLACE(DESCRIPTION, ";"", " " ")
Convert dateText to a Date based on a specified date format. dateFormat is ["x.y.z" | "x-y-z" | "x/y/z"] where [x,y,z] are: "d" for day; "m" for month; "y" for year. For example : CDATE("12/31/2010", "m/d/y")
Integration can support only authorized format date : dd.mm.yyyy or dd-mm-yyyy or yyyymmdd or dd/mm/yyyy
if customer has data in format date like 2018-20-8, I suggest using function CDATE(XXXX, "y-d-m")
i.e.: CDATE("2011-20-9", "y-d-m")
DATE(Long timestamp, Boolean isGMT)
converts timestamp(millisecond) to a Date, If isGMT=true returns a date in the GMT time zone. Otherwise returns a date in the local time zone of the current user. If the time zone cannot be determined, GMT is used
DATE("1292948797", false) => result: 16.01.1970 in format (dd.MM.yyyy) in local time zone of current user.
DATE("1292948797", true)=>15.01.1970 in GMT time zone.
Converts dateText to a Date based on a specified date format. dateFormat is ["x.y.z" | "x-y-z" | "x/y/z"] where [x,y,z] are: "d" for day; "m" for month; "y" for year. For example : CDATE("12/31/2010", "m/d/y")
The following are the date formats which are supported by our tools:
d/m/y or d-m-y or d.m.y.
If your date is in format 31/12/2012, the formula to convert date must be:
target Source -> ClosedDate CDATE("31/12/2012", "d/m/y")
target Source -> ClosedDate CDATE("12/31/2012", "m/d/y")
And if the date is in format 31-12-2012 : ClosedDate <–>
More on VLOOKUP and Best Practices
Before you commence your first integration operation you should observe the following basis for file migration and integration:
1) Parent / child objects must be created using a logical sequence
A contact (child) should have access to an account (parent). In order to assign a contact to an account, the account must exist in the system prior to the contact.
2) Parent / child objects must be connected with primary and foreign keys
For each customer / account (child), there is a designated account owner / user (parent). To import a list of customers, a key which explicitly identifies the Account Owner is required.
An example of the sequence during the creation of records and their relationship to each other:
One way to do mapping is to use the mapping tool at the interface detail page to perform mapping for the interface integration. Drag and Drop from Source Definitions to sObject Field
For Account mapping sheet, do mappings as shown in the figure:
You can do formula mapping by (see the above figure)
- Ticking Formula at a mapping record in the Data Mapping table.
- Clicking the record row
Do as instructed in the above figure
How can I put some logic in the mapped fields?
- If you select the mapped field and tick "Formula" and click again in the selected row, the formula screen appears.
Below you will find a couple of examples of how VLOOKUP can be used with various objects in order to establish connections with other objects. Ensure that each object has a Primary Key (indicated with "*"), so that this object can be referenced.
Owner ID: VLOOKUP(User,Id,Alias,Alias_Sourcefile)
Owner ID VLOOKUP(Account,OwnerID,Ext_ID_Account__c,Ext_ID_SourcefileA)
Account ID VLOOKUP(Account,ID,Ext_ID_Account__c, Ext_ID_SourcefileA)
OwnerID VLOOKUP(Account,OwnerID,Ext_ID_Account__c, Ext_ID_SourcefileA)
Account Id VLOOKUP(Account, ID, Ext_ID_Account__c, Ext_ID_SourcefileA)
Contact ID VLOOKUP(Contact,ContactID,Ext_ID_Contact__c,Ext_ID_SourcefileK)
Opportunity Line Item
In the Standard Price Book, the price for each product is listed as the Sales Price.
The Quantity and Discount are then applied and define the Total Price.
- Formula which calculates the Total Price based on Sales Price, Quantity and Discount
Quantity*VLOOKUP(PricebookEntry, UnitPrice, ProductCode, Product_Number)/100*(100-Discount)
- Formula which retrieves the PricebookEntry from the Standard Price Book
VLOOKUP(PricebookEntry, Id, ProductCode, Product_Number)
IF(VLOOKUP(Pricebook2, Name, Name, PricebookName)="Standard Price Book", VLOOKUP(Pricebook2, Id, Name, PricebookName), VLOOKUP(Pricebook2, Id, Name, "Standard Price Book"))
Add apex class to use as formula
This feature enables user extends function of mapping formula to execute their apex class. Sometime, standard formula cannot handle the complex business logic. So user can write the apex script to execute the business logic.
The apex class must be extends skyvva's based class
- public override String execute(Map
record): execute when processing mapping. User can define the formula based on data in map record and return their result as text (of Number, Date, DateTime, Id) is required by integration.
- public override void preExecute(List: execute before execute(...) and processing mapping, It is optional. But important in case execute query statement to search existing record in salesforce and cached result for using accordingly.
Key of map (record):
- lower-case for InBound
- selected Field name (case-sensitive) For OutBound
Does not execute query in method execute(...) because it is called when processing mapping every message record data, It can exceed the query limit in salesforce. Please override the method preExecute(...) then query and cached the result for using in execute(...).
Invoke apex class with formula APEXCLASS("SkyvvaCustomFormula"): The apex class must be surrounded by double quotes.
Sample Apex Class:
This apex query all accounts based on ERP_DEBTOR_ID number to get each account id to map to target field AccountId for contact.
public override void preExecute(List: Queries all accounts in salesforce base on ERP_DEBTOR_ID's values and caches result in Map.
We listen. Over the last decade, we've built our business by meeting organizations where they are-we solve the problems that matter to you, regardless of how simple or complex they may be.