MassEffect - Insert

MassEffect can be used to insert new records into any Salesforce object that supports inserts.  Records can be inserted into ONE object at a time (per pass) using the standard SOAP API or Bulk API.

When inserting records no matching is done to check for duplicates, all rows in the input file will be added as NEW records to the selected object.  To check for existing records prior to inserting, the upsert operation can be used.  More information on the upsert operation can be found HERE.

For more complex matching, the Find/Report ID's module can be used to match the input file to existing records in a specified object.  More information on Find/Report ID's can be found HERE.

Step 1.  Select Incoming Data Source

Begin by selecting the input file that contains the data to be inserted



1.    Select the file by clicking Select File

• Browse to find the file and click "Open"

2.    If an .xls, .xlsx, .mdb or .accdb file has been chosen the user must select the worksheet/table name to use by clicking on it (will highlight in blue)

• Click Use on the right or double click to select (the fields from the spreadsheet/table will populate in the left column of the page)
• The number of records loaded will be displayed in red
• Read CSV as UTF8:  Select this option to force files to open in UTF-8 encoding, even if they were saved in a different format
  • Ensures that special characters are imported correctly

Step 2. Select the Insert New Records Operation

     

• Inserts new records for one object per pass
  • Cannot create new Accounts and Contacts in one pass
• Check Use Bulk API if desired

Step 3. Field Mappings

Map the fields from the input file to the desired Salesforce fields, or use the Add Constant option when the same data should be inserted into all the records being inserted for a particular field.  

• Locate the object in the middle of the screen and click the + sign next to the object
• Map the fields from the input data source to the appropriate field in the table
  • This can be done in 2 ways:
    • Right click on the table name and select AutoMap (this will automatically map any fields from the input source where the column name matches either the field name or field label in Salesforce)                                                                                                                               
    • Left click on the field, drag and drop to the matching Salesforce object field
• Mapped fields will be denoted with a
  • All fields from the input file do not need to be mapped
  • To remove an incorrectly mapped field right click on the mapping and choose Remove

                 

Important Notes on Mapping and Data Contained in the Input File

• Fields that CANNOT be directly inserted/updated WILL NOT BE SHOWN
  • Examples include:  auto-number, formula, created/modified fields
• Boolean fields: 0 = False, 1 = True; to mark a boolean field as true or false the input file field must have either a 0/1 or True/False as the input value to make the appropriate selection
• Multiselect fields: multiple values must be separated by a ";" character
• For all lookup/ID fields in Salesforce, the Salesforce ID, not the name, will be needed
  • Examples include: Account ID, RecordType ID, Parent ID, Campaign ID
• Owner Alias can also be mapped to the Owner Alias field, but only one can be mapped (either the Owner ID or Owner Alias, not both)
  • Owner full name is NEVER accepted
• To link a new child record to a parent record (e.g. link a Contact to an Account), the other Salesforce Object ID is needed
  • DO NOT USE THE OBJECT NAME (text) as this will result in an "Invalid Cross Reference ID" error

Add Constant

To have the exact same value appear on all the records being updated or inserted, right click on a field and select Add Constant

• Replaces the need to create a column in the data source for a field(s) of data that is constant for all record
• Can be done with any field type
  • Text, text area, long area text:  Enter free form text
  • Picklist/Multi-select picklist:  Valid picklist values shown, check the appropriate box
    • Multiple boxes can be checked for multi-select picklists
  • Dates:  Today's date is displayed with a calendar option provided via a drop-down arrow on the right
    • Load Date: Additional option to always select the current date
    • Helpful for saved/scheduled scenarios so they do not have to be manually updated with today's date each time they are used

                         

Transform

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

Some of the formulas provided are either North American or US based (i.e. NA Phone Fix, ZipCodeClean) and will cause unwanted results with international data.  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 are available
• Simple, single field based formulas ONLY are available
  • Additional more complex formulas are available in MassImpact which can be applied to existing records (AFTER insert/update)
  • The full list of available formulas is shown when clicking the Transform option

                                                     The chosen formula will be indicated after selection

More information on the Transform Formulas is located HERE

Step 4.  Process the Insert

• To perform the chosen operation click   in the lower right
  • By default, ALL the input rows will be processed
  • When using an xls, xlsx or csv file as the input source, only specific rows can be selected for processing by changing the starting and ending rows using the Control option on the right

• • To process the first row from a data source change the End number to 1
  • Changing the Start/End values allows for testing a small portion of the input file before processing the entire file
• A Status pop-up will be displayed when the operation is complete noting if there were any errors and listing any results files created (e.g. Logfile, Success/Error Files, IdSave files, Restore files).  Links are provided to access the results file DIRECTLY from the status pop-up.

         

Results Files with Newly Created Id's

When the process completes an IdSave (SOAP API) or Success/Error files (Bulk API) will be created which contain ALL the input data PLUS the Salesforce ID's for the newly created records.

IdSave File Details

• File Name:  SAME name as the input file with IdSave appended to the end
  • For Example: Contacts_IdSave0.xlsx
  • If multiple inserts were done using the same input file the number at the end will be incremented by 1 for each subsequent insert (e.g. Contacts_IdSave1.xlsx)
• File Location:  Saved in the SAME location as the original input file
• File Type:  Uses the type specified in the DemandTools Options for Default Restore/IdSave/Export File Extension
• File Columns:
  • {ObjectName}_Created_SF_id:  Will contain all the newly created ID's that were assigned to the new records
  • {ObjectName}_Success:  TRUE/FALSE
    • TRUE: The insert was successful
    • FALSE: The insert failed
  • {ObjectName}_Error:  In the event of a failure will contain the specific error returned from the Salesforce API
    • The error text can also be found in the DemandTools Logfile
    • The remaining columns will contain the input data
• The IdSave file can be read directly back into MassEffect to delete the new records if a mistake was made where the user wishes to start over
  • Select Delete as the operation on the right and map the "{ObjectName}_Created_SF_ id" to the id field in the appropriate table