Filter Child Table Based on Child Field using Query

Hi,
Is there any way that we can use the query like this for a child table based upon a child field, if yes then any idea how?

Below is the example of using Filtering a child table using a query based upon a parent field.
https://discuss.erpnext.com/t/filter-child-table-base-on-parent-field/9275

Regards
Ruchin Sharma

@Randy_Lowery
My case is different that is why I said using a query.

I have a child table called Alternate Item in Item DocType, which has few items mentioned under different-2 items.

And, I have a Link field (Link to Item Doctype) in a Child Table of Sales Order.

Now, what I want is, I want to filter the records of Sales Order Item child table with reference to the Alternate Item child table.

I was using the above technique but it is throwing me error.
Here is the error message:

Traceback (most recent call last):
File “/home/vishnu/frappe-bench/apps/frappe/frappe/app.py”, line 55, in application
response = frappe.handler.handle()
File “/home/vishnu/frappe-bench/apps/frappe/frappe/handler.py”, line 19, in handle
execute_cmd(cmd)
File “/home/vishnu/frappe-bench/apps/frappe/frappe/handler.py”, line 40, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/vishnu/frappe-bench/apps/frappe/frappe/init.py”, line 901, in call
return fn(*args, **newargs)
File “/home/vishnu/frappe-bench/apps/frappe/frappe/desk/search.py”, line 12, in search_link
search_widget(doctype, txt, query, searchfield=searchfield, page_len=page_len, filters=filters)
File “/home/vishnu/frappe-bench/apps/frappe/frappe/desk/search.py”, line 91, in search_widget
as_list=not as_dict)
File “/home/vishnu/frappe-bench/apps/frappe/frappe/init.py”, line 1080, in get_list
return frappe.model.db_query.DatabaseQuery(doctype).execute(None, *args, **kwargs)
File “/home/vishnu/frappe-bench/apps/frappe/frappe/model/db_query.py”, line 80, in execute
result = self.build_and_run()
File “/home/vishnu/frappe-bench/apps/frappe/frappe/model/db_query.py”, line 104, in build_and_run
return frappe.db.sql(query, as_dict=not self.as_list, debug=self.debug, update=self.update)
File “/home/vishnu/frappe-bench/apps/frappe/frappe/database.py”, line 148, in sql
self._cursor.execute(query)
File “/home/vishnu/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/vishnu/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1052, “Column ‘idx’ in order clause is ambiguous”)

Regards
Ruchin Sharma

I encountered a similar problem. What I did was, instead of using a Link Field, I used an ordinary Select field with no options and used a script to fill the Select Field’s options based on a query I did on the backend (via python).

What I did was something like this:

frappe.form.ui.on("doctype", "field", function(frm){
frappe.call({
    method: the_python_method,
    args:{
         //your args for your python script
    },
    callback: function(data){
if(data.message){
		options = data.message;
		console.log(data.message.length);
		console.log("options.join:"+options.join("\n"));
		options_new = options.join("\n");
		frappe.meta.get_docfield("doctype", "the_select_field", frm.docname).options = options_new;
	}
    }
    });
});

Does this help?

Hi @littlehera
I have one small doubt here, how would I pass the field value of a child field as a parameter in the Python Script?

Field Name is: alternate_item
Child Table Name: Sales Order Item
Child Table Field Name: items

Regards
Ruchin Sharma

@littlehera
I got the answer of my question, just have one more question that on which even I should call the function for child table?
Because I tried using the below code:

frappe.ui.form.on("Sales Order Item", "alternate_item", function(frm, cdt, cdn) {
});

Where alternate_item is my select field with no value.

but it is not being called.

Regards
Ruchin Sharma

@littlehera
I tried to play around with it and I am getting below error on my browser console:

TypeError: frappe.meta.get_docfield(…) is undefined

Regards
Ruchin Sharma

That will be called on alternate_item value change …

Hi,
I am using below code but no option being added to my select field:

frappe.ui.form.on("Sales Order Custom Parts", "alternate_option", function(frm, cdt, cdn) {
var d =locals[cdt][cdn];
var d1 =  {   
              "item_code": d.item_code 
         };   

frappe.call({
    method: "library_management.update_items.alternate_item",
                 args: d1,
                            callback: function(r) {

if(r.message){
		options = r.message;
		console.log(r.message.length);
		console.log("options.join:"+options.join("\n"));
		options_new = options.join("\n");
frappe.meta.get_docfield('Sales Order Custom Parts', 'alternate_option',frm.docname).options = options_new;
	       }
    }

    });
refresh_field("sales_order_custom_parts");
});

Regards
Ruchin Sharma

Hello, @ruchin78!

It think you should change frappe.meta.get_docfield to something like this:
frappe.meta.get_docfield(cdt, "alternate_option",cdn)

since frm.docname returns the name of the PARENT document and not the childtable document.

Cheers!

Try with:

frappe.ui.form.on("Sales Order Custom Parts", "validate", function(frm, cdt, cdn)

You have to put the script in Sales Order custom script

Hi,

Here is my code:
JS Code:

frappe.ui.form.on("Sales Order Custom Parts", "alternate_option_name", function(frm, cdt, cdn) {
var d =locals[cdt][cdn];
var d1 =  {   
              "item_code": d.item_code 
         };   

frappe.call({
    method: "library_management.update_items.alternate_item",
                 args: d1,
                            callback: function(r) {

if(r.message){
		options = r.message;
		console.log(r.message.length);
		console.log("options.join:"+options.join("\n"));
		options_new = options.join("\n");
frappe.meta.get_docfield('Sales Order Custom Parts', 'alternate_option',frm.doc.name).options = options_new;
	       }
    }

    });
//refresh_field("sales_order_custom_parts");
});

Python Code:

@frappe.whitelist(allow_guest=True)

def alternate_item(item_code):

	return frappe.db.sql("""SELECT item_code from `tabItem Alternate` WHERE item_code is not null and item_code!='' and parent=%s""",item_code,as_dict=1)

Problem is the value added in the select field is:
[object Object]
instead of the item codes.

Regards
Ruchin Sharma

Hi,
I am able to manage the code, and will share the same here also.
Just one more query regarding the same, actually I am not able to find that, on which even I should write my code.

I have 10 items in a child table and every item may have different set of items as an option.
I just want that whenever I click to edit a row the select option should automatically be filled with those set of items.

Regards
Ruchin Sharma

@ruchin78, I think you might need to extract the item_code string since frappe.db.sql usually returns tuples instead of strings. Can you try adding the following to your alternate_item python script?

items = frappe.db.sql("""SELECT item_code from `tabItem Alternate` WHERE item_code is not null and item_code!='' and parent=%s""",item_code,as_dict=1)
    for item in items:
        print item

If print item shows you something like (u('item_code'),) try printing out item[0][0] or item[0]. If either one of those shows you the data that you need, you can then append item to an array which you’ll now return to your JS script like this:

#assuming that item[0][0] gives you the item code, you can do the following:
list = []    
items = frappe.db.sql("""SELECT item_code from `tabItem Alternate` WHERE item_code is not null and item_code!='' and parent=%s""",item_code,as_dict=1)
for item in items:
        list.append( item[0][0])
return list

It should work. I think.

@littlehera
As I said, I am able to manage my code and now bit more let me show what my query is:

see my code below:

frappe.ui.form.on("Sales Order Custom Parts", "show_options", function(frm, cdt, cdn) {
var d =locals[cdt][cdn];
var item=frappe.get_doc(cdt,cdn);
var d1 =  {   
              "item_code": d.item_code 
         };   

frappe.call({
    method: "library_management.update_items.alternate_item",
             args: d1,
    callback: function(r) {
if(r.message) {
frappe.meta.get_docfield('Sales Order Custom Parts', 'alternate_option',cur_frm.doc.name).options = r.message;
	       }
    			}
    });
refresh_field("alternate_options");
});

And see the screenshot below:

I c
Alternate Option field hide and will be visible only when I tick Show Options.

Now, the problem is when I check the show option checkbox the values are not get filled into it and to do that I again need to de-select and then select the Show Option checkbox.

Is there any solution for this problem?

Regards
Ruchin Sharma

Oh, I see. Have you tried adding a condition to check if show_options is checked or not?

Something like:

if(frm.doc.show_options == 1) //checked
    //fill the select field
else
    //hide select.
1 Like

@I tried but its not working.

I tried using a filter:

Everything is working fine, the only issue I am facing is that, I need to click twice on the link field to get the values (refreshed).

frappe.ui.form.on("Sales Order", "onload", function(frm){

cur_frm.set_query("alternate_option", "sales_order_custom_parts",  function (doc, cdt, cdn) 
{
var d= locals[cdt][cdn];
var d1 =  {   
              "item_code": d.item_code 
         };   
frappe.call({
    		method: "library_management.update_items.alternate_item",
             	args: d1,
    		callback: function(r)
				{ 
				a = JSON.parse(JSON.stringify(r.message));
				}
    	});

return { 
		"filters":	[
				["Item","item_code", "in", a]
				] 
			}

refresh_field("alternate_option");
	});
});

Any idea?

Regards
Ruchin Sharma

Hi @ruchin78,

Were you able to find a solution for this? Even I have a filter like this, and it works only when I click it the second time.

Thanks
Uma

can you share the python code please ?