Using child table data for set_query filters

ERPNext Customize ERPNext
1

Hi all.

I’m attempting to filter a child table using set_query. The difficulty I’m having is that I want the filter to be the data I’m pulling from another child table.

This pulls the Customer ID number {00001,00002} from the Customer DocType. The result is what I want to use as a filter:

let name = cur_frm.doc.customer
  
frappe.model.with_doc('Customer', name, function () {
    
    let source_doc = frappe.model.get_doc('Customer', name);
    //console.log(source_doc.responsible_persons_list)
    
    $.each(source_doc.responsible_persons_list, function (index, source_row) {
        console.log(source_row.customer_id)
    });
});

And this is the set_query filter on a different doctype:

// Filter lease_ack child table
frappe.ui.form.on("Lease Agreement", "onload", function(frm) {
    frm.set_query("responsible_person","lease_ack", function() {
        
        for ( let i in frm.doc.lease_ack) {
            
            return {
            filters: [
					['Customer', 'name', 'in', '00001, 00002'],
					]
                    };
        }
    });
});

I have no idea how insert the results from the first bit of code into the filter variables. Any help would be greatly appreciated, and please limit suggestions to client scripts if possible.

Cheers!

2

Just giving this a bump. Any help would be much appreciated

3

Giving this another bump. Really keen to get this sorted, any tips would be appreciated!

4

So I’ve managed to create a function that gets the customer account numbers, parses them in the correct format for set_query, however it still doesn’t like it.

The function below returns: '00005,00002'

// Function to return responsible persons based on frm.doc.customer
function getResp(cust) {

	var arr = [];
	
	frappe.model.with_doc('Customer', cust, function () {
		
		let source_doc = frappe.model.get_doc('Customer', cust);
		
		$.each(source_doc.responsible_persons_list, function (index, source_row) {
			arr.push(source_row.customer_id);
			let newarr = "'" + arr.join() + "'"
			
		});
	});
}

The string from above is passed to the filters array:

// Filter lease_ack child table
frappe.ui.form.on("Lease Agreement", "onload", function (frm) {
	frm.set_query("responsible_person", "lease_ack", function () {

		let resp = getResp(frm.doc.customer);

		for (let i in frm.doc.lease_ack) {
			return {
				filters: [
					['Customer', 'name', 'in', resp],
				]
			};
		}
	});
});

The filtered result is empty. Is there a way to pass this variable into the filter?

image
image762×154 12.5 KB

5

I was able to get set_query to use my array… I was sure I’d tried this and it hadn’t worked! Here it is for posterity:

frappe.ui.form.on("Lease Agreement", "refresh", function (frm) {

	let la = frm.doc.name

	var arr = [];

	let child_table = frappe.model.get_doc('Lease Agreement', la);

	$.each(child_table.product_location, function (index, source_row) {
		
		arr.push(source_row.location);
		let arr2 = arr.join()
		
		frm.set_query("product_location", "security_seal_register", function () {

			return({
				filters: [
					['Location Inventory', 'serial_no', 'in', arr2],
				]
			});
	
		});

	});

});

Note: this is for a different child table… variables and fields differ from the original post.

2 Likes
6

is this worked