How to add MultiSelectList in the report

ERPNext Customize ERPNext
, , ,
1

I want to add MultiSelectList field on the report Stock Balance.

I have added the field in stock_balance.js as below

{
	"fieldname": "item_code",
	"label": __("Item"),
	"fieldtype": "MultiSelectList",
	"width": "80",
	"get_data": function(txt) {
		return frappe.db.get_link_options("Item", txt);
	 }
}

And I got the multi select field on the stock balance report as below

image
image387×518 26.8 KB

but when I select multiple items as shown in the picture I don’t get any results as below.

image
image1097×177 6.44 KB

and logs from prepared report

Traceback (most recent call last):
File "apps/frappe/frappe/core/doctype/prepared_report/prepared_report.py", line 43, in run_background
result = generate_report_result(report=report, filters=instance.filters, user=instance.owner)
File "apps/frappe/frappe/__init__.py", line 767, in wrapper_fn
retval = fn(*args, **get_newargs(fn, kwargs))
File "apps/frappe/frappe/desk/query_report.py", line 90, in generate_report_result
res = get_report_result(report, filters) or []
File "apps/frappe/frappe/desk/query_report.py", line 71, in get_report_result
res = report.execute_script_report(filters)
File "apps/frappe/frappe/core/doctype/report/report.py", line 146, in execute_script_report
res = self.execute_module(filters)
File "apps/frappe/frappe/core/doctype/report/report.py", line 163, in execute_module
return frappe.get_attr(method_name)(frappe._dict(filters))
File "apps/erpnext/erpnext/stock/report/stock_balance/stock_balance.py", line 33, in execute
items = get_items(filters)
File "apps/erpnext/erpnext/stock/report/stock_balance/stock_balance.py", line 367, in get_items
items = frappe.db.sql_list(
File "apps/frappe/frappe/database/database.py", line 281, in sql_list
return [r[0] for r in self.sql(query, values, debug=debug)]
File "apps/frappe/frappe/database/database.py", line 174, in sql
self._cursor.execute(query, values)
File "env/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
result = self._query(query)
File "env/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
conn.query(q)
File "env/lib/python3.8/site-packages/pymysql/connections.py", line 548, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "env/lib/python3.8/site-packages/pymysql/connections.py", line 775, in _read_query_result
result.read()
File "env/lib/python3.8/site-packages/pymysql/connections.py", line 1156, in read
first_packet = self.connection._read_packet()
File "env/lib/python3.8/site-packages/pymysql/connections.py", line 725, in _read_packet
packet.raise_for_error()
File "env/lib/python3.8/site-packages/pymysql/protocol.py", line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File "env/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (4078, "Illegal parameter data types varchar and row for operation '='")

filters from prepared report

image
image1440×490 17.8 KB

what else I am missing?

2

@abubakar The value of the multiselect field isn’t handled well in the python script…

It is handled as a string instead of a list…

So, check the python script of the report…

3

I have suspected three python files,

prepared_report.py, stock_balance.py, query_report.py.

but I don’t know where to change that’s why I am here

4

@abubakar Here is the file, method and line number where the sql is executed…

File: "apps/frappe/frappe/core/doctype/prepared_report/prepared_report.py"
Line: "40"
Method: "generate_report_result"

Before the method, add something like the following…

if "item_code" in instance.filters:
    instance.filters["item_code"] = ["in", instance.filters["item_code"]]
5

@kid1194
I have done this and getting another error

if "item_code" in instance.filters:
			instance.filters["item_code"] = ["in", instance.filters["item_code"]]

		result = generate_report_result(report=report, filters=instance.filters, user=instance.owner)

the error

Traceback (most recent call last):
File "apps/frappe/frappe/core/doctype/prepared_report/prepared_report.py", line 44, in run_background
instance.filters["item_code"] = ["in", instance.filters["item_code"]]
TypeError: string indices must be integers
6

Its working.

I have to change in stock_balance.py file

from this

def get_items(filters):
	"Get items based on item code, item group or brand."
	conditions = []
	if filters.get("item_code"):
		conditions.append("item.name=%(item_code)s")

to this

def get_items(filters):
	"Get items based on item code, item group or brand."
	conditions = []
	if filters.get("item_code"):
		conditions.append("item.name in %(item_code)s")
2 Likes
7

@abubakar I’m glad that you got it to work…