If this is your first Products and Materials Load, we recommend reading the instructions below, then watching the video and then following along with the click-by-click instructions to complete the load.
Typically, clients will request that improveit 360's team perform the load of Products with related Materials, however, if you are an advantageous Admin, you can download the Salesforce Data Loader tool and follow the instructions below and watch this internal reference video.
Preparing your Data
- Each Client should use the "Products & Materials NEW SAMPLE" spreadsheet as a template (attached to this Article. This allows you to enter numerous rows of Materials for a single Product. Once the spreadsheet is completed, you will then create two Excel Files to use in our Load. Both Files will need to contain a Data Migration ID. You will first need to create a Data Migration ID within the spreadsheet. Do this by Combining the Product Category and the Product Name, using a formula: =A2&" "&B2 (see embedded video and follow along).
- Be sure all Cells are Formatted appropriately and do not contain special characters. For example: you may need to change Product Price and Materials Cost cells to NOT include a "$" and commas. Also be sure to check that there are no special characters (like entering a dash or hyphen rather than leaving a blank cell) as this will prevent records from loading.
- Check that all unused columns have been cleared of any data, or deleted. You can hold Ctrl+Shift+Right Arrow key to highlight all columns to the right of your last column, then right click and delete.
- Vendor information must contain the Salesforce Record ID (rather than simply typing in a Vendor's Name into the spreadsheet).
In this Example, we need to capture the Record ID for our Vendor, XYZ Distribution, LLC. I will capture the Record ID by going to the Account page. The ID is the 15 character alpha-numeric string after the "/" in the URL. (see screen shot below of highlighted Record ID)
Copy the Record ID, and Paste it into the Vendor column of your spreadsheet. Your company may purchase Products from a variety of Vendors. Be sure to do this for each Vendor as it applies to the Product.
Save the spreadsheet as a CSV File. Note that this file contains Materials as well as Products.
Next, let's remove duplicate Product rows and create a new File.
Highlight the sheet and select Data>Remove Duplicates. Unselect all columns, be sure to select "My data has headers" and choose only the Data Migration ID. Select OK. Excel will tell you how many total Products you have in your sheet.
Scroll to the bottom of the used cells. Highlight first unused row, hold down Ctrl+Shift+Down Arrow to select all empty rows, right click and Delete unused rows.
Save the Spreadsheet as a CSV File. Note that this file contains only Products.
Load the Products file
- First, we need the Data loader, we will be using the function “Upsert”
- Choose Password Authentication. Locate the System Administrator credentials for the Org, from the ISV. Enter the credentials and click Login.
- After a few moments of verifying the org information, you will be able to click Next to continue.
- Select Products Object to “Up-sert” too. Be sure to select the (i360__Product__c) Object from the list. Then, click Browse and choose the Products spreadsheet we Saved earlier. Remember, this file must be a CSV to the recognized by the Data Loader.
- Wait for it to look over the file and the org you have given it and it should pop up a smaller window showing how many records it found within the File.
- Click “OK” on the popup and on the following screen select “Data migration ID” from drop down menu and click “Next”
- On the following screen we don’t need to add in anything for “Related Objects” simply click “Next”
- On the next section, Create/Edit Map is what we want, then we will let it auto build our mapping. Validate the mapping items are to the correct values and click “OK”
Note: If you are trying to map Vendor information, you will need to use the Account Record ID as the value within the spreadsheet. The Vendor field will not support text.
- Now we should be back on the previous screen, click “Next” and it will ask you where to save the success and error logs at. Generally, the desktop is fine as these will only be needed if things fail to “Upsert”
- Once you click the “Finish” button the system will give you a warning about the action being unable to be undone.
- After you tell it “Yes” it will begin the process for uploading/updating the records given. You will see a running tally of each batch it finished and how many errors it hit, if any. These will then be logged in the error log that saved where ever it was told too prior.
Load the Materials File
- First, we need the Data loader, we will be using the same function “Upsert”
- Choose Password Authentication. Locate the System Administrator credentials for the Org, from the ISV. Enter the credentials and click Login.
- After a few moments of verifying the org information, you will be able to click Next to continue.
- Select Materials Object to “Upsert” too. Be sure to select the (supportworks__Materials_List__c) Object from the list. Then, click Browse and choose the Materials with related Products spreadsheet we Saved earlier. Remember, this file must be a CSV to the recognized by the Data Loader.
- Wait for it to look over the file and the org you have given it and it should pop up a smaller window showing how many records it found within the File.
- We don’t need to do anything on this page as we are leaving the ID fields alone. Simply click “Next”
- On the following page we need to select “Data Migration ID” for the picklist of “supportworks__Product__r” and click “Next” This step ensures that the Materials created are linked to the Product. Do not skip this step!
- Just like before we need to build a map for the system to relate the items.
Be sure you are mapping the Data Migration ID to the Product Data Migration ID field and NOT the Data Migration ID on the Material object. The correct field to map is: “supportworks__Product__r:i360__Data_Migration_ID__c”
This step is critical for the Material records to attach to the correct Product.
- It will again ask you where to save the error logs at, desktop is generally good practice.
- Once you click the “Finish” button the system will give you a warning about the action being unable to be undone.
- After you tell it “Yes” it will begin the process for uploading/updating the records given. You will see a running tally of each batch it finished and how many errors it hit, if any. These will then be logged in the error log that saved where ever it was told too prior.
If all the records have been updated and no errors remain then you’re done! Let's check one of the Products you uploaded and verify it has related Materials attached. Follow along in the video above.
Go to the Products Tab
Use the Active Products list view to locate a Product you created today and would expect to see Materials.
On the Product page, scroll down to the Materials related list.
If there are no Materials present, go to the Materials Tab and look for Materials that were created today. If you incorrectly mapped the Data Migration ID, Materials will exist but they will not be linked to Products.
If you have loaded Materials without mapping to the Product Data Migration ID, you'll want to Delete the Materials you loaded and try again. Be careful, and be sure to map the Data Migration ID from your Material load spreadsheet, to the Product Data Migration ID field. See screen shot below.