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.frappe.io/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 cNow, 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.
@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 ?