MassEffect can be used to update existing records for any Salesforce object that supports updates. Records can be updated ONE object at a time (per pass) using the standard SOAP API or Bulk API.
When updating records, a matching key is required to match the records from the input file to the existing records in the desired object. By default, the Salesforce record ID is set as the matching key, but can be changed to any field in the object.
Data can be updated on the existing records by either mapping an input column from a spreadsheet to the appropriate field in Salesforce, or by using the Add Constant option available when right-clicking on a field name. Options are available to update existing fields only when empty and to merge new data with existing data for specific data types. Data can also be transformed during an update (e.g. States can be standardized to the long form or 2 digit abbreviation, phone numbers can be reformatted, data can be proper cased etc.). These options are described in detail below.
Step 1. Select Incoming Data Source
Begin by selecting the input file that contains the data to be updated.
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 process by clicking on it (will highlight in blue)
- Click Use on the right or double click to select the appropriate worksheet
- the fields from the spreadsheet/table will populate in the left column of the screen
- 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. This will ensure that special characters are imported correctly.
Step 2. Select the Update Existing Records Operation
- Updates records for one object per pass
- Check Use Bulk API if desired, more information on that option is located HERE
- Restore File: By default restore files are created when updating records
- Uncheck this box if a restore file is NOT needed
A pop-up message, "Restore / MergeData file could not be created", may be shown when attempting to process the update. This indicates there is a problem creating the restore file and the update will be ABORTED. More information on this error and how to resolve can be found in the Validity Community Solution: Restore / MergeData file could not be created.
- Process Blank: By default blank values DO NOT overwrite existing data. Check this box to OVERWRITE existing data in Salesforce with a blank value.
- Applies when the input file contains blanks for a mapped field or the Add Constant option is used to set a field to blank
Step 3. Choosing the Unique Key
Locate the object in the middle of the screen and click the + sign next to the object to show all the fields for that object. The first field shown in each table is the Id field, which is the default matching key.
- The object Id can be either a 15 digit Id (e.g. exported from a Salesforce Report) or a 18 digit Id (e.g. exported using DemandTools)
- More information on 15 vs. 18 digit Id's can be found in the Validity Community Solution: Why are the Salesforce ID's returned by DemandTools 18 digits instead of 15 digits?
Set as Key
To designate a DIFFERENT field as the unique key field, right click the desired field and choose Set as Key.
A pop-up warning will appear displaying the caveats when changing the unique key.
Important Notes on Changing the Unique Key
- Keys ARE case sensitive
- Restore files cannot be created if the unique key is changed
- Special update options are not available (e.g. update only if empty, and merge multi-select, text-area or boolean field options)
- Fields that CANNOT be directly updated WILL NOT BE SHOWN in "Update Mode" and therefore cannot be chosen as the unique key
- Examples include auto-number fields, formula fields and any other read-only fields
- Updates will be done one record at a time vs. in batch mode (the default batch size in DemandTools is set to 100), which could use a significant amount of API calls as a result.
- Depending on the number of records being updated, the update could run significantly slower
- Multiple records in Salesforce could be updated from one record on the input file (no guarantee that the selected key is unique)
- If multiple records are found that match the key ALL matching records will be updated
When the key is set, you will see an updated "key" icon next to the field
If a restore file is required, special update options needed, or matching on a read-only field is desired (i.e. set as key), the DemandTools Discovery module can be used to match the input file to the appropriate table in Salesforce based on the desired key which will return a file that contains all the input data AND the Salesforce ID. This file can then be used as input to MassEffect, and now matching can take place on the Salesforce ID.
Step 4. Field Mappings
Map the fields from the input file to the desired Salesforce fields or use the Add Constant option for a particular field(s) when the same data should be updated on all the records. Data can also be transformed during an update (e.g. States can be standardized to the long form or 2 digit abbreviation, phone numbers can be reformatted, data can be proper cased etc.).
- 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 object 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)
- This can be done in 2 ways:
- 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 picklist 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 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 records
- 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 an calendar option provided via a drop-down arrow on the right
- Load Date: Additional option to always apply the current date (date running the update) to the field
- Helpful for saved/scheduled scenarios so they do not have to be manually updated with today's date each time they are used
- Load Date: Additional option to always apply the current date (date running the update) to the field
Access Special Update Options
Mapped field data can be manipulated prior to insert/update to the records in Salesforce by right-clicking on the mapped field. Options include removing a mapping, updating only if empty, merging the new data with existing data (available for specific data types only) and transforming the data using one of our supplied data normalization formulas.
- Remove: Available for all operations (removes the mapping)
- Update from input data only if the field is empty in Salesforce: Available for Update and Upsert
- Will only update a field value in Salesforce if the field in Salesforce is blank, populated data in Salesforce will NOT be updated
- Merge input data with existing values in Salesforce: Available for Update and Upsert
- This option only appears on applicable field types; text area/long area text, multi-select picklist, number, and boolean (true false checkbox) fields
- Merges the value in the incoming spreadsheet with the existing value in Salesforce
- Text area fields are separated by a carriage return
- By default new data is APPENDED to existing data
- Option to PREPEND is available in DemandTools -> Options - > General Settings
- Multi-select picklist fields will ADD new values to any existing values in the record being updated
- Number fields will sum the input and existing value
- Boolean True/False fields will always retain the True value, "True always wins"
- Transform: Available for Insert, Update and Upsert
- Ability to normalize input data PRIOR to insert/update to Salesforce
- Can select a variety of pre-built formulas designed to address common data standardization needs
- Simple, single field based formulas ONLY are available
- The full list of available formulas is shown when clicking the Transform option
- Click HERE for detailed information on the Transform formulas
The chosen formula will be shown after selection :
Step 5. Process the Update
- 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
Restore File
When the process completes a Restore File will be created which contains a before image of the fields that were updated.
- Assumes the Restore File option was checked
- Available with and without Bulk API
- The first column in the restore file will contain the record id's for the updated records
- The remaining columns will contain the data that existed in the changed fields PRIOR to the update
- In the event of a bad update, the restore file can be used to reverse the changes
Update File
When errors are encountered when updating records WITHOUT Bulk API, an Update file will be created containing any failed rows. The file will contain the Salesforce ID of the records it was attempting to update, the error that was returned by the Salesforce API and ALL the input data. The failed rows can then be corrected and processed back through MassEffect.
- File Name: SAME name as the input file with Update appended to the end
- For example: Contacts_Update0.xlsx
- If multiple updates were done using the same input file the number at the end will be incremented by 1 for each subsequent update (e.g. Contacts_Update1.xlsx)
- File Location: Saved in the SAME location at the original input file
- File Type: Uses the type specified in the DemandTools Options Menu for Default Restore/IdSave/Export File Extension
- File Columns:
- {ObjectName}_UpdateID: Will contain the Salesforce record Ids for the failed rows
- {ObjectName}_Success: FALSE
- Will always contain FALSE as the file ONLY writes out failed rows
- {ObjectName}_Error: 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
