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

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 “[e-mail]” 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)