How to user SQL Query in Custom Script

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.

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";

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];
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;
    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);

Hi @ruchin78

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

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.


@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:

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