Filter Links Data in Child Table

Frappe Framework Customization
1

Hi,

I want to filter data on child table,
I’ve Child Table named ‘BS Tracking Reviews’
BS Tracking Reviews have field ‘balance_sheet’ which is linked to Doctype named ‘Heads’
in Heads i’ve field named ‘client’ and i also have ‘client’ field in Parent doctype named “Tracking Reviews”.
I want to filter Heads Data in Tracking Forms, according to clients.

Find below images for reference.

1
1522×510 39.2 KB

Screenshot%20from%202018-10-15%2019-12-21
Screenshot%20from%202018-10-15%2019-12-211111×269 5.39 KB

I’ve tried below script but it is not working.

frappe.ui.form.on("BS Tracking Reviews", {
	balance_sheet: function(frm,cdt, cdn){
		data_filteration(frm, cdt, cdn);
	}
});
var data_filteration = function(frm, cdt, cdn) {
    var child = locals[cdt][cdn];
	//frappe.msgprint("Working");
		frm.set_query("child.balance_sheet", function() {
			return {
				filters: [
					["Heads","client", "in", [frm.doc.client]]
				]
			}
		});
}
2

try this
--------------------on .js-------------------------------------

        frappe.ui.form.on(“Doctype Name”, “onload”, function(frm) {
        frm.fields_dict.childtablename.grid.get_field(‘child_field’).get_query =
        function() {
        return {

        	query: "appname.appname.doctype.doctypename.doctypename.methodname",
        filters: {
        	"client":cur_frm.doc.client

        }
        }
        }

        });

    --------------------------on .py--------------------------------------------------

  @frappe.whitelist()
       def methodname(doctype, txt, searchfield, start, page_len, filters):
       	
       	return frappe.db.sql(""" your query """.format(code= filters.get("client")))
10 Likes
3

Thanks @PrashantPatil i’ll try and feed you back.

4

work like a charm

5

Hello @pratikcws @shahid @PrashantPatil,

I tried your solution for filter links data in child table and it pretty much worked for me. The bad thing which is happening is you have to manually select the option from the Link field.

A normal link field has all the data and works like a charm with suggestions when typing the name of data. It sorts too using like operator and so on.

I want this to happen in your solution too with suggestions when a user manually types the data.

Can anyone help me out?
Thanks in advance!

6

It would be of great help if anyone can drop any hints/path to achieve this. :grinning:

7

@ROHAN_JAIN1
Hi rohan,
You can do this by adding like condition in your query.
E.g.
frappe.db.sql("your query ......and month like '{1}' """.format('%%%s%%' % txt))

2 Likes
8

very simple

first do this in your js
frappe.call({
method:‘property_sales.property_sales.doctype.architect_certification_achieved.architect_certification_achieved.filter_serialno’,
freeze: true,
args: {
“project_name”: cur_frm.doc.project,
“building_name”: cur_frm.doc.building_name
},
callback: function(serialno){
console.log(serialno)
var includeinarray = []

	cur_frm.set_query("serial_no", function() {
        return {
            filters: [
			 ['Serial No', 'serial_no', 'in', serialno.message],
			 
		 ]
        };
    });
	
	}
	
})

and add this code in py

@frappe.whitelist()
def filter_serialno(project_name,building_name):
	sn_list = frappe.db.sql("""select pu.final_serial_number as name from `tabPlot Unit Price` as pu LEFT join `tabGenerate Sales Unit` as gs on gs.name=pu.parent where gs.project_name=%s and gs.sel_building_name=%s""",(project_name,building_name),as_dict=1)
	return [x.name for x in sn_list]
1 Like