Apex Code Development You need to sign in to do that
Don't have an account?
MetalHead Price List Upload
Hello,
I am a new user, and I am not a computer programmer.
I am trying to import a 3-column price list into SF Pro. 1) Part #, 2) Part Description, 3) Price.
I thought this would be simple So far I have been given a 6-page .pdf that makes no sense for what I am trying to do. It says I have to use the Excel Connector to import products into the Products 2 table, ensure that each product is linked to a Pricebook ID, and then import into a price book entry table.
Sounds good, except I have no idea what any of that means. I was able to get the Excel add-in for Salesforce connector to work, that seems to be important in the forums I have read.
Is there any manual, or procedure available for importing a simple 3-column price list, that a lowly Marketing Manager guy could understand?
Thanks for any assistance.
However in my previous post, Price List upload - explained I did list in the very, very last section a link to my google doc as an examples. However I did what to point it out here just to call it out. I explain in the doc about certian fields and forumulas I'm using so I hope this helps.
link to the example.
http://spreadsheets.google.com/pub?key=p67bXC7rKoqbr7GrP0yzdbg
All Answers
In case any one else had the same question.
I was told today that importing products can only be done manually, or through the data loader with Enterprise Edition.
I am new to salesforce, currently setting up the system for my company.
Now stuck with this massive work of keying in the products + price.
I have the data in excel.
But i dont understand this Excel Connector guide as well.
What is the easiest way to do this?
Sleepless.
Once I got the ADL, I was equally frustrated, until I talked to a Support guy named David Rozier, who has his own written procedure for using the Data Loader.
Thank you so much for the tip.
That excel connector is a waste of time.
I will download the ADL today, and perhaps try to locate this support guy David R. you mentioned.
In my excel spreadsheet, I just have like 5 columns (Product Family, Product Name, Product Code, Product Description, Product Price) and about 500 line of items. This is probably an easy task for the ADL to import the data into SF right?
Thanks for taking the time to guide me.
It does not make much sense, but i will follow your instruction step by step.
Wait for my good news.
I managed to import the product list in, but not the standard price. Basically i just want to key in 0 on the Standard Price List for now.
I got stuck following your instruction starting here:
In ADL choose Export. Go to PriceBook2. This will save your price book IDs in a file on the desktop.
When exporting "PriceBook2", i got stuck on step 3: Edit your query.
What am i suppose to do here?
What do you mean by "In your SUCCESS FILE, create a new column beside ProductID, called PriceBook ID. Copy the Standard Price Book ID, and copy all the way down the rows."
In the ADL choose import, i also cannot find "PriceBookEntry".
I really hope you can help me. I am like so close yet so far.
Thanks a lot.
Hey,
1) For products, you have to have the column for prices, and map it to price. You ALSO have to have a column called UseStandardPrice, that says FALSE in all the rows, and map this to UseStandardPrice in ADL. If you want the price to be $0.00, use $0.00 as the price, but still creat the column called UseStandardPrice and make it false.
2) For Price Book Entry: in the main menu of Import inADL there is an unchecked button that says "Show All" or something, you have to check this first, and then scroll down, it will be there.
For Step 3, just check all the boxes in the query fields. It will generate a file called extract.csv on your desktop. In this file will be the PRICEBOOK ID's that you need to make the file for PRICE BOOK ENTRY.
When you import the file for Price Book Entry, you need to have the Product ID, the Price Book ID, the Product Name, the Product Description, IsActive, and IsStandardPrice as columns in your Excel Sheet.
The Price Book Id's will all be the same, since you have to import all the products into the Standard Price Book. You can map products to individual Price Books later.
The Product Id's will all be different, and you get these from the succes file from importing Products2.
When you did the Products2 import, you should have got a file on your desktop called Success009......
If you didn't get this file, search for it on your computer, or try Heaven or David in support, since I have no clue. I only know a very limited scope of this, and I can't believe that it is so complicated to do such a simple task as importing a price list.
Good luck.
I have read through this thread and am scratching my head. Everything that you are doing with the Data Loader can be done with the Excel Connector. And much faster if you don't have thousands of products and numerous price books. There are quite a few threads here about using the Connector to import and update Products and Price Books. You can do a search with somethng like "excel connector products."
I can only surmise that the reason that Support told you that it cannot be done with the Connector is because the Connector is open source and not officially supported by Salesforce.
Hi Harry,
This thread was started be me by asking if anyone knew how to import a SIMPLE 3-Column Excel sheet into SF.com using the Excel Connector. 1) Part #, 2) Part Description, 3) Price
There were no replies.
The same question was asked of the sales team, where I was told that they could look into the situation for a fee of $245/hour. I wasn't up for that, and found it a a disgraceful request.
When support was called, I was told that the Connector is unsupported as you mention. My reply was that I didn't need support on the Connector, but that I needed support on how to Import a simple 3-column spread sheet. There was no assistance.
Finally, I spoke to another support person, who told me that what I was trying to do, and what the first guy wanted to charge me for, was not possible in SF.com.
I finally got great support from David Rozier and Heaven in support, along with my Sales Rep, who helped me get set up.
This thread has been continuing because Trebla has had the same difficulty as I had. Your comments are about as helpful as the orignal support group.
If you can find a procedure for importing a simple 3-column Excel sheet into SF.com with the Excel Connector, please post it here, as this is the original question on this thread.
There are still NO REPLIES, and there are now 2 people who have searched. Thanks in advance for posting these instructions.
Thank you so much again for your patience.
This is certainly more helpful than Harry's comments.
I am truly grateful.
Hi Trebla,
Harry is probably a developer or computer programmer. A lot of these people are very condescending towards customers and people with limited computer skills.
For some reason they assume people like you and I are not intelligent enough to use a search function on a website. They forget that the reason people choose programs like SF.com is for the exact reason to avoid IT people. I think they also forget that it is an expensive program, and that people like me are succesful enough in other areas to be able to afford the program in the first place.
After toiling over the problems I have described at length in this thread, the reply is that he is scratching his head at why we didn't just search for Connector in the communtiy website??
THANKS SOOOO much, should have thought of that. Apparently he missed the part about the company's own support group stating that it is not even possible. Also, my ORIGINAL question about inserting a simple 3-column spreadsheet is now 3 weeks old, and is still unanswered by anyone on here or in the company in sales or support by email and telephone. As I said before I found 2 people in support who were good people, otherwise I would have been the shortest subscription on record with this company.
Hope you got your products in so you can get back to business.
I would like to see if I can help not by giving you "how" to do it, for it appears your on track and I don't want to derail you. However I want to try and help with the Why, not that you may care at this point but I've found sometimes knowing the whys helps with the hows. What your doing is what I think the HARDEST thing about Salesforce, less the APIs, in the 5 years I've been an admin using Salesforce.
I chuckle at the thread and the issues you're having for I've gone through them myself and that was right after they introduced these product2 and pricebook2 tables, what was that all about? I think it was just for these reasons I hope to explain because Salesforce got it wrong the first time around, however it introduces your comments MetalHead,
Let's say we have products Zit and Pipi. We sell into different industries: Education, Government, Service Providers and Enterprise. For the Education we sell Zit for $120, and Government and Enterprise for $160, Service Providers $200. However Pipi is only sold to Education for $100. So the question I have for you is: How do we manage this? MetalHead as you stated you have price in your excel which you want to add to your price to your products, however we can't store the price on the product because there can be multiple prices per product. Also what about specifying that Pipi is only for the Education? I think if you understand this one concept you'll start to understand why it's done this way and what needs to happen for you to in upload products and prices.
The Product2 table just stores the product information like, product code, name, description, product family and other custom fields. We use the Price Book table to create those different price books one for each of our industry as above. All it contains is the names of the price books, Edu, Gov, SP, and Ent. So we can have a product, Zit, sold in multiple industries. As well as an industry can have multiple products being sold to it. This is known as a many-to-many which we need a third table which in our case is the pricebookentry table. Below I hope this diagram helps.
As you can see the pricebookentry record has all three key elements, the product, price book and price. This allows one product to have multiple prices, and allows a selective product(s) to only show up in one pricebook. Therefore when you create your price list you need to first create all products. If your going to have multiple price list then you need to create them second. If not then just use the Stand Price list. So that's the fundamental piece however now let's more to the nuances of Salesforce.
In Salesforce there is one price book which is called Standard. You can think of this as the master price book which should have every product you plan to sell. This price book comes into play with that pesky "IsStandardPrice." Let's say the Zits price for Gov and SP is the base price from which we uplift or discount all other industry prices. In doing so we want to then set the price of the Zits product in the Standard Price list as 160.
Here I've added two other line items ZitsStan and PipiStan to show the base price for our two products. Now let's say the base price for Zits has changed. What would we have to do to reflect that change for the Gov and SP price books. We would have to make two changes. Wouldn't it be nice if we could just change one price and have it reflect across ALL price books? And that is why there is a flag called Is Standard Price. When you change the "Standard Price Book's" price for a given product that price will change in the other price books.
So when you're inserting records for the Standard Price book just be sure to mark your product as active, isActive = True, and isStandardPrice = False. You need to set it to false because it is the standard you're not pulling the standard. When your inserting to a different price book and want to use the Standard price then you need to set your "UseStandardPrice" as True and the price (I can't remember which but make price either the same as the standard price or nothing, I think it's the same)
So that's what I can offer and unsure this helps however it's here for posterity. As for as my opinion I like the excel connector because it removes that one step about the success records file. Once you insert your records the file you're working with has the ID right there.
Here is my example of the excel connector template I use any time I deal with products and price books. To me it's just easier since I'm already there in Excel. One key point to note is you have to say 'new' in the ID column to insert your records. (I've changed all prices to reflect dummy prices)
Message Edited by fifedog on 09-05-2007 10:09 PM
Just a brief note about your experience with Harry and Support, that sucks and a bummer. However not all unexpected. As far as Harry goes you're right he sounds a bit more technically and some times in our rush we forget the audience, however I knew this would be the case when I started the board 5 years ago.
I'm not a SFDC employee nor have I ever, nor do I think I would. I only hope that you've found the boards a valuable resource in getting your questions answer. I was a user just like you wanting more help since SFDC support didn't have all the answers for me to become successful. There are answers to their program then there are real world answers which we can only glean from one another and for posterity sake these conversations can help the next wave of users.
MetalHead and Trebla,
While this forum is valuable, you have to remember that only a small number of users visit it. And most of them, myself included, probably only take a look when time permits and may not have time to read and/or respond to all the entries.
I kept my response short because you had found a solution and I simply did not have sufficient time to go into the details as fifedog did. (Thanks, fifedog.) My "head scratching" was about why SFDC support had told you that it "could not" be done with the EXCEL Connector. And my intent was to encourage you to not give up on that tool. While I do some VBA and can bungle through a smidgen of HTML, I am hardly a programmer. Heck, I take a look at the code behind the Connector and can barely figure out what is going on. But I don't have to understand it all to use it.
You do have to understand that the data in Salesforce is arranged in tables and how various tables are related to each other. But the Connector is helpful just for that purpose. Sometimes I simply fire it up to see what fields are in each table and the relationships to other tables.
Since the Connector is an SFDC "stepchild," information about using is scattered around and you will need to search it out. But, once you become familiar with it, it is a valuable timesaver.
However in my previous post, Price List upload - explained I did list in the very, very last section a link to my google doc as an examples. However I did what to point it out here just to call it out. I explain in the doc about certian fields and forumulas I'm using so I hope this helps.
link to the example.
http://spreadsheets.google.com/pub?key=p67bXC7rKoqbr7GrP0yzdbg
I will try to consolodate what I learned here to give back to this forum as it has helped me many times.
Fifedog- thank you as well. Yesterday I was all over the place and couldn't get this to work even with MetalHeads excellent notes.I understand enough of the API structure that it really came together when I reread the post today. I 've spent so much time pouring over the WIL thread and many others over this past year that I feel like I know you.
This is an indispensable resource and I really appreciate all the input. (especially after suceeding with this after about 8 unsuccesful hours)
-Kringo
Thanks SO much for sharing this - it is very helpful. I am interested in your opinion - we sell multi-year licenses and asp software services to financial institutions and have tiered pricing based on the size of the institution. There are probably around 1,000 different products an FI could buy from us.
Each "product" has potentially at least 4 separate components to price (unique products in SFDC) - such as license, annual maintenance, implementations, training. They are all different types of revenue and thus recognized differently by our accounting folks. So for every product we sell, I need (in this example) 4 unique entries in the pricebook. And that's just the licensed version. If they want they ASP version, I have monthly fees, implementations fees, training fees at least. So now we are up to at least 7 entries into a pricebook per product from us.
Since those 7 items have different prices for the different tier ( we have 7 tiers) sizes (and we rarely sell anything at list price - its typically less than list price), I have only entered the items once with a standard price of $0 in order to avoid the pain of updated and maintaining. The reps know that they have to enter the products into their opps and then enter the selling price, based on the size of their prospect.
You can imagine how long the list of products is for some opportunities if they buy (and they usually do) multiple products from us.
I'd be interested in your thoughts on our practice and if you would set it up differently.
Thanks,
Jane