DemandTools Help Version 1.71 |
 |
|
June 29, 2007 |
||
OVERVIEW
The MassEffect Module allows you to mass update, insert, and/or delete any salesforce.com object, including custom objects, from an external data source, such as excel, csv, access, or UDL data file. Additionally, you can mass export any salesforce.com object to any of the same data sources. The goal is to make bulk export, bulk loading and bulk editing of your salesforce database a simple and fast task.
The potential uses of this tool are limitless but in most cases it is used to update critical information for several hundred (thousand) records that would take significantly longer if done via the salesforce user interface.
Some Common Uses for MassEffect Include: |
You want to be able to save multiple mapping files for later use. |
|
Update information from a CSV, XLS, MDB or UDL file into salesforce |
|
Update any salesforce object including custom objects |
|
|
|
Insert products and price books |
|
Insert attachments from files that reside on your local PC (includes image files, XLS, word documents etc.) |
|
Insert new Tasks, Opportunities, and Events – any object in salesforce |
|
Mass deletion of a record set |
|
Populate newly created custom fields |
|
Updating an object using something other than the salesforce.com ID as a 'key' |
|
Loading new objects |
|
Export the full or partial contents of any table, or a subset, to a CSV, XLS, MDB or UDL file |
|
You want to schedule regular data loads, such as nightly imports |
|
Repair incorrectly imported data |
UPDATE, INSERT, or DELETE Records
STEP #1 - Select the file type and Select File
Select the appropriate radio button for the file type of your input data source, and then “select” the file. Once the file is located, select Open.
STEP #2 - Select the Worksheet/Table
When you load your file all worksheets and tables will be listed. Highlight the Worksheet/Table you wish to process and then select "Use".
STEP #3 - Select the Operation and Process Type
Insert |
Creates a new single object |
Update |
Updates objects based on a matching key
–The key is usually the salesforce unique ID but can be any field
–Keys are case sensitive
–Boolean fields 0 = false, 1 = true
–Multiselect fields must be separated by a “;” character
–The tool will show all “Updateable” fields but some may not be updateable in conjunction with other fields. This information will be shown in the log files
|
Delete |
Removes objects given their salesforce.com ID. |
|
Batch Process: If you are doing a large import or update you can select to use a" Batch Process." By using this the updates and inserts are up to 15 times faster than in non-batch mode. If you are inserting objects in "Batch" mode the tool will also create a CSV file (same name as the input data with a unique number plus .CSV extension) of the created salesforce.com Id's. The only issue with working in "Batch" mode is that the error reporting is not as good as non-Batch mode. Process Blank: Checking this box will allow you to overwrite a populated field with a 'null' value. Restore File: If you are performing an update operation on your salesforce.com data and would like to create an undo file you can simply check the "Restore File" checkbox. This will cause your update to take longer but it will save a CSV file of your current data state for the fields that you are updating. To "Undo" an update you will simply load the saved CSV file and use the same mapping and reload your original data. |
|---|
The tool displays the available columns from the data source in the left panel. The objects available for modification appear in the middle of the screen. Open the object you wish to update by selecting the “+” sign to the left of the object. All the updateable fields in this object will be displayed. Simply left click to drag and drop the field names in the data source to the matching field to update in salesforce.com. If you make a mistake you can remove the mapping by right clicking on the field and selecting “remove”, then re-map correctly.
| Selecting a Key ID | If you are doing an update operation you will need to give a "Key" field. By default this is the salesforce.com "Id" field but you can right click on any field and set it to be your key field. The “light bulb” symbol will change to a “key” indicating that this field is being used as the matching key. |
 |
|---|---|---|
| Setting a Constant | If you would like the exact same value to appear on all the records you are updating and/or inserting you can simply right click on a field and select “Add Constant”. You do not need to populate all rows in your input spreadsheet with the same data. You will be prompted to enter only acceptable values in the field f or instance:
|
Pick List Constant Example
|
| Mapping Options | Update Newly Added Fields: When you initially start the MassEffect tool it will load all of your available salesforce.com tables and cache the field names to disk so that it will load quickly on future loads. If you make changes to your salesforce.com fields (i.e. add new custom fields or objects) you can simple select the "Update Newly Added Fields" button to reload your field data. Utilize 'Save', 'Load', or 'Clear' to process multiple update files using the same mappings. If this is going to be a repetitive update/import you can save your mapping so that the next time you simply load the data source, load the mapping and press the "Process" button.
|
 |
STEP #5 - Special Update Conditions
'Update Only if Field Empty' - Allows for the processing of files to populate only those records with fields of missing information. Data will not be processed if anything exists in the salesforce.com live instance field.
'Merge MultiSelect Fields' - DemandTools now gives you the ability to concatenate Multi-Select field values. If the value you are merging exists in the picklist, the value will become an additional value rather than a changed one. For example, if your input file contains “apples”, and salesforce currently contains “oranges”, the updated field in salesfore will now contain “apples;oranges”.
Restore file is required to process either of the Special Update Conditions
STEP #6 - Select the rows to process
The Control Box allows you to only process a portion of your worksheet/table. The default values are from row 2 through the end of the data. **Reports exported from salesforce.com have approximately 5 rows at the end that are seen as relevant data. Processing these rows will generate errors in the error log. |
 |
When you are ready to update, insert, or delete your data you simply press the "Process" button and the data is sent to salesforce.com. If there are any errors in the update/insert you will be informed and the application will save a text file of the error. A direct link to it’s location will be displayed.
If you are running an insert an additional file will be created containing the SF ID’s for the newly created records. This file is a CSV file and is located in the same location as your input file with the added extension _0.CSV (the zero will increment if you process the same input file multiple times). A link to it’s location will be displayed along with the log file. If you discover that you need to “undo” your insert, simply use this CSV file as an input file and run a mass delete of these records (mapping on SF ID).
If you are running an update and have elected to create a “restore” file, it’s path will be returned as well. You can “undo” your updates by reprocessing your restore file.
To export the contents of a table, simply right click on the table name and the export data dialog box will appear. Exporting data is commonly used for the following purposes:
1. Find the SF ID fields of the users and roles to use for inserts/update
2. Find the SF ID’s of objects that will become parent ID’s for other objects you are inserting/updating.
3. Export data for reporting
4. Find the SF ID’s of objects you wish to mass delete
5. You may also wish to export data, make modifications in your spreadsheet and then re-upload the changes.
The DataExport dialog box allows you to select ALL records in an object for export or just specific ones based on conditions. You build the conditions by selecting the field name and setting the criteria. Picklist and Owner fields are shown as checkboxes. If you want to OR multiple values from the same field (i.e. BillingState = OR or WA) you simply separate the items with a comma. Continue to select “Add Condition” if you want to further refine your select. You can also OR different fields by clicking the “OR all conditions..” radio button. Lastly, you can choose to export ALL the fields from the object or a subset in the “Fields to Export” section. Once all your options are set, select “Export Data”. You will be prompted to enter an output file in which to store your export. This can be either an XLS, CSV, MDB or UDL file.
The data from salesforce will be retrieved at up to 100 records per second but will depend on current internet traffic.
- Log files are stored in the C:\Program Files\DemandTools\LogFiles directory.
- A separate log is created for each day.
- Insert operations return an ID list stored in the same folder as your input file.
- Restore files are also CSV files and are stored in the C:\Program Files\DemandTools\Restore directory.