DemandTools Help for the MassEffect Module Version 1.8
		August 14, 2007
What is new in 1.8: SCENARIOS can be saved and scheduled Exports can gather information across tables Batch is default Merge options are now available on a field by field basis and are accessible by right clicking the field name Filter long picklist in the export screen   INDEX Update, Insert, Delete Records STEP 1 - Select file type and Select File STEP 2 - Select Worksheet/Table STEP 3 - Select Operation STEP 4 - Field Mappings (Assign or Load) STEP 5 - Select Rows to Process STEP 6 - Process Exporting Records Record Id's Log Files

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.

IF YOU ARE USING OFFICE 2007, DEMANDTOOLS WILL NOT RECOGNIZE THE FILE TYPES SAVED.  SAVE ALL DOCUMENTS AS 2003 OR 2005.

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.

NOTE:  Whenever you se "ID" after a field name, you are referencing another object in sf.com and YOU MUST have the sf.com ID for that field (not the name).  For example, OwnerID, ParentAccountID, CampaignID, etc.

	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
	Migrate data from legacy systems
	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

If any new fields have been added to Salesforce.com make sure to click on

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 simply select the "Update Newly Added Fields" button to reload your field data.

Back to Top

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.

Back to Top

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

Back to Top

STEP 3 - Select the Operation and Process Type

Back to Top

STEP 4 - Field Mappings

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.

AutoMap	By right clicking the table name you have the ability to auto map your input data into salesforce.com.  The column name in your spreadsheet must match either the sf.com display or internal reference name for a field to auto map (to see the internal reference name, check teh "Show MassEffect Field Name" button in the Preference/Options menu.  If a field cannot be auto mapped, it will remain black on teh left hand side of the screen.  Once it is manually mapped, it will turn blue.
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. NOTE:  If you change the unique key, the following options are not available: Restore File Special upate option:  Update Only if Empty Merge options
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: For a picklist field, the constant must be a value that is valid in salesforce.com Text cannot populate a date field or a numeric field A numeric field with a limitation on the number of places to the left or right of the decimal point can not be exceeded	Pick List Constant Example Date Constant Example -----------Text Constant Example
Special Update Options	By right clicking a mapped field (the one in blue) you can assign individual field update options ("UPDATE" and "MERGE" options only available when using the sf.com ID as key): REMOVE a field mapping UPDATE field only if it is blank in Salesforce. Data will not be processed if anything exists in the salesforce.com live instance field. MERGE input data (Multi-select lists, text area, Boolean (T/F)).  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”. For boolean, "T alwasy wins", so if your input data contains F, but your sf.com field contains T, the resulting value will be T.
Scenarios	Utilize Save Scenario, Load'Scenario, or Clear Scenario 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. Scenarios are schedulable.

Back to Top

STEP 5 - Select the rows to process

The Control Box allows you to only process a portion of your worksheet (only applicable to XLS or CSV input files). 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.

Back to Top

STEP 6 - Process

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 log the errors in a log file.  A direct link to it’s location will be displayed. You can search teh log file for "status Code" to quickly locate the error(s).

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 an XLS file and is located in the same location as your input file and will have teh same name with the added extension IdSave0.xls (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 XLS file as an input file and run a mass delete of these records (mapping on SF ID). By default, the file will be created as XLS, you can change the defalut file type for IDSave files in the Preference/Options menu (same setting used for Restore files).  If you are inserting more than 64,000 records, be sure to change the default file type to something other than XLS (i.e. CSV).

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.

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 stored in the C:\Program Files\DemandTools\Restore directory. NOTE:  If your job returns anything OTHER THAN 0 ERRORS, be sure to check yoru log file for the nature of the errors.  Correct your input data dn reprocess any failed records if necessary.

Back to Top

EXPORT Records

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.

To apply conditions to lengthy pick lists, right click the list of values to bring up a Filter menu:

The data from salesforce will be retrieved at up to 100 records per second but will depend on current internet traffic.

Back to Top

LOG FILES

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