Price List in BOM Item

[ruchin78]

Hi Everyone,
I want to use Price List in BOM Item Table.
But the problem is, I am getting error when I tried to use it.

I tried it using the below codes:

cur_frm.add_fetch('item_code','price_list_name','buying_price_list')

AND I tried:

cur_frm.add_fetch('item_code','price_list','buying_price_list')

But everytime, when I select an item in the BOM Item, it gives me an error shown below:
raceback (innermost last):
File “/home/sam/frappe-bench/apps/frappe/frappe/app.py”, line 57, in application
response = frappe.handler.handle()
File “/home/sam/frappe-bench/apps/frappe/frappe/handler.py”, line 19, in handle
execute_cmd(cmd)
File “/home/sam/frappe-bench/apps/frappe/frappe/handler.py”, line 36, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/sam/frappe-bench/apps/frappe/frappe/init.py”, line 806, in call
return fn(*args, **newargs)
File “/home/sam/frappe-bench/apps/frappe/frappe/desk/form/utils.py”, line 44, in validate_link
% (fetch, frappe.db.escape(options), ‘%s’), (value,))[0]]
File “/home/sam/frappe-bench/apps/frappe/frappe/database.py”, line 136, in sql
self._cursor.execute(query, values)
File “/home/sam/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/sam/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, “Unknown column ‘price_list_name’ in ‘field list’”)

I appreciate if someone can help me.
Actually, my requirement is while using Valuation Rate for BOM, I want to show the price list rate in the detail too, so that if in any case there is no rate found in the item’s transaction then user can see that rate in Price List instead of opening the item and then see the price list.

Regards
Ruchin Sharma

[ruchin78]

Hi Everyone,

The above issue has been resolved, now I can see and select the Price List in BOM Item, but I want that the price of the BOM Item, should be shown in a separate field.

I am getting confused how to get this, I appreciate if anyone can help me in this regard.

Instead of ITEM-PRICE-00001, I want the rate of that particular item mentioned in the selected Price List.

I am using filters for the same:

cur_frm.set_query("price_list_rate", "items",  function (doc, cdt, cdn) {
var c_doc= locals[cdt][cdn];

return { 
"filters": [
["Item Price","price_list", "=", c_doc.buying_price_list],
["Item Price","item_code", "=", c_doc.item_code]
] 
}
});

I appreciate, if anyone can help me in this regard.

Regards
Ruchin Sharma

[priya_s]

@ruchin78 If you want to set Price List Rate on selection of Item Price then add two fields -

1. Item Price (link field of Item Price master)
2. Item Price Rate (auto fill when select Item Price - read only field)
   And i think there is no need to Price List field which you have added, because when set item_code, price_list in search field of Item Price then it get searched by price list.

To auto-fill of price list rate field :- Go to Customize Form → select BOM Item → open Price List Rate field.
In option set item_price.price_list_rate , rate automatically set to field on selection of Item Price.

[ruchin78]

Hi @priya_s
I tied the first thing what you said but it gave me something else.

Regards
Ruchin Sharma

[priya_s]

You want to set Price list rate on selection of Item Price … right?
First set Search Fields to Item Price form through Customize Form then you search it by Price List…

[ruchin78]

Hi @priya_s,

Trick worked for me, but still I am not getting rate.

Can you please explain what else I need to do?
Also, what does it mean?
rate automatically set to field on selection of Item Price.
I did write a code for it, but it didn’t help.

frappe.ui.form.on("BOM", "price_list", function(frm, doctype, name) {
frappe.set_value("price_list_rate",price_list.price_list_rate);
});

[priya_s]

@ruchin78

Also, what does it mean?rate automatically set to field on selection of Item Price.

means when you select that item price, respective item rate get fetch and it displaying in another field Item Price Rate… see in screen shot -

And instead of writing code you just set code in option of Item Price Rate field. Refer my above 2nd screen-shot.

[ruchin78]

Hi @priya_s

I already done it but its not working for me.

Edit:

Regards
Ruchin Sharma

[priya_s]

@ruchin78 , in your scenario your field name is buying_price_list . So instead of item_price.price_list_rate in option you try with buying_price_list.price_list_rate .

[ruchin78]

Hi @priya_s
I tried it, but getting error when the BOM Doctype get loaded.

Traceback (innermost last):
File “/home/sam/frappe-bench/apps/frappe/frappe/app.py”, line 57, in application
response = frappe.handler.handle()
File “/home/sam/frappe-bench/apps/frappe/frappe/handler.py”, line 19, in handle
execute_cmd(cmd)
File “/home/sam/frappe-bench/apps/frappe/frappe/handler.py”, line 36, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/sam/frappe-bench/apps/frappe/frappe/init.py”, line 806, in call
return fn(*args, **newargs)
File “/home/sam/frappe-bench/apps/frappe/frappe/desk/form/utils.py”, line 44, in validate_link
% (fetch, frappe.db.escape(options), ‘%s’), (value,))[0]]
File “/home/sam/frappe-bench/apps/frappe/frappe/database.py”, line 136, in sql
self._cursor.execute(query, values)
File “/home/sam/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/sam/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, “Unknown column ‘price_list_rate’ in ‘field list’”)

Edit:

I tried by renaming the Price List Rate Field to Item Price
and now I am getting the below error:

Traceback (innermost last):
File “/home/sam/frappe-bench/apps/frappe/frappe/app.py”, line 57, in application
response = frappe.handler.handle()
File “/home/sam/frappe-bench/apps/frappe/frappe/handler.py”, line 19, in handle
execute_cmd(cmd)
File “/home/sam/frappe-bench/apps/frappe/frappe/handler.py”, line 36, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/sam/frappe-bench/apps/frappe/frappe/init.py”, line 806, in call
return fn(*args, **newargs)
File “/home/sam/frappe-bench/apps/frappe/frappe/desk/form/utils.py”, line 44, in validate_link
% (fetch, frappe.db.escape(options), ‘%s’), (value,))[0]]
File “/home/sam/frappe-bench/apps/frappe/frappe/database.py”, line 136, in sql
self._cursor.execute(query, values)
File “/home/sam/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/sam/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, “Unknown column ‘price_list_rate’ in ‘field list’”)

Regard
Ruchin Sharma

[priya_s]

@ruchin78 , it worked for me…so it is hard to guess why this error occurs -
Can you please remove Price List (row 13) field and add new field with different name . Also change name in Price List Rate field Option code.

[ruchin78]

Hi @priya_s
I deleted both the fields and created new as you are using and it worked.

Thanks for your help