Tutorial V2.41 (Lightning)

  1. Home
  2. Docs
  3. Tutorial V2.41 (Lightning)
  4. 36. Formular reference guide?

36. Formular reference guide?

Formula Operators and Functions

Summary

Working with formulas. Find out which operators can be used in which formulas and what each one does.

           Math Operators        

Operator

Description

+(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.

Logical Operators

Operator

Description

 

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

&& (AND)

Evaluates if two values or expressions are both true. Use this operator as an alternative to the logical function AND.
 

|| (OR)

Evaluates if at least one of multiple values or expressions is true. Use this operator as an alternative to the logical function OR.

Text Operators 

Operator

Description

CONCATCONCAT(text1,text2) Join text1 with text2

Date and Time Functions

Operator

Description

MONTHMONTH(date) Returns the month, a number between 1 (January) and 12 (December)
 

DATE

 

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

 

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-yyyy” | “dd/mm/yyyy” | “yyyymmdd” and nbOfDays is numeric Eg: DATEADD(“10.10.2010”,30); DATEADD(“10.10.2010”, -30)

 

DATEDIFFDATEDIFF(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.yyyy” | “dd-mm-yyyy” | “dd/mm/yyyy” | “yyyymmdd” Eg: DATEDIFF(“10.10.2010”,TODAY()); DATEDIFF(“10.10.2011″,”31.10.2011”)
DATETIMEDATETIME(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.
DAYDAY(“YYYYMMDD”) Returns the day of the month, a number between 1 and 31
CDATECDATE(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”)
CDATETIMECDATETIME(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)
TODAYTODAY([integer]) Returns the current date if integer is optional, else return current date +/- integer
YEARYEAR(date) Returns the year of a date

Logical Functions

Operator

Description

CASECASE(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
IFIF(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
ISNULLISNULL(expression) Checks whether an expression is null (blank) and returns TRUE or FALSE
NOTNOT(boolean) Changes FALSE to TRUE or TRUE to FALSE
NOTHINGNOTHING() Returns the nothing value

Text Functions

Operator

Description

BEGINSBEGINS(text,compare_text) Checks if text begins with specified characters and returns TRUE if it does. Otherwise returns FALSE
LENLEN(text) Returns the number of characters in a text string
LOWERLOWER(text) Converts all letters in the value to lowercase
LPADLPAD(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
MAXMAX(number,number,…) Returns the greatest of all the arguments
MIDMID(text, start_num, num_chars) Returns character from the middle of a text string, given a starting position and length
MINMIN(number,number,…) Returns the least of all the arguments
REPLACEREPLACE(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
RIGHTRIGHT(text, num_chars) Returns the specified number of characters from the end of a text string
RPADRight side of the value with spaces or the optional pad string so that the length is padded_length
SUBSTITUTESUBSTITUTE(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
SUBSTRSUBSTR(text,startIndex,endIndex) Get a substring from a string text from an index to an index
TRIMTRIM(text) Removes all spaces from a text string except for single spaces between words
UPPERUPPER(text) Converts all letters in the value to uppercase
LEFTLEFT(text, num_chars) Returns the specified number of characters from the start of a text string
LASTWORDLASTWORD(text) Returns the last word in the specified text. For example: LASTWORD(“Toto Tata Titi”) return Titi
LASTINDEXOFLASTINDEXOF(Text, compareText) Returns the last index of a specified text in a Text
CONTAINS

 

CONTAINS(text, compare_text) Checks if text contains specified characters, and returns TRUE if it does. Otherwise, returns FALSE
FINDFIND(text, search_text [, start_num]) Returns the position of the search_text string in text
ENDSENDS(text,compare_text) Checks if text ends with specified characters and returns TRUE if it does. Otherwise returns FALSE
REPLACELINEBREAKREPLACELINEBREAK(text, replacement) Replaces each line break in text with the specified literal replacement

Advanced Functions

VLOOKUPVLOOKUP(Standard/CustomObjectName,FieldToRetrieve,FieldCondition1,Condition1,FieldCondition2,Condition2,…) Retrieve a field value from custom/standard object
APEXCLASSAPEXCLASS(“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
GETCURRENTUSERNAMEGETCURRENTUSERNAME() Returns the context user’s full name. The format of the name depends on the language preferences specified for the organization
GETCURRENTUSERIDGETCURRENTUSERID() Returns the context user’s ID
DEFAULTRECORDTYPENAMEDEFAULTRECORDTYPENAME(sObjectName) Returns the name of default record type of sObject
DEFAULTRECORDTYPEIDDEFAULTRECORDTYPEID(sObjectName) Returns the ID of default record type of sObject

Commonly used formula, with Examples

SourceField=Content

Formula

Description

Result in the target field

 ?Target?Always provides the word ?Example? in the target fieldExample
Field1 = ?A?Field1 & Field2Unites the content of two fieldsAB
Field1 = ?A?

Field2 = ?B?

Field1 &?? ??& Field2Unites the content of two fields with spacesA 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 value0 = ?? 1 = ?true? 2 = ??
DATE_Field = ?05.08.2018 09:51:40?IF(ISNULL(DATE) | LEN(DATE)<19,NOTHING(), SUBSTR(DATE,8,10) &”-“& SUBSTR(DATE,5,7) &”-“& SUBSTR(DATE,0,4))Converts the data format05-08-2018
DATE_Field= ?01/05/2018? Converts the data format05.01.2018
VAT = ?19? TURNOVER = ?300?(VAT/100)*TURNOVERCalculates 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 = ??

1234567890LEFT(TEXT,5)Reproduces only the left 5 characters12345
1234567890RIGHT(TEXT,5)Reproduces only the right 5 characters67890
1.000.000,0IF(CONTAINS(KLIMK,”.”), SUBSTITUTE(SUBSTITUTE(KLIMK, “.”, “”),”,”,”.”),KLIMK)Removes the separation character commas and replaces the decimal point separation char1000000.00
Source_Nr = 512VLOOKUP (Account, ID, Client_Nr__c, Source_Nr)Adds the Salesforce ID of the Account to the target field whose Client-Nr__c is consistent with the Source_Nr (512)Lookup Account ID = ?0012000000IL6xiAAD?
Source_Email = ?user@skyvva.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 (user@skyvva.com)Lookup User ID = ?0012000000IL6xiAAD?

Examples:

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

IF

IF with String

IF(language__c = “German”, ‘D’, ‘E’)

Logic

IF(ISNULL(SALESFORCEID), VLOOKUP(Lead, Id, SAP_ID_REF__c, E101STRUC_IDENTIFICATIONKEY#IDENTIFICATIONCATEGORY:Z_SF#.IDENTIFICATIONNUMBER ) , SALESFORCEID)


SUBSTR:

SUBSTR(text,startIndex,endIndex)

Get a substring from a string text from an index to an index

Example:

SUBSTR(“20120809”,6,8)&SUBSTR(“20120809”,4,6)&SUBSTR(“20120809”,0,4)

Output: 09082012


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

Example:

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, “;”, ” “)


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


Example:

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

Output: 20.08.2018


DATE:

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

Example:

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.


CDATE:

CDATE(dateText, dateFormat)

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

or

target Source -> ClosedDate CDATE(“12/31/2012”, “m/d/y”)

And if the date is in format 31-12-2012 : ClosedDate <–>

CDATE(“31-12-2012”, “d-m-y”)

 


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

E.g.:

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

E.g.:

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:

Note: for each object, it is required to have an External Id field which is to be used for synchronization with external system. This external id field must be unique in order to avoid duplicate records on each object.

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.

Examples:

User

Alias*                                                           <= Alias_Sourcefile

Account

Ext_ID_Account__c*                             <= Ext_ID_SourcefileA

SourceField                                          Target

Owner ID:                                                  VLOOKUP(User,Id,Alias,Alias_Sourcefile)

Contact

Ext_ID_Contact__c*                             <= Ext_ID_SourcefileK (alternative email address)

SourceField                                         Target

Owner ID                                                   VLOOKUP(Account,OwnerID,Ext_ID_Account__c,Ext_ID_SourcefileA)

Account ID                                                VLOOKUP(Account,ID,Ext_ID_Account__c, Ext_ID_SourcefileA)

Opportunity

Ext_ID_Opportunity__c*                     <= Ext_ID_SourcefileO

SourceField                                         Target

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

Target:

TotalPrice

Source:

Quantity*VLOOKUP(PricebookEntry, UnitPrice, ProductCode, Product_Number)/100*(100-Discount)

  • Formula which retrieves the PricebookEntry from the Standard Price Book

Target:

PricebookEntry

Source:

VLOOKUP(PricebookEntry, Id, ProductCode, Product_Number)

StandardPricebookEntry

Target:

Pricebook2Id

Source:

IF(VLOOKUP(Pricebook2, Name, Name, PricebookName)=”Standard Price Book”, VLOOKUP(Pricebook2, Id, Name, PricebookName), VLOOKUP(Pricebook2, Id, Name, “Standard Price Book”))

Contact

Target:   AccountId

Source: VLOOKUP(Account,Id,Name,Customer_Name)

ADVANCE FEATURE

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

<skyvvasolutions.IFormulaBase> and override some methods:

  • public override String execute(Map<String,String> 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<Map<String,String>> records): 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

Warning! :

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.

Mapping

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<Map<String,String>> records): Queries all accounts in salesforce base on ERP_DEBTOR_ID’s values and caches result in Map.

public override String execute(Map<String,String> record): Get account id from cached and return the id.

globalwith sharing class SkyvvaCustomFormula extends skyvvasolutions.IFormulaBase{
//cache of account Id
Map<String,String> erpIdAccountId=new Map<string,String>();
/*
Query accounts based on ERP_DEBTOR_ID and ceche the result in map  erpIdAccountId
@param records:      Collection of map data of every messages
i.e: list of map record records
{email=test1@gmail.com,  erp_debtor_id=RES001,  firstname=test1,  lastname=test1}
{email=test2@gmail.com,  erp_debtor_id=RES002,  firstname=test2,  lastname=test2}
*/
public override void preExecute(List<Map<String,String>> records){
//System.debug(‘>SkyvvaCustomFormula.preExecute:message-records:’+records.size());
//add value of erp_debtor_id into set
Set<String> erpIds=new Set<String>();
for(Map<String,String> m: records){
       //key must be lower case
       String erpId=m.get(‘erp_debtor_id’);
       if(String.isNotBlank(erpId))erpIds.add(erpId);
}
//System.debug(‘>SkyvvaCustomFormula.preExecute: Set erpIds:’+erpIds);
//>Set erpIds:{RES001, RES002}
//Query accounts based on set erpIds and put to cache
for(Account a: [select erp_debtor_id__c,id from Account where ERP_DEBTOR_ID__c
IN:erpIds]){
                     erpIdAccountId.put(a.erp_debtor_id__c, a.Id);
       }
//System.debug(‘>SkyvvaCustomFormula.preExecute:cache erpIdAccountId:
‘+erpIdAccountId);
//>erpIdAccountId:{RES001=001F000001hGGJhIAO, RES002=001F000001mv8F4IAI}
}
/*
Find account id based on ERP_DEBTOR_ID from cache
@param record: map contains key/value of each message-data. the key of must be lower case
i.e: record{email=test1@gmail.com, erp_debtor_id=RES001, firstname=test1, lastname=test1}
*/
public override String execute(Map<String,String> record){
       //System.debug(‘>SkyvvaCustomFormula.execute: ‘+record);
       //key must be lower case
      String erpIdVal=record.get(‘erp_debtor_id’);
       //return account-id
              return erpIdAccountId.get(erpIdVal);
       }
}

 

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.

 
Was this article helpful to you? Yes No

How can we help?