Formulas
Use formulas to dynamically add columns to your source data. Use common Excel & Salesforce formulas such as LEFT, CONCATENATE, and IF
General Formula Rules
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"
Text Operators
MAP
Map source values to target values. The Formula must include 3 parameters:
The field being mapped
The dictionary of mapping values
The default value where the source value is not provided in the mapping
All values must be enclosed in double-quotes " and not single-quotes '
MAP([Color], {"One": "Blue", "Two": "Red"}, "No Color")
This will convert 'One' to 'Blue', 'Two' to 'Red', and every other value to 'No Color'
CONCATENATE
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”
LEFT
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”
RIGHT
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
Replace characters in a string using either direct replacement, or a regex expression.
[Email].str.replace(" ", "")
[Phone].str.replace("\d+", "", regex=True)
SPLIT
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 “sam@dataimporter.io” 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)
LOWER
Convert the text of a column into lowercase characters.
"Sam Hoult" would become "sam hoult"
LOWER([NAME])
Comparison Operators
EQUALS
True if the operands are equal. String comparisons that use the equals operator are case-sensitive.
[State] == "NY"
NOT EQUALS
True if the operands are not equal. String comparisons that use the not equals operator are case-sensitive.
[State] != "NY"
LESS THAN
True if the left operand is less than the right operand.
[Revenue].astype('int') < 50000
GREATER THAN
True if the left operand is greater than the right operand.
[Revenue].astype('int') > 50000
Logical Operators
IF
Determines 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")
AND
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")
OR
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")
ISNULL
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])
NOTNULL
Returns True is a value is Not Null and False if it is null.
NOTNULL([Name])
CONTAINS
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
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
to_datetime
Changes the field from string to date / datetime
to_datetime([DateField])
TODAY
Evaluates to the current day.
today()
YESTERDAY
Evaluates to the previous day
yesterday()
TOMORROW
Evaluates to the next day
tomorrow()
timedelta
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 updated