Fetch values from table for select list

In frappe v13 it exists. Maybe it doesn’t exist in the field type select field but it’s there…

Can I know the name of the field that I have to change?

@Bhargav_N The field you have to change its fieldtype is setting_name.

Sorry, not name. Field type?

@Bhargav_N The fieldtype should be changed to Autocomplete

In frappe v14, this fieldtype exists, but not v13…
So if you are using v13 then I should find another solution for you…

@Bhargav_N How about we give Select fieldtype another chance…
Because I found out that the 20 limit is set by get_list method so below I set the limit to 100…

frappe.ui.form.on('Tool Setup and Program Control Plan', {
    onload: function(frm) {
        // Cache for setting_name options
        frm._setting_names = {};
    },
    refresh: function(frm) {
        frm.trigger('load_process_names');
    },
    component: function(frm) {
        frm.trigger('load_process_names');
    },
    load_process_names: function(frm) {
        var select = frm.get_field('setting_name'),
        val = frm.doc.setting_name || null,
        // The select list options with the default first option
        list = [{label: __('Select Process...'), value: ''}];
        // If the component field is empty, then empty the select list
        if (!frm.doc.component) {
            // Adding the old value of setting_name (if there is any) to the select list
            if (val) list.push({label: val, value: val});
            // Adding the list of options to the select field
            select.df.options = list;
            // Selecting the old value of setting_name if there is any
            select.set_options(val);
            return;
        }
        // A function to add the list of options to the select field 
        var setOptions = function(optList) {
            // Adding the list of process names to the select field
            select.df.options = list;
            // Selecting the old value of setting_name if there is any
            select.set_options(val);
        };
        // If cache exist, the get options from cache
        if (frm._setting_names[frm.doc.component]) {
            setOptions(frm._setting_names[frm.doc.component]);
            return;
        }
        // Getting the process name values
        frappe.db.get_list('Item Process Table', {
            fields: ['pn'],
            filters: {
                parent: frm.doc.component,
                parenttype: 'Item Process List',
                parentfield: 'ipt'
            },
            // The number of rows to get
            limit: 100,
        }).then(function(data) {
            // Sorting the process name from A-Z
            data = frappe.utils.sort(data, 'pn');
            // Making sure that the list of process name doesn't have duplicate values
            let check = [];
            data.forEach(function(v) {
                if (check.indexOf(v.pn) < 0) {
                    check.push(v.pn);
                    list.push({label: v.pn, value: v.pn});
                }
            });
            // Storing list in Cache
            frm._setting_names[frm.doc.component] = list;
            setOptions(list);
        });
    }
});

Thanks a lot I will check and share you the result.

Same, it is listing only 20

Thanks a lot its working.

@Bhargav_N So it’s working without the 20 options limit?

If so, that’s great…

And are you ok with the 100 options limit?

Yes! Thanks a lot

1 Like

‘frappe.db.get_list’ is not allowing to fetch details from child table of the doctype except for Administrator. Is there any method via script that permission is passed to certain user roles?

(its working fine of admin but not working any users after upgrading to version 14 )
Thanks in advance.

@Bhargav_N Can you add a server script (Python) or not?

If you can, then you can get the child table data from a custom whitelist method using frappe.call


import frappe

@frappe.whitelist()
def get_item_process_list(component, limit = 100):
    if not component or not isinstance(component, str):
        return []
    
    limit = limit if limit and isinstance(limit, int) else 100
    
    try:
        data = frappe.get_all(
            "Item Process Table",
            fields=["pn"],
            filters={
                "parent": component,
                "parenttype": "Item Process List",
                "parentfield": "ipt",
            },
            limit_page_length=limit,
        )
        
        return data if isinstance(data, list) else []
    except Exception:
        return []

Thanks a lot for support.
I saved this script in ‘Server Script’ and script type as: ‘Permission query’ and I’m Getting below error.

App Versions

{
	"erpnext": "14.10.1",
	"frappe": "14.19.1",
	"hrms": "1.0.0",
	"india_compliance": "14.0.5",
	"payments": "0.0.1"
}

Route

List/Inspection Report/List

Trackeback

Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 69, in application
    response = frappe.api.handle()
  File "apps/frappe/frappe/api.py", line 54, in handle
    return frappe.handler.handle()
  File "apps/frappe/frappe/handler.py", line 45, in handle
    data = execute_cmd(cmd)
  File "apps/frappe/frappe/handler.py", line 83, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "apps/frappe/frappe/__init__.py", line 1590, in call
    return fn(*args, **newargs)
  File "apps/frappe/frappe/__init__.py", line 784, in wrapper_fn
    retval = fn(*args, **get_newargs(fn, kwargs))
  File "apps/frappe/frappe/desk/reportview.py", line 29, in get
    data = compress(execute(**args), args=args)
  File "apps/frappe/frappe/desk/reportview.py", line 65, in execute
    return DatabaseQuery(doctype).execute(*args, **kwargs)
  File "apps/frappe/frappe/model/db_query.py", line 172, in execute
    result = self.build_and_run()
  File "apps/frappe/frappe/model/db_query.py", line 187, in build_and_run
    args = self.prepare_args()
  File "apps/frappe/frappe/model/db_query.py", line 226, in prepare_args
    self.build_conditions()
  File "apps/frappe/frappe/model/db_query.py", line 535, in build_conditions
    match_conditions = self.build_match_conditions()
  File "apps/frappe/frappe/model/db_query.py", line 783, in build_match_conditions
    doctype_conditions = self.get_permission_query_conditions()
  File "apps/frappe/frappe/model/db_query.py", line 873, in get_permission_query_conditions
    condition = script.get_permission_query_conditions(self.user)
  File "apps/frappe/frappe/core/doctype/server_script/server_script.py", line 121, in get_permission_query_conditions
    safe_exec(self.script, None, locals)
  File "apps/frappe/frappe/utils/safe_exec.py", line 72, in safe_exec
    exec(compile_restricted(script), exec_globals, _locals)  # pylint: disable=exec-used
  File "<unknown>", line 1, in <module>
ImportError: __import__ not found

Request Data

{
	"type": "POST",
	"args": {
		"doctype": "Inspection Report",
		"fields": "[\"`tabInspection Report`.`name`\",\"`tabInspection Report`.`owner`\",\"`tabInspection Report`.`creation`\",\"`tabInspection Report`.`modified`\",\"`tabInspection Report`.`modified_by`\",\"`tabInspection Report`.`_user_tags`\",\"`tabInspection Report`.`_comments`\",\"`tabInspection Report`.`_assign`\",\"`tabInspection Report`.`_liked_by`\",\"`tabInspection Report`.`docstatus`\",\"`tabInspection Report`.`idx`\",\"`tabInspection Report`.`item_code`\",\"`tabInspection Report`.`process_name`\",\"`tabInspection Report`.`cb`\"]",
		"filters": "[]",
		"order_by": "`tabInspection Report`.`modified` desc",
		"start": 0,
		"page_length": 20,
		"view": "List",
		"group_by": "`tabInspection Report`.`name`",
		"with_comment_count": true
	},
	"freeze": false,
	"freeze_message": "Loading...",
	"headers": {},
	"error_handlers": {},
	"url": "/api/method/frappe.desk.reportview.get"
}

Response Data

{
	"exception": "ImportError: __import__ not found"
}

@Bhargav_N Sorry, my mistake…

  • Set the server script type as API
  • Set the method name as get_item_process_list
  • Use the following codes…

Server Script

if not doc.component or not isinstance(doc.component, str):
    frappe.response["message"] = []

else:

    try:
        data = frappe.get_all(
            "Item Process Table",
            fields=["pn"],
            filters={
                "parent": doc.component,
                "parenttype": "Item Process List",
                "parentfield": "ipt",
            },
            limit_page_length=100,
        )
        
        frappe.response["message"] = data if isinstance(data, list) else []
    except Exception:
        frappe.response["message"] = []

JavaScript

frappe.ui.form.on('Tool Setup and Program Control Plan', {
    onload: function(frm) {
        // Cache for setting_name options
        frm._setting_names = {};
    },
    refresh: function(frm) {
        frm.trigger('load_process_names');
    },
    component: function(frm) {
        frm.trigger('load_process_names');
    },
    load_process_names: function(frm) {
        var select = frm.get_field('setting_name'),
        val = frm.doc.setting_name || null,
        // The select list options with the default first option
        list = [{label: __('Select Process...'), value: ''}];
        // If the component field is empty, then empty the select list
        if (!frm.doc.component) {
            // Adding the old value of setting_name (if there is any) to the select list
            if (val) list.push({label: val, value: val});
            // Adding the list of options to the select field
            select.df.options = list;
            // Selecting the old value of setting_name if there is any
            select.set_options(val);
            return;
        }
        // A function to add the list of options to the select field 
        var setOptions = function(optList) {
            // Adding the list of process names to the select field
            select.df.options = list;
            // Selecting the old value of setting_name if there is any
            select.set_options(val);
        };
        // If cache exist, the get options from cache
        if (frm._setting_names[frm.doc.component]) {
            setOptions(frm._setting_names[frm.doc.component]);
            return;
        }
        // Getting the process name values
        frappe.call('get_item_process_list')
        .then(function(data) {
            if (!data || !data.message || !$.isArray(data.message)) {
                frappe.throw(__('Unable to get the process list'));
                return;
            }
            data = data.message;
            // Sorting the process name from A-Z
            data = frappe.utils.sort(data, 'pn');
            // Making sure that the list of process name doesn't have duplicate values
            let check = [];
            data.forEach(function(v) {
                if (check.indexOf(v.pn) < 0) {
                    check.push(v.pn);
                    list.push({label: v.pn, value: v.pn});
                }
            });
            // Storing list in Cache
            frm._setting_names[frm.doc.component] = list;
            setOptions(list);
        });
    }
});

Server Script Name: get_item_process_list
Script Type: API
API Method: get_item_process_list

is it right??

@Bhargav_N Yes, that’s right…

Did it work?

Sorry for the late reply.
I’m getting this error.

App Versions

{
	"erpnext": "14.10.1",
	"frappe": "14.19.1",
	"hrms": "1.0.0",
	"india_compliance": "14.0.5",
	"payments": "0.0.1"
}

Route

Form/Tool Setup and Program Control Plan/new-tool-setup-and-program-control-plan-2

Trackeback

Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 69, in application
    response = frappe.api.handle()
  File "apps/frappe/frappe/api.py", line 54, in handle
    return frappe.handler.handle()
  File "apps/frappe/frappe/handler.py", line 45, in handle
    data = execute_cmd(cmd)
  File "apps/frappe/frappe/handler.py", line 69, in execute_cmd
    return run_server_script(server_script)
  File "apps/frappe/frappe/handler.py", line 87, in run_server_script
    response = frappe.get_doc("Server Script", server_script).execute_method()
  File "apps/frappe/frappe/core/doctype/server_script/server_script.py", line 89, in execute_method
    _globals, _locals = safe_exec(self.script)
  File "apps/frappe/frappe/utils/safe_exec.py", line 72, in safe_exec
    exec(compile_restricted(script), exec_globals, _locals)  # pylint: disable=exec-used
  File "<unknown>", line 1, in <module>
NameError: name 'doc' is not defined

Request Data

{
	"type": "POST",
	"args": {},
	"headers": {},
	"error_handlers": {},
	"url": "/api/method/get_item_process_list"
}

Response Data

{
	"exception": "NameError: name 'doc' is not defined"
}

@Bhargav_N Sorry, my mistake…

I thought that the doc variable will be available for this type of server script…

Because of that, we have to send the component value with the request…

I have modified both codes a bit…

Give it a try and see if it’s working or not…

Server Script

component = frappe.form_dict.component
if not component or not isinstance(component, str):
    frappe.response["message"] = []

else:

    try:
        data = frappe.get_all(
            "Item Process Table",
            fields=["pn"],
            filters={
                "parent": component,
                "parenttype": "Item Process List",
                "parentfield": "ipt",
            },
            limit_page_length=100,
        )
        
        frappe.response["message"] = data if isinstance(data, list) else []
    except Exception:
        frappe.response["message"] = []

JavaScript

frappe.ui.form.on('Tool Setup and Program Control Plan', {
    onload: function(frm) {
        // Cache for setting_name options
        frm._setting_names = {};
    },
    refresh: function(frm) {
        frm.trigger('load_process_names');
    },
    component: function(frm) {
        frm.trigger('load_process_names');
    },
    load_process_names: function(frm) {
        var select = frm.get_field('setting_name'),
        val = frm.doc.setting_name || null,
        // The select list options with the default first option
        list = [{label: __('Select Process...'), value: ''}];
        // If the component field is empty, then empty the select list
        if (!frm.doc.component) {
            // Adding the old value of setting_name (if there is any) to the select list
            if (val) list.push({label: val, value: val});
            // Adding the list of options to the select field
            select.df.options = list;
            // Selecting the old value of setting_name if there is any
            select.set_options(val);
            return;
        }
        // A function to add the list of options to the select field 
        var setOptions = function(optList) {
            // Adding the list of process names to the select field
            select.df.options = list;
            // Selecting the old value of setting_name if there is any
            select.set_options(val);
        };
        // If cache exist, the get options from cache
        if (frm._setting_names[frm.doc.component]) {
            setOptions(frm._setting_names[frm.doc.component]);
            return;
        }
        // Getting the process name values
        frappe.call('get_item_process_list', {component: frm.doc.component})
        .then(function(data) {
            if (!data || !data.message || !$.isArray(data.message)) {
                frappe.throw(__('Unable to get the process list'));
                return;
            }
            data = data.message;
            // Sorting the process name from A-Z
            data = frappe.utils.sort(data, 'pn');
            // Making sure that the list of process name doesn't have duplicate values
            let check = [];
            data.forEach(function(v) {
                if (check.indexOf(v.pn) < 0) {
                    check.push(v.pn);
                    list.push({label: v.pn, value: v.pn});
                }
            });
            // Storing list in Cache
            frm._setting_names[frm.doc.component] = list;
            setOptions(list);
        });
    }
});