General Notes
Create custom formulas from scratch:
- Extend a Close Date by 30 days: {closedate} + 30, input: 03/31/07, result: 04/30/07
- 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
- Take “monthly revenue” and calculate "annual revenue". Set field Annual Revenue: {monthly_revenue} * 12 input: 100000, result: 12000000
Use the "Check Formula" button to confirm the syntax is correct before adding to set conditions.
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.
A direct link to the restore file is provided when processing completes.
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 DemandTools Options menu
Formula Wizard
1. Type in a custom formula or click Insert Function to select from one of the pre-built formulas
- If creating a custom formula click Add to Set Conditions when done entering the formula
2. Select the desired pre-built formula and click Use Function
3. Replace the parameter "String Value" in parentheses (could also appear as "Input String", "Country Value", "State Value", "Input Address", "Input Number", etc.) with the appropriate field name from Salesforce
- Highlight the word "String Value" or backspace over it (leaving the parenthesis) and click "Insert Field"
4. Select the field from the drop list and then click Use Field
- The complete formula in this example: ZipCodeClean( {billingpostalcode} )
Pre-built Formulas (select the hyperlink to view more detail about the formula)
AllTextAfterChar: Allows the return of text only if it is after a specific character in the string
AllTextBeforeChar: Allows the return of text only if it is before a specific character in the string
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 (\r\n) when concatenating fields
CR: Allows the ability to insert a carriage return (\r) character when concatenating fields
CountryMatchReturn2CharacterName: Converts country to ISO 2 character name
CountryMatchReturn3CharacterName: Converts country to ISO 3 character name
CountryMatchReturnISONumber: Converts country to ISO 3 digit number
CountryMatchReturnLongName: Converts country to long form
Date: Populate a field with a specified year, month, and day
DateAddValue: Allows for the increase or decrease of a date/time by a set interval
FirstWord: Returns the first word from a text string
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
Int: Rounds a number DOWN to the nearest integer
isNull: Used to determine if a field contains a null value
LastWord: Returns the last word from a text string
LCase: Takes a field and lower cases letters
Left: Returns the specified number of characters starting from the left in a text string
len: Returns the number of characters in a text string
LF: Allows the ability to insert a line feed (\n) character when concatenating fields
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: Attempts to standardize a street address to match the USPS preferred format
Normalized_US_Address2LineSuiteFirst: Attempts to standardize the address returning the "address line 2" data separate (above) from the street address
Normalized_US_Address2LineSuiteLast: Attempts to standardize the address returning the "address line 2" data separate (below) from the street address
Now: Updates a date and/or date/time field to the current day and/or current day and time
ParseNameReturnFirstName: Parses the first name into the specified field to be updated
ParseNameReturnLastName: Parses the last name into the specified field to be updated
ParseNameReturnSuffix: Parses the last name suffix (e.g. Sr., II, DDS) into the specified field to be updated
ParseNameReturnTitle: Parses the title (e.g. Mr. Mrs. etc) into the specified field to be updated
ProperCaseName: Proper case a field, designed for Name fields (e.g. Company/Account Name, First and Last Name).
- Only proper cases data that is NOT currently in mixed case (e.g. incoming data all in uppercase or lowercase)
ProperCaseNameForceAll: Allows proper casing all data REGARDLESS of existing case
Random_0to1: Assigns a random value from 0 to 1
Random_0to100: Assigns a random value from 0 to 100
RegExMatch: Allows users to build their own custom regular expressions to manipulate data in a specific way
RegExReplace: Search a field, use a regular expression to find data, replace with desired string
RemoveLastWord: Removes the last word from a specified text string
Right: Returns the specified number of characters starting from the right in a text string
Round: Rounds a number to the nearest integer
StateMatchLongName: Updates all US states & Canadian provinces to the long form
StateMatchShortName: Updates all US states & Canadian provinces to the ISO two character abbreviation.
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
toNumber: Converts the input value to a number. If the input value is a number field that is empty (null) will convert to a 0
TrimStartSpaces: Trim all leading spaces from a text field
TrimEndSpaces: Trim all trailing spaces from a text field
TrimSpaces: Trim all leading AND trailing spaces from a text field
UCase: Takes a field and upper cases letters
WCase: Takes a field an upper cases just the first letter
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
