Query Filter is not working with Supplier

Frappe Framework App Development
1

Hi All,

I have a query with the filter it is working fine if I use it for dates but when I put supplier in the filter it is giving me an error.

May anyone throw some light on it?
Query

SELECT 
supplier_name AS "Supplier::250",
SUM(base_total) AS "Amount (Without Tax):Float:250",
SUM(total_taxes_and_charges) AS "Tax and Charges:Float:250",
SUM(base_grand_total) AS "Grand Total:Float:250"
FROM `tabPurchase Receipt`
WHERE status='To Bill'
AND posting_date between %(from_date)s and %(to_date)s
AND supplier_name = %(supplier)s
GROUP BY supplier_name;

Filter

frappe.query_reports['Supplier Wise Purchase Receipt'] = {
    "filters": [
        {
            "fieldname":"from_date",
            "label": __("From Date"),
            "fieldtype": "Date",
	    "default": get_today(),
            "reqd": 1
        },
       {
            "fieldname":"to_date",
            "label": __("To Date"),
            "fieldtype": "Date",
	    "default": get_today(),
            "reqd": 1
        },
       {
            "fieldname":"supplier",
            "label": __("Supplier"),
            "fieldtype": "Link",
	    "options": "Supplier"
        }
    ]
}

Error:
Traceback (most recent call last):
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/app.py”, line 55, in application
response = frappe.handler.handle()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/handler.py”, line 19, in handle
execute_cmd(cmd)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/handler.py”, line 36, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/init.py”, line 879, in call
return fn(*args, **newargs)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/desk/query_report.py”, line 82, in run
result = [list(t) for t in frappe.db.sql(report.query, filters)]
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/database.py”, line 137, in sql
self._cursor.execute(query, values)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 185, in execute
for key, item in args.iteritems())
KeyError: ‘supplier’

Regards
Ruchin Sharma

2

“supplier:Link/Supplier:250”, you can try this in your SQL

3

@Pawan
I tried it but still the error is same.

SELECT 
supplier_name AS "supplier:Link/Supplier:250",
SUM(base_total) AS "Amount (Without Tax):Float:250",
SUM(total_taxes_and_charges) AS "Tax and Charges:Float:250",
SUM(base_grand_total) AS "Grand Total:Float:250"
FROM `tabPurchase Receipt`
WHERE status='To Bill'
AND posting_date between %(from_date)s and %(to_date)s
AND supplier_name = %(supplier)s
GROUP BY supplier_name;

Regards
Ruchin Sharma

4

python is not able to find the supplier key in the dictionary, can you please check the filters dict

5

@makarand_b
Sorry I have no idea about how to check the same.
I appreciate if you can let me know how to check the same.

Regards
Ruchin Sharma

6

add print statement or frappe.errprint and check the filters value in the console

7

@makarand_b
Thanks a lot for the same, let me check.

Regards
Ruchin Sharma

8

what report type do you created? Query Report or Script Report.

i think you use “Query Report”:

SELECT
supplier_name AS “Supplier::250”,
SUM(base_total) AS “Amount (Without Tax):Float:250”,
SUM(total_taxes_and_charges) AS “Tax and Charges:Float:250”,
SUM(base_grand_total) AS “Grand Total:Float:250”
FROM tabPurchase Receipt
WHERE status=‘To Bill’
GROUP BY supplier_name;

i think,
“query report” can not create filter with javascript.

but if you make report with “Script Report” you can adding filter with javascript

1 Like
9

@komsel2228
Yes you are right I used Query Report as Report Type.
Apart from that the filter used for From Date and To Date is working fine in Query Report but it the supplier filter is not working at all.

Regards
Ruchin Sharma

1 Like
10

i never create “Query Report”.
i’ve create “Script Report” and use filter and its work.

i will try your case. i hope we can study together to solving your problems.

11

@komsel2228 Yes, you are right like I do not have much idea about creating Script Report.

Regards
Ruchin Sharma

12

@ruchin78
i try your query report.
why you get message error because in first run, your supplier is null.

but if you add, default in your js, fieldname “supplier”, you not get message error.

{
“fieldname”:“supplier”,
“label”: __(“Supplier”),
“fieldtype”: “Link”,
“options”: “Supplier”,
“default”: “halo brother” —example
}

13

@komsel2228
Yes, you are right it is working fine for me but it the default value is an issue then it should also work without default value by using the below query:

SELECT 
supplier_name AS "supplier:Link/Supplier:250",
SUM(base_total) AS "Amount (Without Tax):Float:250",
SUM(total_taxes_and_charges) AS "Tax and Charges:Float:250",
SUM(base_grand_total) AS "Grand Total:Float:250"
FROM `tabPurchase Receipt`
WHERE status='To Bill'
AND posting_date between %(from_date)s and %(to_date)s
AND supplier_name = IFNULL(%(supplier)s,supplier_name)
GROUP BY supplier_name;

Regards
Ruchin Sharma

14

I am facing same issue. i.e. link type filter and if it contains value then condition should get apply else not. I am also using query report

15

Check this out.