Hi,

We receive signed agreement documents from our customers.  We have a quota of 10 documents received per week.  We track the date of receipt of each document. I can generate a report to show the cumulative documents received by week.  I'm not sure how to generate a chart that shows, on a weekly basis, the difference between the cumulative documents received and the cumulative goal.   Do I need to use custom summary formulas for this?  

Can anyone help me with this?  Thanks.

I wanted to pass along the steps it took me to finally import our price books into SF via the Excel Connector for Professional Edition.  As a beginning price book, SF, SF community and Connector user, I had to do a bunch of searching and looking at different threads to piece together a method which was successful for me, so I'd like to post it so others might have less hunting to do. 

We are a small company that sells software products, and do work with U.S. Commercial and Gov't clients, as well as International.  As a result, our total products number is ~400, in three different price books. We use SF Professional Edition, and paid extra to get the Products/Price Book functionality.  The most valuable site I found was this Google Doc (http://spreadsheets.google.com/pub?key=p67bXC7rKoqbr7GrP0yzdbg&gid=1) shared by contributor fifedog on another thread:  http://community.salesforce.com/sforce/board/message?board.id=Excel_Connector&thread.id=550&view=by_date_ascending&page=2 .  It was very helpful, but some things were still not clear to me.

Here are the general steps it took to finally get our price books set up:

1. List all of your products, product numbers (part number, serial number, etc), product descriptions and prices in one spreadsheet, which will be used for the "Standard Price Book".  For instance, we provide training for our software, but at different prices to our three different client types (Commercial, GSA and International).  The three different prices for essentially the same product will be considered three different products.  If you used Excel to make this spreadsheet, shut Excel down after you create this spreadsheet.

2. Go to the "Manage Price Books" link on the Products page on SF and Activate the Standard Price Book if it is not already.  This will allow the Excel Connector to get some info you need.

3. Download and install the Office Toolkit provided by salesforce (http://sforce.sourceforge.net/excel/install.htm )

4. Download the Excel Connector (http://blogs.salesforce.com/features/2006/05/excel_connector.html ).  Place the .xla file in the following directory:  C:\Documents and Settings\<User>\Application Data\Microsoft\AddIns. DO NOT click on the .xla file, as doing so will cause headaches later (trust me:) ).

5.  Launch Excel, click Tools --> Add-ins and select the “SForce_Connect...” option and hit OK.  If you don't see this option, you didn't follow step #4 correctly, or you need to Browse to the directory where you stored the .xla file.  You should see a message about Macros (you need to enable them), and a Toolbar with “SForce Connect...” will pop up.  You are on your way!

6.  In SF go to Setup-->My Personal Information-->Reset My Securtiy Token and reset your security token.  An email will be sent with this token...you will need it to login in via the Connector.

7.  Now you are ready to start importing your Products and setting up your Price Books.  Open up your Product spreadsheet from step one in Excel so you can import the product list.  Arrange your columns and rows as seen in the “products” tab of the Google doc above, minus the bracketed numbers which fifedog is using to point out various things.  The "Product2" cell and the names of the other fields (Product ID, etc.) need to look exactly like that, except that “Part Number” should say “Product Code”.  You don't need price at this point; hence the blank column separating it from the other fields.  You put in the values of "New" in the “Product ID” column, as this will tell SF to make new records.  Once everything is arranged properly, select all of the record rows (everything below the row with “Product ID” ).  Use the drop-down in the SForce Connector Toolbar and click “Insert Rows”.  If you haven't signed in yet, it will prompt you to sign in with your username, and password (add your token to the end of the password). Hit OK at the prompt telling you how many rows you have.  After that, the rows should insert, and the “Product ID” column will be populated, which is the goal of this step.  All of your products are now in SF (minus the prices), which you should see on the Products page in SF. Yay.  Fun huh?

 The next three steps involve setting up the Standard Price Book, which was the trickiest part for me. 

8.  Arrange another spreadsheet like fifedog did in the “Price books” tab of the Google doc.  Set up the top two rows the same, excluding the “System Modstamp”, “>” and date cells—these get filled in by the Query you will do next. Don’t put any records in yet.  Once you have the top two rows set up, select the “Pricebook2” cell and use the SForce Connector toolbar to “Query Table”.  You should see one record pop up, called “Standard Price Book”, and the “Is Standard Price Book” value is “TRUE”.  I needed to make sure I completed Step #2 above before any of this would work for me. On the Google Doc, it looks like fifedog created the “Standard Price Book” row, so mimicking that I created that price book row and got really confused. Not needed…it already exists in SF and is imported; I needed to Activate the Standard Price Book in SF before I could get this information into Excel. 

9.   Now, you can insert rows (with “New” in the ID column) with information for each price book you want to make.  My company has three price books: Commercial, GSA and International.  They are not the Standard Price Book, so “Is Standard Price Book” is “FALSE”, and I want them to be Active (available to the sales reps), so that value is “TRUE”.  The goal here is to get the “Price Book ID” number after it is created in SF.  Select the new rows and click “Insert Rows” on the Connector.  Voila: “Price Book ID” numbers populate.  You should now see those price book names in the Price Book drop down on the Products page in SF.

10.  Arrange a new spreadsheet like fifedog has in the Pricebook Entry tab on the Google doc, with the value “New” in all records under the “Price Book Entry ID”.  The first price book you need to set up is the “Standard Price Book”.  ALL of the products and their prices need to go into this price book, so that a standard price is set and usable for the more specific price books.  Use the ID for the “Standard Price Book” (from step #9) for the “Price Book ID” column for all of the product rows.  The “Use Standard Price” column should be “FALSE” for all records, as we are setting the standard now, not looking for it from somewhere else.  “Active” values should be “TRUE”.  Notice you do not need the product name or part number columns any more.  They are all known now by their “Product ID”.   Once everything is in, select the rows and click “Insert Rows”, and the “Price Book Entry ID” column will be populated.  You can verify successful export into SF by looking at the Standard Price Book on the Products page in SF. 

11. To populate the other price books, you basically follow step #10 again for each, but change the “Price Book ID” to the appropriate number from the spreadsheet in step #9, and only make rows for the products that pertain to that price book.  For instance, I don’t want my Commercial or International prices in my GSA price book; so I only copy over the GSA Product ID’s and the associated prices.  Also, you need to set “Use Standard Price” to “TRUE” for all records in these specific price books.  Again, “New” should be the value in the first column for all records until you populate this by clicking “Insert Rows”.  Verify that all of the products are loaded in the correct price books by looking at the Price Book section of the Products page in SF.  Congratulations, you have achieved the main goal of this process.  If you screw up any of the steps, you can always select the rows you want get rid of and use the “Delete” function on the Connector to get them out of SF and start over. 

Maybe this will help some of those just starting with Excel Connector.  

Hi,

We receive signed agreement documents from our customers.  We have a quota of 10 documents received per week.  We track the date of receipt of each document. I can generate a report to show the cumulative documents received by week.  I'm not sure how to generate a chart that shows, on a weekly basis, the difference between the cumulative documents received and the cumulative goal.   Do I need to use custom summary formulas for this?  

Can anyone help me with this?  Thanks.