Dear All,
I want to fetch item price to a table in the Item doctype like i have indicated below. There are no doctype showing in the option field which supports my request. any ideas? .
(I have added a some other table field as an example)
Hi @ramalyb,
Actually, we have an alternative solution, so please check the post.
Here we set on item master and it will only be fetching the standard buying data. if you want to get all data then please modify the script.
The script is already in the post.
I hope this helps.
Thank You!
Dear @NCP Thank you for the solution and if need to see all the available price options for the item, how can i do that? as this query will filter only Standard Buying Prices as per its script. im not good at coding and need your support.
Thank you,
Ramaly
Hi @ramalyb,
Please apply client script.
frappe.ui.form.on('Item', {
refresh: function(frm) {
frm.add_custom_button(__('Show Item Price'), function() {
get_buying_price(frm);
}, __("View"));
},
});
function get_buying_price(frm){
buying_price = frappe.db.get_list('Item Price', {
fields: ['item_code', 'price_list', 'price_list_rate', 'valid_from', 'valid_upto'],
filters: [
["Item Price", 'item_code', "=", frm.doc.item_code]
],
as_list: 1
}).then(function(val) {
let data = frappe.utils.dict(["item_code", "price_list", "price_list_rate", "valid_from", "valid_upto"], val);
const table_fields = [
{
label: 'Item Code',
fieldname: 'item_code',
fieldtype: 'Link',
options: 'Item',
in_list_view: 1,
read_only: 1
},
{
label: 'Price List',
fieldname: 'price_list',
fieldtype: 'Link',
options: 'Price List',
in_list_view: 1,
read_only: 1
},
{
label: 'Price List Rate',
fieldname: 'price_list_rate',
fieldtype: 'Currency',
in_list_view: 1,
read_only: 1
},
{
label: 'Valid From',
fieldname: 'valid_from',
fieldtype: 'Date',
in_list_view: 1,
read_only: 1
},
{
label: 'Valid Upto',
fieldname: 'valid_upto',
fieldtype: 'Date',
in_list_view: 1,
read_only: 1
}
];
let d = new frappe.ui.Dialog({
title: 'Item Price: ' + frm.doc.item_code,
size: "large",
fields: [
{
label: 'Items',
fieldname: 'items_price',
fieldtype: 'Table',
fields: table_fields,
options: 'Item',
cannot_add_rows: 1,
cannot_delete_rows : 1,
data: data
},
],
primary_action_label: 'Close',
primary_action(values) {
d.hide();
}
});
d.show();
}
)}
Output:
Now, appears the all item price.
After applying the client script, reload the page and then check.
I hope this helps.
Thank You!
2 Likes
Dear @NCP It worked like a charm. Thank you. Is there any way that i feed this data to a table inside item doctype? so as soon as i open a particular item, it displays its prices in a table as i have indicated in my original post.
Thank you,
Ramaly
That for need some changes in client script. you can learn and try for it.
Thank You!
HI @NCP I tried but couldn’t’ get it done, can you support me? Since Item Price table is a parent table its not showing up to attached with a table field in my Item Doctype. is there any way to create a exact copy of Item Price as a child table with consistent update? This will be like a view in SQL.
Thank you,
Ramaly
Hi @ramalyb,
If you want to update/insert all Item Prices in the table then please apply the below client script.
frappe.ui.form.on('Item', {
refresh: function(frm) {
frm.add_custom_button(__('Item Price'), function() {
get_price(frm);
}, __("Insert/Update"));
},
});
function get_price(frm){
buying_price = frappe.db.get_list('Item Price', {
fields: ['item_code', 'price_list', 'price_list_rate', 'valid_from', 'valid_upto'],
filters: [
["Item Price", 'item_code', "=", frm.doc.item_code]
],
as_list: 1
}).then(function(val) {
let data = frappe.utils.dict(["item_code", "price_list", "price_list_rate", "valid_from", "valid_upto"], val);
save_item_prices_to_custom_table(frm, data);
}
)}
function save_item_prices_to_custom_table(frm, prices) {
frm.clear_table('custom_item_price_table');
prices.forEach(function(price) {
let child = frm.add_child('custom_item_price_table');
frappe.model.set_value(child.doctype, child.name, 'item_code', price.item_code);
frappe.model.set_value(child.doctype, child.name, 'price_list', price.price_list);
frappe.model.set_value(child.doctype, child.name, 'price_list_rate', price.price_list_rate);
frappe.model.set_value(child.doctype, child.name, 'valid_from', price.valid_from);
frappe.model.set_value(child.doctype, child.name, 'valid_upto', price.valid_upto);
});
frm.refresh_field('custom_item_price_table');
frm.save();
}
Then reload and check.
Output:
Thank You!
3 Likes
Hi @NCP Thank you for the support. Could you please tell me how to add the table field in the form as there is no supporting option field for item price list.
Thank you,
