MassEffect can be used to upsert existing records in any Salesforce object that supports insert/update. Records can be upserted ONE object at a time (per pass) using the standard SOAP API or Bulk API.
When upserting records, a matching key is required to match the records from the input file to the existing records in the desired object. The matching key can be either the Salesforce unique Id, an external ID (a custom field with the “External ID” attribute), or email address (Leads or Contacts). When a matching record is found it will be updated. If no match is found a new record will be inserted. Lookup fields on the object being upserted can also be populated using an external ID (e.g. populate Account ID when upserting Contacts).
Step 1. Select Incoming Data Source
Begin by selecting the input file that contains the data to be upserted.
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 fields from the spreadsheet 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 Upsert Records Operation
- Upserts records for one object per pass
- Check Use Bulk API if desired
- More information on Bulk API can be found HERE
- Restore File: By default restore files are created when upserting records
- Uncheck this box if a restore file is NOT needed
- 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 table that allow updates. The fields available as matching keys (Id, external Id's, and email addresses) will be denoted with a yellow flag. To set the key, right-click on the desired field and select Set as Key.
- If using the object ID, it can be either a 15 digit Id (e.g. exported from a Salesforce Report) or a 18 digit Id (e.g. exported using DemandTools)
- When selecting either an External Id or the email address, there is no guarantee the field is unique
- If there are duplicates in the input file or in Salesforce, NONE of the matching records will be updated
- Salesforce will return an error
- When a duplicate is in the input file: "Duplicate external id specified"
- When there are duplicate matching records: "Email: more than one record found for external id field: [00QE000000QDJOPMA5, 00QE000000QDJTdMAP]
Note: Duplicates are checked WITHIN a single batch. Therefore duplicates MAY be processed if they happen to fall in separate batches.
Special Notes When Upserting Leads
When selecting the standard Salesforce email address or the Salesforce object ID as the matching key for Leads, the API will look for matches to BOTH converted and unconverted Leads. There is no way to change this behavior.
If the ONLY matching record is a converted Lead the update will fail with the error: cannot reference converted lead. If matches are found to multiple Leads (including a combination of converted and unconverted) the update will fail with the error: Email: more than one record found for external id field: [00QE000000QDJOPMA5, 00QE000000QDJTdMAP].
Note: If using the object Id as the matching key, at most a single match could be found, but it could be a converted or unconverted Lead.
The only workaround to avoid matching to converted Leads by email is to create a custom external ID field for email, copy the current email to the external ID field, then use the external ID field as the matching key. Salesforce DOES NOT match to converted Leads in an upsert call when an actual external ID field is used.
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
- Left click on the field, drag and drop to the matching Salesforce object field
- Right click on the object name and select AutoMap
- 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 select 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 but not both)
- Owner full name is NEVER accepted
- Owner Alias can also be mapped to the Owner Alias field, but only one can be mapped (either the Owner ID or Owner Alias but not both)
- Examples include: Account ID, RecordType ID, Parent ID, Campaign ID
- 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 a "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 such that they do not have to be manually updated with today's date each time they are used
- Load Date: Additional option to always select the current date
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
- Will only update a field value in Salesforce if the field in Salesforce is blank
- 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, an 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 - click HERE for information on Transform formulas
Populating Lookup Fields Using an External Id
Lookup fields can be populated using an external ID to that object, e.g. Account ID when upserting Contacts. To create the relationship, users need to know what fields have external id's that can be used to populate the lookup field.
1. Right click on the desired field and select Foreign Key Upsert -> {objectname} -> Field to use as the foreign key (could be multiple on that table)
2. Map the field from the input file
3. Repeat steps for any additional lookup fields
Note: This option is ONLY available with Upsert. When Inserting/Updating the Salesforce ID will be needed to populate any lookup fields.
Step 5. Process the Upsert
- 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
Upsert File
When upserting records WITHOUT Bulk API, an Upsert file will be created containing results for each row in the input file. The file will contain the Salesforce ID of the records it attempted to update or insert, any errors that were returned by the Salesforce API, and ALL the input data. Failed rows can be corrected and processed back through MassEffect.
- File Name: SAME name as the input file with Upsert appended to the end
- Leads for Upsert_Upsert0.xlsx
- If multiple upserts were done using the same input file, the number at the end will be incremented by 1 for each subsequent upsert (e.g. Upsert_Upsert1.xlsx)
- File Location: Saved in the SAME location at the original input file
- File Type: Uses the type specified in the DemandTools Options for Default Restore/IdSave/Export File Extension
- File Columns:
- {ObjectName}_UpsertedID: Will contain the Salesforce record IDs it attempted to update or insert
- If an attempted insert failed, the word "Error" will be shown in place of the ID
- {ObjectName}_Created: TRUE/FALSE
- TRUE: It tried to create a new record
- FALSE: It tried to update an existing record
- {ObjectName}_Success: TRUE/FALSE
- TRUE: The insert/update was successful
- FALSE: The insert/update failed
- {ObjectName}_Error: In the event of a failure, this will contain the specific error returned from the Salesforce API
- The error text can also be found in the DemandTools Logfile
- {ObjectName}_UpsertedID: Will contain the Salesforce record IDs it attempted to update or insert
- The remaining columns will contain the input data
