Formulas
Use formulas to dynamically add columns to your source data. Use common Excel & Salesforce formulas such as LEFT, CONCATENATE, and IF
To reference a source column in a formula then place the column name in square brackets
[Name]To add a static value e.g. a record type id, place the value in double quotes
"0013t00001XJ2dXAAT"Combine two or more fields by referencing their columns names and adding a + in between them.
[Name] + "-" + [Type]E.g. if the Name value is ‘Sam Hoult’, and the Type value is ‘Prospect’ then the evaluated formula for that record would be “Sam Hoult-Prospect”
Take the left n characters of a field
LEFT([Name], 3)E.g. if the Name value is Benjamin Button, then the evaluated formula for that record would be “Ben”
Take the left n characters of a field
RIGHT([Name], 3)E.g. if the Name value is Benjamin Button, then the evaluated formula for that record would be “ton”
These expressions can be combined together e.g. for a column [Date] where the values are in yyyy-mm-dd format the following formula could be used
LEFT(RIGHT[Date], 5), 2)This first takes the right 5 characters from the date string: ‘mm-dd’, and then takes the left 2 characters of that substring: ‘mm’
Replace characters in a string using either direct replacement, or a regex expression.
[Email].str.replace(" ", "")[Phone].str.replace("\d+", "", regex=True)Split up a field by a specified character, and the specified number of the split. 0 gives the first value in the split, 1 gives the second and so on.
The value “[email protected]” would give the value “dataimporter.io” using the formula below.
SPLIT([Email], "@", 1)The value "One;Two;Three" would give the value "One" using the formula below.
SPLIT([Picklist], ";", 0)
Convert the text of a column into lowercase characters.
"Sam Hoult" would become "sam hoult"
LOWER([NAME])True if the operands are equal. String comparisons that use the equals operator are case-sensitive.
[State] == "NY"True if the operands are not equal. String comparisons that use the not equals operator are case-sensitive.
[State] != "NY"True if the left operand is less than the right operand.
[Revenue].astype('int') < 50000True if the left operand is greater than the right operand.
[Revenue].astype('int') > 50000Determines if expressions are true or false. Returns a given value if true and another value if false.
IF([State] == "NY", "New York", "Other")IF([Revenue].astype('int') > 100000, "Big Customer", "Regular Customer")You can also nest IF statements like so
IF([State] == "NY", IF([Type] == "Big Customer", "NY Big Customer", "NY Regular Customer"), "Other")Combine multiple logic statements together using the ampersand symbol. Each condition must be surrounded by parenthesis, as shown in the example below. Only when every condition returns True will the entire statement return True.
IF(([State] == "NY") & ([City] == "New York City"), "Target Customer", "Other")Combine multiple logic statements together using the ampersand symbol. Each condition must be surrounded by parenthesis, as shown in the example below. When one of the conditions returns True will the entire statement return True.
IF([State] == "NY") | ([State] == "TX"), "Target Customer", "Other")You can combine multiple & and | operators to create more complex expressions
IF((([Support Plan] == "TRUE") & ([Name] == "Big Account")) | ([Big Account] == "True"), "Gold", "Silver")Returns True is a value is Null and False if it contains a value. Will return False if the value is an empty string e.g. “”.
IF(ISNULL([Name]), "Unknown", [Name])Returns True is a value is Not Null and False if it is null.
NOTNULL([Name])Returns True if a value contains the string provided, otherwise will return False. The string provided is case sensitive.
IF(CONTAINS([Name], "New"), "New Account", "Old Account")If the value is “New York” this would return true, where as “new york” would return False.
Date operators can be used to generate static values, as well as to filter records e.g. where date is less than 90 days from today.
When you want to use a field in your data as part of a date calculation then you need to first convert it to the right format using the to_datetime formula
Changes the field from string to date / datetime
to_datetime([DateField])Evaluates to the current day.
today()Evaluates to the previous day
yesterday()Evaluates to the next day
tomorrow()Can be used to change a dates value by a number of days, weeks or months
timedelta(days=90)to_datetime([DateField]) - timedelta(days=90)
Last modified 2mo ago