MassEffect - Transform Formulas

MassEffect has the ability to normalize input data PRIOR to uploading to Salesforce. Transform is available for the Update, Upsert and Insert operations.

There is no "preview" option in MassEffect when using transform, therefore turning on history tracking in Salesforce is highly recommended for any fields that will be transformed.  Restore files are created for updates and upserts and can be used to undo unwanted changes.

IT IS HIGHLY RECOMMENDED THAT BULK API IS USED with the transform options as the "normalized" values that are sent to Salesforce are captured in the Success/Error files.

• Can select a variety of pre-built formulas designed to address common data standardization needs
• Formulas are similar to those available in MassImpact
• Simple, single field based formulas ONLY are available
• The full list of available formulas is shown when clicking the Transform option

                                                        The chosen formula will be indicated after selection

Transform Formulas
The following formulas are available when right-clicking on a mapped field and selecting the Transform option.

Propercase Name

• 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.
• ONLY attempts to proper case when the input data is all in upper or lower case
  • If the input data is already proper cased (combination of upper and lower case, e.g. eBay) changes will NOT be made
• Logic regarding the prefix “Mac” (e.g. does not capitalize the first letter after “Mac” if the word is 5 or less letters)
• Logic to identify abbreviations
  • When there is no punctuation between the letters, and the word contains NO VOWELS, the word is assumed to be an abbreviation and all letters will be uppercased
    • For Example:  BCBS, GM, JFK etc.
• ProperCaseOverrides.txt file available in DemandToolsData\ReplaceList directory for user customization, click HERE for more information on this file.
  • Allows the user to add overrides for any words the formula is not proper casing in a preferred manner
  • Abbreviations containing a vowel should be added here as the standard logic will only assume a word is an abbreviation if it does not contain a vowel
  • Words in this list will be returned in the format specified
    • Examples applying to Company/Account Names:
      • IBM, AIG, AAA, USA, eBay, salesforce.com
    • Examples applying to First/Last Names:
      • AJ, EJ, PhD, CPA
  • This list is pre-populated but can be updated as needed

Note: This file is loaded upon login, therefore if changes are made they will not be recognized until the user logs out and back in to DemandTools

Examples:

Input: MARY
Result: Mary

Input: mcdonald's corp.
Result: McDonald’s Corp.

Input: HEWLETT PACKARD
Result: Hewlett Packard

Input: JJ ENTERPRISES
Result: JJ Enterprises

Input: Validity, Inc.
Result: Validity, Inc. (already proper-cased so NO changes made)

Input: EBAY
Result: eBay (cases eBay in the preferred format since this value is in the ProperCaseOverrides.txt file)

Note: The following words WILL NOT be capitalized: and, at, by, for, of, or, to, it.

Propercase Name IC

• Same as ProperCaseName EXCEPT proper cases all data REGARDLESS of existing case

Examples:

Input: Mary
Result: Mary

Input: mary
Result: Mary

Input: JAB Consulting
Result: Jab Consulting (assumes JAB is NOT in the ProperCaseOverrides list)

When using this version of Propercase it is especially important to review the ProperCaseOverrides list and customize as needed.

Upper Case

• Takes a field and upper cases letters

Example: Input: ma
Result: MA

Lower Case

• Takes a field and lower cases letters

Example: Input: MA
Result: ma

Country Match 2Char

• Converts country to ISO 2 character name

Example:
Input: Germany
Result: DE

Country Match 3Char

• Converts country to ISO 3 character name

Example:
Input: United States
Result: USA

Country Match Long

• Converts country to long form

Example:
Input value: CA
Result: Canada

Note: United Kingdom or UK will be changed to GB or GBR, respective to the formula in use as this is the ISO format.

NA Phone Fix

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

Examples:

Input: 212-555-1212
Result: (212) 555-1212

Input: +1 212.555.1212
Result: (212) 555-1212

Input: 2125551212 ext155
Result: (212) 555-1212 x155

This is a North American phone fix, DO NOT use with international phone numbers. If less than 10 characters returned after stripping alpha characters and punctuation, DemandTools will return the original input data.

Zip Code Clean

• 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.

Example:
Input: 2043
Result: 02043

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.

State Match Short

• Updates all US states & Canadian provinces to the ISO two character abbreviation

Examples:

Input: Florida
Result: FL

Input: Ma
Result: MA

State Match Long

• Updates all US states & Canadian provinces to the long form 

Example:

Input: OH
Result: Ohio

Ability to customize to accommodate international states. Click HERE for more details.

Note: The State formulas will also convert common abbreviations for states to the appropriate long or short form (e.g. Conn -> Connecticut, CT, Mass -> Massachusetts, MA, Cal -> California, CA, Miss -> Mississippi, MS).

Filename With Extension

• Used when inserting documents or attachments to pull the file from the full pathname.  The full pathname needs to mapped to the "body" field for the document/attachment.  Instead of having a separate column with just the Name of the file (to map to the "Name" field), can map the full pathname to the "Name" field also and then use the "Filename with Extension" option to pull the filename from the full path and use that to populate the "Name" field in the document or attachment.