Fetch fields from two doctypes

Dear community,
In price list doctype i had two fields,

  1. Select Property (Table) as custom_select_property
    under the table the field name as Select Property (Link) as select_property options Customer Group
  2. Service Charges as custom_service_charges

in sales invoice
i had two fields

  1. Select Property (Link) as select_property options Customer Group
    2.Service Charge fee as custom_service_charge_fee

Need to fetch Price list service charge value in custom_service_charge_fee
for eg:

in price list

the select property contain ABC,BVD,SCV

below table i had Service Charges field i add data as 15

so now ABC,BVD,SCV has service charge as 15.

now in sales invoice if i select property as SCV the field custom_service_charge_fee
should auto fetch the service charges

i tried as fetch from as price_list.custom_service_charges it not works

even tried with

frappe.ui.form.on('Sales Invoice', {
    select_property: function(frm) {
        if (frm.doc.select_property) {
            frappe.db.get_value('Price List', {'select_property': frm.doc.select_property}, 'custom_service_charges', function(value) {
                if (value) {
                    frm.set_value('custom_service_charge_fee', value.custom_service_charges);
                }
            });
        }
    }
});

what am missing pls help to solve this.

Thanks in advance

To achieve the desired functionality in Frappe, you need to ensure that you’re correctly fetching the custom_service_charges value based on the selected property in the Sales Invoice. Here’s a step-by-step explanation of how to do this:

Step 1: Define Your Fields

Make sure your fields are defined correctly in both the Price List and Sales Invoice doctype. You should have:

  • Price List
    • custom_select_property (Table)
    • custom_service_charges (Decimal)
  • Sales Invoice
    • select_property (Link to Customer Group)
    • custom_service_charge_fee (Decimal)

Step 2: Use Correct Fetch Logic

In your JavaScript code, you should ensure that you’re querying the Price List based on the selected property correctly. Here’s an improved version of your code:

javascript

Copy

frappe.ui.form.on('Sales Invoice', {
    select_property: function(frm) {
        if (frm.doc.select_property) {
            frappe.db.get_value('Price List', 
                { 'custom_select_property': frm.doc.select_property }, 
                'custom_service_charges', 
                function(value) {
                    if (value && value.custom_service_charges) {
                        frm.set_value('custom_service_charge_fee', value.custom_service_charges);
                    } else {
                        frm.set_value('custom_service_charge_fee', 0); // or handle no value case
                    }
                }
            );
        } else {
            frm.set_value('custom_service_charge_fee', 0); // Reset if no property selected
        }
    }
});

Key Changes Made

  1. Correct Field Reference: Ensure that you are querying with the correct field name. Here, I assumed custom_select_property is the field in the Price List that corresponds to the selected property in the Sales Invoice.
  2. Check for Existing Value: Added a check to ensure value.custom_service_charges exists before setting it.
  3. Resetting Value: If no property is selected, reset the custom_service_charge_fee to 0.

Step 3: Debugging

If this still doesn’t work, consider the following debugging steps:

  • Console Logging: Add console.log statements to check if the select_property is being read correctly.
  • Check Doctype Links: Ensure that the link between the Sales Invoice and Price List is set correctly, and that the select_property values match.

Step 4: Update Permissions

Ensure that your user has the necessary permissions to read from the Price List doctype.

With these adjustments, your code should successfully fetch the service charge based on the selected property in the Sales Invoice. If you encounter any specific errors, feel free to share them for further assistance.