Home > DemandTools Advanced Functions > MassImpact Formulas

MassImpact Formulas

 General Notes

• Create custom formulas from scratch:
  1. Extend a Close Date by 30 days:  {closedate} + 30, input:  03/31/07, result: 04/30/07
  2. Concatenate 2 text fields or take one field and add a constant.  For example, set the Opportunity Name equal to the Account Name + "Text String": {account.name} + " -  " + "Boston Trade Show 04/30/07", input:  I.B.M., result: I.B.M. – Boston Trade Show 04/30/07
  3. Take “monthly revenue” and calculate "annual revenue".   Set field Annual Revenue:  {monthly_revenue} * 12 input:  100000, result: 12000000
• Use the "Insert Function" button to display a list of prebuilt formulas.
• References to "StringValue" as a formula parameter should be replaced with either a specific value or a specific text field in Salesforce (use "insert field").  "NumberValue" should be replaced with a specific number or specific number field in Salesforce.
• Use the "Check Formula" button to confirm the syntax is correct before adding to set conditions.

NOTE:  Always review results BEFORE processing, and uncheck any records that should not be updated.  If records are accidentally updated and the original data needs to be restored, use the restore file that is automatically created in MassImpact as input to MassEffect update.

A direct link to the restore file is provided when processing completes.  To access the restore file AFTER the process completion message has been closed, access the file from the DemandTools Restore directory.  Check the "File Paths" tab in DemandTools options for the location of the Restore file directory.  Naming conventions for MassImpact restore files are "MIRestore_tablename_date_XXXX.xls".  The XXXX number will increment based on how many times the same table was updated in a particular day (MIRestore_Account_Aug112007_00010.xls).  By default, restore files are created as XLS, but this can be changed in the Preference/Options menu of DemandTools.

Prebuilt formulas

• ConvertDate:  Allows a date string (yyyy-mm-dd) to be converted to date/time format for insertion into Salesforce.
• CRLF:  Allows the ability to insert a carriage return/line feed character when concatenating fields.  NEW in DemandTools 2.2
• Date:  Populate a field with a specified year, month, and day.
• if_DateReturn:  Return a data value based on a condition.
• if_NumberReturn:  Returns a number value based on a condition.
• if_StringReturn:  Returns a text value based on a condition.
• isNull:  Used to determine if a field contains a null value.  NEW in DemandTools 2.2
• Int:  Rounds a number DOWN to the nearest integer.
• LCase:  Takes a field and lower cases letters.
• len:  Returns the number of characters in a text string.
• Mid_1:  Returns all characters from the middle of a text sting after a given starting position.
• Mid_2:  Returns the characters from the middle of a text string, given a starting position and length (number of characters to return).
• 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.
• Normalized_US_Address:   Standardizes a street address to match the USPS preferred format.
• now:  Updates a date and/or date/time field to the current day and/or current day and time.
• ProperCaseName:  Proper case a "Name" field.
• random:  Assigns a random value.
• Round:  Rounds a number to the nearest integer.
• StringReplace:  Search a field, find a particular string, replace with desired string.
• StringReplaceCS: Same as StringReplace with case sensitivity .
• today:  Update a date and/or date/time field to the current day.
• UCase:  Takes a field and upper cases letters.
• WCase:   Takes a field an upper cases just the first letter.
• ZipCodeClean: Strips out non-numeric characters, checks to see if there are less and 5 digits.

Date(year, month, day):  Populates a field with the specified year, month and day.

Example:  Date(2007,05,01) result: 05/01/2007

if_DateReturn(Condition, True Value, False Value):  Returns a date value based on a condition.

Example 1:  Set Field:  Opportunity.CloseDate based on probability, if_DateReturn({probability} >= 80, ConvertDate("2007-7-30"),ConvertDate("2007-8-30"))

NOTE:  To hardcode a date use the “Convert Date” functionality as described above.  Other functions can be used in conjunction with if_DateReturn to base a date on the current date.

Example 2: if_DateReturn({probability} >=90, today()+30, today()+60)

if_NumberReturn(Condition, True Value, False Value):  Returns a number value based on a condition.

if_StringReturn(Condition, True Value, False Value):  Returns a text value based on a condition.

Example:  Set Field:  Account.Rating based on annual revenue, if_StringReturn({annualrevenue}>100, "Hot", "Cold").

NOTE: If the existing field value should not be updated if the condition is not met, use "" for the false value: if_StringReturn({annualrevenue}>100, "Hot", "") and be sure to NOT CHECK the box for "Update Blanks" before processing (blanks will be ignored and the existing value in Salesforce will be kept as is).  Or “Use Conditions” where annualrevenue > 100, and set the value of rating to hot (don’t use the if_StringResult function).

Int(NumberValue):  Rounds a number DOWN to the nearest integer.

Example:  Int({annualrevenue})  input: 1000000.75, result 1000000

LCase(StringValue):  Takes a field and lower cases letters.

Example:  LCase({billingstate}), input: MA, result: ma

len(StringValue):  Returns the number of characters in a text string.

Mid_1(StringValue, index):  Returns all characters from the middle of a text string after a given a starting position.

Example:  Could be used to remove "Delete***" from an Account Name, if needed to "reset":
Mid_1({name}, 9), input "Delete***Thompson Industries", result "Thompson Industries".  For this example make sure you "Use Conditions" where the name starts with "Delete***".

Mid_2(StringValue, index, length):  Returns the characters from the middle of a text string, given a starting position and length (number of characters to return).

NaPhoneFix(StringValue):  Takes a phone number and formats it to match the format as entered in Salesforce user interface, i.e. (XXX) XXX-XXXX.  Will also standardize an abbreviation for extension to x.

Example 1:   NaPhoneFix({phone}), input: 212-555-1212, result: (212) 555-1212
Example 2:   NaPhoneFix({phone}), input: +1 212.555.1212, result: (212) 555-1212
Example 3:   NaPhoneFix({phone}), input: 2125551212 ext155, result: (212) 555-1212 x155

NOTE:  This is JUST for North American (10 digit) phone numbers, will cause unwanted results for international phone numbers. Incomplete phone numbers (less than 10 digits) will be stripped of all punctuation (i.e. 212-555-121 will be returned as 212555121).

Normalized_US_Address(InputAddress):  Standardizes a Street Address to match the USPS preferred format. 

Example 1:  Normalized_US_Address({mailingstreet}), input:  7th Floor, 162 Washington St., result:  162 Washington St Fl 7
Example 2:   Normalized_US_Address({mailingstreet}), input:  One North Main Street, Suite 100, result:  1 N Main St Ste 100

NOTE:  This does NOT verify the street address, just normalizes the data to the USPS standards.  To actually verify that the address is valid (exists in the USPS database) you need to use the Address Verification module (which will also standardize as part of the validation).

now():  Will update a date and/or datetime field to the current day and/or current day and time.  Should be used primarily for Datetime fields where the time should be captured in addition to the date.

ProperCaseName(StringValue):  Proper Case a "Name" field, typically a company/account name, or person.  Instead of just upper casing the first letter of each word, will catch some common prefixes, and case correctly (i.e. McDonalds, O’Sullivan).  Also, upper cases anything after punctuation.

Example 1:  ProperCaseName({name}), input Mcdonald’s Corp. result: McDonald’s Corp.
Example 2:  ProperCaseName({name}), input HEWLETT PACKARD. result:  Hewlett Packard

NOTE:  If the name contains abbreviations NOT separated by periods, the result will be just the first letter in uppercase (i.e. IBM will become Ibm).  "Mac" is NOT recognized as a common suffix, for example Macdonald, will NOT be changed to MacDonald.

random_0to1():  Assigns a random value from 0 to 1.
random_0to100():  Assigns a random value from 0 to 100.

NOTE:  Random functions can be used to assign a random number and then select a random sample of records to be used to test an email blast or direct marketing campaign.  For a random 10% sample, select the full set of records to be marketed, based on criteria.  Use the random function (i.e. random_0to100) to assign a random number to a custom number field.  Export the records where the number <= 10.

Round(NumberValue):  Rounds a number to the nearest integer.

StringReplace(StringValue, "Find", "Replacement"):  Search a field, find a particular string, and replace with desired string.  For use only on text fields.

Example 1:  Could be used to remove "Delete***" from an Account Name, if needed to "reset".  StringReplace({name}, "Delete***", "") input: Delete***Jackson Manufacturing, result:  Jackson Manufacturing
Example 2:  StringReplace({name}, "ctr", "Center") input:  Ctr for Disease Control, result:  Center for Disease Control

StringReplaceCS(StringValue, "Find", "Replacement"):  Same as StringReplace except the "find" value is Case Sensitive.

Example: StringReplaceCS({name}, "Dr. ", "") input: Dr. John Smith, result:  John Smith

NOTE:  Both StringReplace functions WILL REPLACE the "find" value ANYWHERE it finds it in the name.  Be sure to use the proper function and include punctuation and spaces as needed.  Review results carefully before processing.  For example:
StringReplace({name}, "Dr", "") will change "Dr. Michael Andrew" to ". Michael Anew"

today():  Will update a date and/or datetime field to the current day.  If used with a datetime field will have 12:00:00 AM as the time.

UCase(StringValue):  Takes a field and upper cases letters.

Example:  UCase({billingstate}), input: ma, result: MA

WCase(StringValue):  Takes a field and upper cases just the first letter and lower cases all remaining letters in the string.

ZipCodeClean(StringValue):  Takes a zipcode, strips out all non-numeric characters, then checks to see if it is less than 5 digits, and if so, adds a leading zero(s) to it.  The primary purpose is to fix zip codes where the leading zero was truncated on import (typically New England zip codes).

Example 1:   ZipCodeClean({billingpostalcode}), input: 2043, result: 02043
Example 2:   ZipCodeClean({billingpostalcode}), input: 243, result: 00243

NOTE:  This is JUST for US zip codes, will cause unwanted results for Canadian and international zips codes.  To find US zipcode that are missing the leading zero, you could use conditions where the states equal the New England states that typically have a leading zero, and then add another condition where the zip code does not start with "0".

See also