 |
 |
 |
 |
Home > DemandTools Modules > DemandTools Maintenance Modules > MassImpact Formulas MassImpact Formulas General Notes
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("yyyy-mm-dd"): Allows a date string (yyyy-mm-dd) to be converted to date/time format for insertion into Salesforce. 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_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 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 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. 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. 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 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: 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 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 |
 |
 |
 |
Print