I need to bulk-change prices. The quickest would be to use SQL. However, I only found “tabItem” with prices in it so far.
Which tables would I need to update?
The Buying and Selling prices are stored together in the DocType 'Item Price' (SQL table name 'tabItem Price')
There are date ranges in there (Valid From, Valid To). So you have a choice:
- Update the existing Prices, and change the price value.
or - Update the existing Prices, and enter a “Valid To” date.
- Insert a new, additional row, with a starting “Valid From” date, and the new price value.
Either way is fine; just depends on whether you prefer to maintain a history of older prices, or not.
Excellent, thanks! Is that really the only table one needs to change? Or will changes there “travel” into the other places? (such as tabItem)
that said, I’d need to join with tabItem anyway to group by item_group
You are welcome! 
In version 13, for Buying and Selling, that’s all that is needed. The buying and selling prices are not stored separately on 'tabItem'. (very good question, btw)
I am unsure about v14 (I haven’t upgraded yet)
Yes, if your pricing depends on Item Group, you’d have to perform a join to fetch that. 
SELECT tabItem.standard_rate, tabItem Price.price_list_rate FROM tabItem Price JOIN tabItem USING(item_code) WHERE price_list = "Standard Selling" LIMIT 5;
“tabItem Price.standard_rate” and “tabItem.price_list_rate” CAN have the same value but are different things.
Thanks Brian!
The Item’s 'standard_rate' is an unusual field:
-
Mostly, it’s -only- used during Item creation. The value of ‘standard_rate’ automatically becomes a row in
'Item Price'.
There are some exceptions:
- It’s sometimes used as a fallback Cost price, when
'valuation_rate'is empty. - A few modules like Restaurant and Healthcare reference the ‘standard_rate’ (they probably should not)
After item creation, it’s hidden on the web page:
Sales Orders and Sales Invoices don’t pay attention to 'standard_rate'. They look directly to the Item Price table for answers about default prices.
For your reporting and mass updates, I think you can safely ignore it.
Here’s the SQL I use to fetch the current, active Sales Price:
SELECT
price_list_rate
FROM
`tabItem Price` AS PriceTable
INNER JOIN
`tabItem`
ON
tabItem.item_code = PriceTable.item_code
AND tabItem.sales_uom = PriceTable.uom
WHERE
PriceTable.item_code = 'some_item_code'
AND PriceTable.price_list = 'Standard Selling'
AND IFNULL(PriceTable.customer,'') = ''
AND PriceTable.selling = 1
AND IFNULL(PriceTable.valid_from,'2000-01-01') <= DATE(CONVERT_TZ( UTC_TIMESTAMP(), 'UTC', 'EST'))
AND IFNULL(PriceTable.valid_upto,'2500-12-31') >= DATE(CONVERT_TZ( UTC_TIMESTAMP(), 'UTC', 'EST'))
ORDER BY valid_from DESC
LIMIT 1
2 Likes
Hi Brian!
same issue one year on, have to keep increasing those prices during inflation (are you in the UK even?). Anyhow, as I have been finding my own answers to my own questions on stack overflow for decades, here’s another greeting from the past;)
Bulk-increase all prices by 6.8% on Jan 1st:
at 00:01 1.1.2024
at>mysql -u root -p[mysql password] -e 'USE [db name]; UPDATE `tabItem Price` SET price_list_rate = price_list_rate * 1.068 WHERE price_list = "Standard Selling";'
at>
CONTROL-D
(adjust factor and constraints until tender)
Being an sql person, I have used the above approach a couple of times without problems now. Yet there might be repercussions in data consistency as tmatteson pointed out in 105729, so here’s his approach :
items = frappe.get_all('Item', {'item_group': ['in', ['item group', 'other item group']]}, 'name', pluck="name')
for item in frappe.get_all('Item Price', {'item_code': ['in', items]}):
item_price = frappe.get_doc('Item Price', item)
item_price.price_list_rate = item_price.price_list_rate * 1.068
item_price.save()
frappe.db.commit()
Alas, with peterg’s explanation of the ORM (again in 105729) I see no harmful consequences with the vanilla sql method. But please correct me if I’m wrong.
Merry Christmas everyone!
1 Like
It is a bad practice.
I would use Data Import Tool. It will create a spreadsheet which you can update all rows. And then upload it.
PS: You can also
item_price.add_comment('Comment', 'New price calculated by 1.068. Responsible: X From selling department')
That way you maintain some logs in the comments section. It will help lot if anything goes wrong.
PS2: You can also use Bulk Update Tool.
Good point, Turker, didn’t know about the Bulk Update. Logs and comments safe lives.
Hi @kombi,
Tyler and Turker are correct: using Python code, Data Import, or Bulk Update are the suggested, approved solutions.
The risk of using direct SQL is this:
For your particular DocType, there might exist DocType Event methods (‘before_save’, ‘on_update’, ‘on_change’, and many others). Those methods could perform important validation, ripple changes into other DocTypes, update Links, etc. By using direct SQL, those events never fire. Potentially causing a wide variety of data problems, or breaking the referential integrity of the database.
So unless you really know what you’re doing…it’s best to stick with the official, best practices.
With that being said. If you’re intimately familiar with your DocType, and you either know it’s safe, or you’re handling the side effects yourself? Cool. You do you.
In version 13, Item Price and I are old friends: I’ve worked with all its code. So I’m extremely comfortable just doing direct SQL…occasionally. I don’t make a habit of it. There are other tables I run pure SQL against too. For example, because looping through Documents and calling methods increases the runtime by many orders of magnitude.
However…there are other DocTypes/tables that I wouldn’t dream of altering directly, without calling Frappe document methods.
Your own environment and experience may vary!
(I’m located in the northwestern United States, btw. Yes, inflation is -rough- the past few years!)
I catch you guy’s drift! While we’re at it, I am delving into a new thing and will probably start a new thread about it but maybe you can tip me off already. In Short: We need performance periods in invoices generated by subscriptions.
So my approach would be to somehow detect the interval from the Subscription Plans sub-table and add a custom dropdown in each subscription offering last / current / next period. The resulting invoices should than state i.e. the previous/current/next week, month, year or whatever.
As it seems hard to fetch information from sub-tables using javascript this might have to happen during invoice creation. How to hook into that process is still a little opaque to me, I would guess it is triggered by the scheduler?
Sorry, ot! Will start a new thread
Hello
Frappe v15
Another interesting method of updating multiple documents (aka records) simultaneously without having to open each document, bring about the changes and then Save, is to use the Report View of the DocType.
This method is clearly not limited to the Item Price DocType and updating it’s Rate field, but can be applied to any DocType and any field, even from a child table, provided the field (column) is exposed in the report’s list of Columns which can be displayed.
For the Item Price DocType, ensure the Rate column is displayed (Item Price > Report View > Menu > Pick Columns) and then simply edit the values in the Rate column in-situ. Also note, there is no need to Save, when the cell looses focus the value it is automatically saved.
This seems to work for Administrator, but I can imagine it’s something which you do not want all users to have access to.
1 Like