ERPNext Foundation ERPNext Cloud User Manual Blog Discuss Frappé* Donate

Error updating price list via data import


#1

It doesn’t seem possible to update the rate of an existing price list via data import. I get the following error;

Item Price appears multiple times based on Price List, Supplier/Customer, Currency, Item, UOM, Qty and Dates.

I am using document type Item Price and action Update Records and I’ve varied the columns I keep in the Excel document in every way I can think of from all columns of data to just thee, Item Code, Price List, and Rate. No luck.

I’ve even done imports that say Success but the pricing didn’t change.

Any ideas as to what could be the problem? Do I need to include Brand or Manufacturer? I assume that Item Code and Price List names are the only two match fields Frappe compares before making the changes.


#2

Ok, I will take a shot at this for you…

First of all, since you did not mention your version, I am going to assume you are using v11.

Secondly, you must download a template from the data import/export tool that includes your current data populated in the spreadsheet.

Now, the rest of what I tell you will reference the below screenshot of one of my v11 “Item Price” downloaded templates:

image

We are only interested in columns B, C, D, and E

In column B you will find a cryptic looking string in quotes.
If you are going to UPDATE records, then this column must be pre-populated in the template when you download it!!! If you download a blank template then column B is empty and all records you put in the template will in fact be used to create NEW Items in your price lists. The ONLY way to UPDATE a record is if you have the strings populated in column B first and they can only come from the database when you download a template ‘with data’

You can see in the example screenshot above that column B is already populated (as well as the others).

So, now all you nave to do is replace the values in column E with your new prices and save the spreadsheet. This new saved spreadsheet will be what you upload to the Import tool in order to change your prices.

A word of caution…

Break your price list up into groups of one thousand or less in order to keep the buffers from being over-run during the import process. If you know that your ERPNext server is running on less than 4gb of memory, then you might want to cut that number in half again just to speed up the process and reduce errors.

Hope this helps you out. These are my solutions and… Your mileage may vary :grin:

BKM


#3

OK, I think you hit the nail on the head, I did not include the ID column. I’m very familiar with FileMaker and they use the term “match field” to identify which fields FileMaker uses to find matching records. Some kind of language like that would be good in the description of the templates.

This also explains why I got multiple entries for the same price list after my import, it was creating new records because the ID field we not present. Since I’m still in the learning/evaluation stages I simply deleted all the inventory items where I screwed up the price lists and will start over.

Thanks for your guidance


#4

Please try the new instructions and if they work out for you then come back to the forum and mark this as a solution so that others will also be able to benefit from your question.

Welcome to the forum!

BKM


#5

This solved my problem. I was not including the ID column in my template so ERPNext assumed I wanted to add new records. I re-exported the data, filled in the new pricing, and uploaded it WITH the populated ID column and things worked as expected.

Thanks for the quick reply!