DemandTools V Module Documentation

DemandTools - Modify Formula Dictionary

Below is a list of all pre-built formulas currently available in the Modify module.  Those that are most commonly used are denoted. 
   

Formula Definition  Syntax Example 
AllTextAfterChar Allows the return of text only if it is after a specific character in the string

Formula: 
AllTextAfterChar(InputString, "CharToSearchFor") 

Example: 
AllTextAfterChar( ${email} , "@")
AllTextBeforeChar Allows the return of text only if it is before a specific character in the string

Formula:
AllTextBeforeChar(InputString, "CharToSearchFor") 

Example: 
AllTextBeforeChar( ${email} , "@")
ConvertDate Allows a date string (yyyy-mm-dd) to be converted to date/time format for insertion into Salesforce
 

Formula:
ConvertDate("yyyy-mm-dd")

Example:
if_DateReturn( ${probability} > 80, ConvertDate("2021-07-30"),ConvertDate("2021-08-30"))

  • Input:  7/30/2021 12:00:00 AM
  • Result:  2021-08-30T04:00:00Z
CHAR Allows the ability to insert a carriage return/line feed character (\r\n) when concatenating fields

Formula: 
CHAR(number)

Example:  {billingstreet} + CHAR() + {billingcity} + ", " + {billingstate} + "  " + {billingpostalcode}

  • Input:  100 Main St Hingham, MA 02043
  • Result:    100 Main St

                     Hingham, MA 02043
CountryMatchReturn3CharacterName Converts country to ISO 3 character name

Formula: 
CountryMatchReturn3CharacterName(CountryValue, UseFuzzy)

Example: CountryMatchReturn3CharacterName( ${billingcountry}, true)

  • Input: United States
  • Result: USA 
CountryMatchReturnISONumber Converts country to ISO 3 digit number
Formula:
CountryMatchReturnISONumber(CountryValue, UseFuzzy)

Example: 
CountryMatchReturnISONumber( ${billingcountry}, true)
CountryMatchReturnLongName Converts country to long form

Formula: 
CountryMatchReturnLongName(CountryValue, UseFuzzy)

Example: 
CountryMatchReturnLongName( ${billingcountry}, true)

  • Input: US or USA
  • Result: United States 
Date Populate a field with a specified year, month, and day
Input: 2021,05,01
Result: 05/01/2021 		Formula: 
Date(year, month, day)

Example:  Date(2021,05,01)
 
DateAddValue Allows for the increase or decrease of a date/time by a set interval
 

Formula: 

DateAddValue("IntervalString", ValueToAdd, DateToEffect)

Example: 
DateAddValue("d",-4,{close_date})

  • Input: 12/30/2010
  • Result: 12/26/2010
     
FirstWord Returns the first word from a text string

Formula: 
FirstWord(StringValue)

Example: 
FirstWord( ${company})

  • Input: Validity Inc
  • Result: Validity 
Int Rounds a number DOWN to the nearest integer

Formula: 
Int(NumberValue)

Example: 
Int( ${annualrevenue}) 

  • Input: 1,000,000.75
  • Result: 1,000,000
LastWord Returns the last word from a text string

Formula: 
LastWord(StringValue)

Example: 
LastWord( ${company})

  • Input: Validity Inc. - Canada
  • Result: Canada
     
Lower Takes a field and lower cases letters

Formula: 
Lower(StringValue)

Example:
Lower( ${billingstate})

  • Input: MA
  • Result: ma
Left Returns the specified number of characters starting from the left in a text string

Formula: 
Left(StringValue, NumberOfChars)

Example: 
Example:  Left( ${billingpostalcode} , 5)

  • Input: 80108-7890
  • Result: 80108
len Returns the number of characters in a text string

 

Formula: 
len(StringValue)

Example: 
Example:  len( ${billingpostalcode})

  • Input: 80108
  • Result: 5
Mid_1 Returns all characters from the middle of a text sting after a given starting position

Formula: 
Mid_1(StringValue, index)

Example:  
Mid_1( ${name}, 9)

  • Input: Delete***Thompson Industries
  • Result: Thompson Industries
Mid_2 Returns the characters from the middle of a text string, given a starting position and length (number of characters to return)

Formula: 
Mid_2(StringValue, index, length)

Example: 
Mid_2( ${phone}, 2,3)

  • Input: (212) 555-1212
  • Result: 212
NaPhoneFix Takes a phone number and formats it to match the format as entered in Salesforce.  Will also standardize an abbreviation for extension to x

Formula: NaPhoneFix(phone)

Example: 
NaPhoneFix( ${phone})

  • Input: +1 212.555.1212
  • Result: (212) 555-1212
Normalized_US_Address Attempts to standardize a street address to match the USPS preferred format

Formula: 
Normalized_US_Address(InputAddress)

Example: 
Normalized_US_Address( ${mailingstreet})

  • Input:  7th Floor, 162 Washington St.
  • Result:  162 Washington St Fl 7

 

Note:  We highly recommend that address normalization formulas not be scheduled as the results will vary based on the validity of the supplied address. For example, street addresses that contain building names (i.e. 123 Main St Suite 100 One Prudential Tower) may return unwanted results.  **All results should be manually reviewed and unwanted results unchecked before processing.**

Normalized_US_Address2LineSuiteFirst Attempts to standardize the address returning the "address line 2" data separate (above) from the street address

Formula: 
Normalized_US_Address2LineSuiteFirst(InputAddress)

Example:
Normalized_US_Address2LineSuiteFirst( ${mailingstreet})

Input: One North Main Street, Suite 100
Result:     Ste 100
                1 N Main St

 

Note: We highly recommend that address normalization formulas not be scheduled as the results will vary based on the validity of the supplied address. 
 

Normalized_US_Address2LineSuiteLast Attempts to standardize the address returning the "address line 2" data separate (below) from the street address

Formula: 
Normalized_US_Address2LineSuiteLast(InputAddress)

Example: 
Normalized_US_Address2LineSuiteLast( ${mailingstreet})

Input: One North Main Street, Suite 100
Result:    1 N Main St
               Ste 100

Now Updates a date and/or date/time field to the current day and/or current day and time

Formula: 
now()
 

ParseNameReturnFirstName Parses the first name into the specified field to be updated

Formula: 
ParseNameReturnFirstName(FullName)

Example: 
ParseNameReturnFirstName( ${name} ) 

  • Input:  Full Name field = James Smith
  • Result:  First Name field = James
ParseNameReturnLastName Parses the last name into the specified field to be updated Formula: 
ParseNameReturnLastName(FullName, IncludeSuffix)
  • true:  includes the suffix in the last name field
  • false:  strips the suffix and returns ONLY the last name

ParseNameReturnLastName( ${name} , true) 

Example:     
ParseNameReturnLastName( ${name} , false)

  • Input:  Full Name = James Smith Jr.
  • Result:  Smith 
ParseNameReturnSuffix Parses the last name suffix (e.g. Sr., II, DDS) into the specified field to be updated

Formula: 
ParseNameReturnSuffix(FullName)

Example:  
ParseNameReturnSuffix( ${name} ) 

  • Input:  James Smith Jr.
  • Result:  Jr.
ParseNameReturnTitle Parses the title (e.g. Mr. Mrs. etc)  into the specified field to be updated

Designed to break a field containing a full name into Salutation, First, Last, and custom suffix name fields

Includes logic to identify titles, suffixes, first initials, hyphenated last names, 2 word last names etc. 		Formula: 
ParseNameReturnTitle(FullName)
Proper Takes a field and upper cases just the first letter and lower cases all remaining letters in the string.

Formula: 
PROPER(StringValue)

 

Example:  PROPER({description})

  • Input:  primary contact for all new equipment sales
  • Result: Primary contact for all new equipment sales
ProperCaseName Proper Case a "Name" field, typically a Company/Account Name, or First and Last Name.  Instead of just upper casing the first letter of each word, will apply logic to handle common prefixes (e.g. Mc and Mac), abbreviations, and allows for user overrides to always punctuate a particular string in a preferred manner

Formula: 
ProperCaseName(StringValue)

Example: 
ProperCaseName( ${name})

  • Input: mcdonald's corp.
  • Result: McDonald’s Corp.
ProperCaseNameForceAll Allows proper casing all data regardless of existing case

Formula: 
ProperCaseNameForceAll(StringValue)

Example: 
ProperCaseNameForceAll( ${name})

  • Input: JAB Consulting
  • Result: Jab Consulting
RAND Assigns a random decimal number between 0 & 1

Formula: 
RAND()

 

Example:  

Populate the random_c field with a number between 0 & 1

RAND(0,1)

  • Result:  .47964
  • Result:  .83492
RANDBETWEEN Assigns a random integer between specified values

Formula: 
RANDBETWEEN(bottom,top)

 

Example: 

Populate the random_c field with a random number from 0 to 100

RANDBETWEEN(0,100)

  • Result: 62
  • Result: 3

 
RegExMatch Allows users to build their own custom regular expressions to manipulate data in a specific way.  Knowledge of regular expressions is required to use this formula and customers are responsible for building their own expressions.  

There are several free regex tutorial websites online that users may want to reference to learn more. 		Formula:
RegExMatch(InputString, RegularExpression)
 
RegExReplace Search a field, use a regular expression to find data, replace with desired string

Knowledge of regular expressions is required to use this formula and customers are responsible for building their own expressions.  

Formula: 
RegExReplace(InputString, RegularExpression, ReplaceString)

Example: 
Replace a carriage return in a street address with a space


RegExReplace( ${billingstreet},"\n"," ")

Input:  55 Best Place
           Suite 12
Result:  55 Best Place Suite 12
 

RemoveLastWord Removes the last word from a specified text string Formula:

RemoveLastWord(StringValue)

Example: RemoveLastWord({lastname})

  • Input: Smith Sr.

  • Result: Smith
Right Returns the specified number of characters starting from the right in a text string
  		 Formula:

Right(StringValue, NumberOfChars)

 

Example: Right( {accountnumber} , 5)

  • Input: 000098765
  • Result: 98765
Round Rounds a number to the nearest number

Formula:

Round(NumberValue, NumberDigits)

 

Example: Round( {random__c},2) 

*round the random_c field to the nearest integer

  • Input:  47.5667
  • Result: 47.57

 
StateMatchLongName

Updates all US states & Canadian provinces to the long form

Formula:

StateMatchLongName(StateValue, UseFuzzy)

Example:  StateMatchLongName({billingstate}, true)

  • Input:  CT

  • Result: Connecticut
StateMatchShortName Updates all US states & Canadian provinces to the ISO two character abbreviation.

Formula:

StateMatchShortName(StateValue, UseFuzzy)

 

Example:  StateMatchShortName({billingstate}, true)

  • Input: Massachusetts
  • Result: MA
StringReplace Search a field, find a particular string, replace with desired string

Formula:

StringReplace(StringValue, "Find", "Replacement")

 

Example:  Could be used to remove "Delete***" from an Account Name.

StringReplace({name}, "Delete***", "")

  • Input: Delete***Jackson Manufacturing
  • Result:  Jackson Manufacturing
StringReplaceCS Same as StringReplace with case sensitivity

Formula:

StringReplaceCS(StringValue, "Find", "Replacement")

 

Example: 

StringReplaceCS({name}, "Dr. ", "")

  • Input: Dr. John Smith
  • Result:  John Smith

 
Today Update a date and/or date/time field to the current day

Formula:

today()  

Example:  Update the Last Verified Date_c field to today's date

Formula = today
Result = 11/20/2019

 

Note:  No additional parameters are required when using this formula
TrimStartSpaces Trim all leading spaces from a text field

Formula:

TrimStartSpaces(StringValue)

 

Example:  TrimStartSpaces({firstname})

  • Input:  "   John"
  • Result:  "John"
TrimEndSpaces Trim all trailing spaces from a text field

Formula:

TrimEndSpaces(StringValue)

 

Example:  TrimEndSpaces({firstname})

  • Input:  "John   "
  • Result:  "John"
Trim Trim all leading AND trailing spaces from a text field

Formula: 
TRIM(StringValue)

 

Example:  Trim({firstname})

  • Input:  "   John   "
  • Result:  "John"
Upper Takes a field and upper cases letters Formula:

UPPER(StringValue)

 

Example:  UCase({billingstate})

  • Input: ma
  • Result: MA
ZipCodeClean

Attempts to reformat US zip codes adding a leading zero if needed


The primary purpose is to fix zip codes where the leading zero was truncated on import

Formula:

ZipCodeClean(StringValue)

 

Example: ZipCodeClean({billingpostalcode})

  • Input: 2043
  • Result: 02043

Note: This is JUST for US zip codes and specifically those in states that typically have a leading zero. May cause unwanted results with non US zip codes.

Related articles

Get DemandTools

We're assuming you already have DemandTools but if you don't, you should totally get it!

Company
Products
Solutions
Resources
Contact