How to user SQL Query in Custom Script

Frappe Framework Custom Script
1

Hi,
Below is my script which was perfectly working in Version 4. Now it is not working in Version 5.
I have item groups as shown in the picture and want to generate next item code in the selected group.

Pasted image651×575 50.8 KB

Pasted image695×353 20 KB

====================================================
cur_frm.cscript.next_id = function(doc)
{
doc.item_code = “”;
var pattern = “”;
pattern = doc.item_group.substring(0,4)+‘%’;

var query = "SELECT `tabItem`.name FROM `tabItem` WHERE `tabItem`.name LIKE '" + pattern + "' ORDER BY name desc limit 1";

msgprint(query);
wn.call({
method: “webnotes.widgets.query_builder.runquery”,
args: {
“query”: query
},
callback: function(r)
{
// returned last value
var last_id = “”;
if (typeof r != ‘undefined’ && typeof r.values != ‘undefined’ && typeof r.values[0] != ‘undefined’)
{
last_id = r.values[0][0];
}
else
{
last_id = pattern.substring(0,pattern.length-1) + “001000”;
}

    var last_number = parseInt(last_id.substring(4))+1;
    var new_prefix = last_id.substring(0,4);
    var new_suffix = last_number.toString();
    var new_id = new_prefix;
    for (var i=0;i<(6-new_suffix.length);i++)
        { 
        new_id += '0';
        }
    new_id += new_suffix;
    msgprint(new_id);
    cur_frm.set_value("item_code", new_id);
    }
});
}

cur_frm.cscript.item_group = function(doc)
{
cur_frm.toggle_display(“next_id”, (doc.__islocal && doc.item_group != ‘’)? 1:0);
}

2

Hi @ruchin78

We have moved away from wn namespace since version 4. It should be frappe.call

This code is from version 3. You will need to rewrite the query part too. I think this use case should be handled server side using a Frappe App. @aditya has done something similar in his setup.

Thanks,
Anand.

3

@anand its @adityaduggal (look for the photo)

Btw I did catch it…yeah @ruchin78 I have something similar in my app as well…but I guess we should be fine if @rmehta could help us with awesome variants

Any ways ruchin you can have a look at my server side code here:

Also the client side script is here:

https://gist.github.com/adityaduggal/6535027

Let me know if you are not getting something in my code, though almost all of this code was written with the help of the team and google.

1 Like