How to fetch Standard Buying price from Price List

ERPNext
1

I want to add a field on the Item DocType form to show the Standard Buying price from the Price List.

Can anyone suggest why standard fetching does not show the price? I think this probably needs to be done with a Client Script to lookup the price_list_rate from the Item Price table and write this to the custom field added to the Item DocType.

Even more confusing is that the Price List can contain more than 1 price for an item, so doesn’t this data need to be attached to the Item form as a child table field?

I would love an explanation of how the Price List system works as it appears that the DocTypes have a more complex relationship than usual.

Thank you very much.

2

Hi @pstary,

As per your scenario, we configure and set the script. So please check it.

Item Buying Price (Standard Buying):

image
image1830×563 76.7 KB

Here, I added a custom button in Item Doctype. When you click on the Show Item Price button then will show the dialog box which you want.

image
image1824×518 73.8 KB

Output:

image
image1846×603 66.4 KB

Please go to the Client script doctype and Select the Item Doctype.

image
image1828×592 75.8 KB

Code:

frappe.ui.form.on('Item', {
    refresh: function(frm) {
        frm.add_custom_button(__('Show Item Buying 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", "buying", "=", 1], 
            ["Item Price", 'item_code', "=", frm.doc.item_code],
            ["Item Price", 'price_list', "=", "Standard Buying"]
        ],
        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 Buying Price: ' + frm.doc.item_code,
            size: "large",
            fields: [
                {
                    label: 'Items',
                    fieldname: 'items_buying_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();  
    }
)}

I hope this helps.

Thank You!

2 Likes
3

Your solution is awesome! Thank you so much and it works perfectly.

I have learned a lot from following your code and trying to modify it to take this solution just one step further but cannot find the answer.

I would like to click on one of the Price List rows in the dialog and insert that row’s price_list_rate data into a custom field named, “Current Cost” on my Item form.

Thank you.

4

Hi @pstary,

That for check it and apply it.

Code:

frappe.ui.form.on('Item', {
    refresh: function(frm) {
        frm.add_custom_button(__('Show Item Buying Price'), function() {
            get_buying_price(frm);
        }, __("View"));
    },
});

function get_buying_price(frm) {
    frappe.db.get_list('Item Price', {
        fields: ['item_code', 'price_list', 'price_list_rate', 'valid_from', 'valid_upto'],
        filters: [
            ["Item Price", "buying", "=", 1], 
            ["Item Price", 'item_code', "=", frm.doc.item_code],
            ["Item Price", 'price_list', "=", "Standard Buying"]
        ],
        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 Buying Price: ' + frm.doc.item_code,
            size: "large",
            fields: [
                {
                    label: 'Items',
                    fieldname: 'items_buying_price',
                    fieldtype: 'Table',
                    fields: table_fields,
                    options: 'Item',
                    cannot_add_rows: 1,
                    cannot_delete_rows: 1,
                    data: data
                },
            ],
            primary_action_label: 'Submit',
            primary_action(values) {
                var val_buy = values.items_buying_price;
                var checkedCount = 0;
                var checkedRowIndex = -1;
                for (var i = 0; i < val_buy.length; i++) {
                    if (val_buy[i].__checked === 1) {
                        checkedCount++;
                        checkedRowIndex = i;
                    }
                }
                if (checkedCount > 1) {
                    frappe.throw(__("At a time, you can select only one row."));
                    frappe.validated = false;
                } else if (checkedCount === 1) {
                    frm.set_value('current_cost', val_buy[checkedRowIndex].price_list_rate);
                    frm.save();
                } 
                d.hide();
            }
        });
        d.show();  
    });
}

I hope this helps.

Thank You!

5

Thank you again! Everything works exactly as I requested.

The code is more complex than I expected but you trapped out multi-selection with an error dialog which is more elegant and I learned even more from your most appreciated response.

Thank you again.