Hi all,
how can i insert multiple options in filters of query report?
my .js files is:
{
“fieldname”:“form_types”,
“label”:(“Form Type”),
“fieldtype”:“Select”,
“options”:“C-Form”
}
2 Likes
In the Query report, default Filter is available above each column. You could use that filter as well.
{
"fieldname":"form_types",
"label": __("Form Type"),
"fieldtype": "Select",
"options": ["","C-Form", "D-Form", "E-Form"]
},
3 Likes
Thanks Sangram,
This solves my problem, but filters are not working in above query, is there anything that i have to do?
Can you explain it in detail?
In below picture u can see that I had entered filters in “supplier” field as well as “Form type” field, but in filtered report it shows all suppliers and all form types
.
Please share your code
frappe.query_reports["CR Purchase Register"] = {
"filters": [
{
"fieldname":"posting_date",
"label":("From Date"),
"fieldtype":"Date",
"default":get_today(),
"reqd":1
},
{
"fieldname":"posting_date",
"label":("To Date"),
"fieldtype":"Date",
"default":get_today(),
"reqd":1
},
{
"fieldname":"supplier",
"label":("Supplier"),
"fieldtype":"Link",
"options":"Supplier"
},
{
"fieldname":"form_types",
"label":("Form Type"),
"fieldtype":"Select",
"options":["","C-Form",H-Form"]
}
]
}
2 Likes
select
tabPurchase Invoice.name as “ID:Link/Purchase Invoice:130”,
tabPurchase Invoice.posting_date as “Date:Date/Date:80”,
tabPurchase Invoice.form_types as “Form:Select/Purchase Invoice:50”,
tabPurchase Invoice.supplier_name as “Supplier Name:Data/Data:110”,
tabPurchase Invoice.bill_no as “Invoice No:Data/Data:80”,
tabPurchase Invoice.bill_date as “Invoice Date:Date/Date:80”,
tabPurchase Invoice.outstanding_amount as “Outstanding:Float/Currency:100”,
tabPurchase Invoice Item.item_name as “Item Name:Data/Data:125”,
tabPurchase Invoice Item.qty as “Qty:Int/Float:80”,
tabPurchase Invoice Item.rate as “Rate:Float/Currency:80”,
tabPurchase Invoice Item.amount as “Amount:Float/Currency:100”,
tabPurchase Invoice.total as “Total:Float/Currency:105”,
tabPurchase Invoice.base_total_taxes_and_charges as “Total Taxes:Float/Currency:80”,
tabPurchase Invoice.discount_amount as “Discount:Float/Currency:80”,
tabPurchase Invoice.grand_total as “Grand Total:Float/Currency:100”
from
tabPurchase Invoice,
tabPurchase Invoice Item
where
tabPurchase Invoice Item.parent=tabPurchase Invoice.name
have you seen this?
https://frappe.github.io/frappe/user/en/guides/reports-and-printing/how-to-make-query-report.html
https://frappe.io/docs/user/en/guides/reports-and-printing/how-to-make-query-report.html
Specifiaccly last section could help you
- Advanced (adding filters)
2 Likes
Yes, I have referred the manual and created the below mentioned query, but it works only for supplier if selected and form types if selected and if no supplier is selected it gives error.
select
tabPurchase Invoice.name as “ID:Link/Purchase Invoice:130”,
tabPurchase Invoice.posting_date as “Date:Date/Date:80”,
tabPurchase Invoice.form_types as “Form:Select/Purchase Invoice:50”,
tabPurchase Invoice.supplier as “Supplier:Data/Data:110”,
tabPurchase Invoice.bill_no as “Invoice No:Data/Data:80”,
tabPurchase Invoice.bill_date as “Invoice Date:Date/Date:80”,
tabPurchase Invoice.outstanding_amount as “Outstanding:Float/Currency:100”,
tabPurchase Invoice Item.item_name as “Item Name:Data/Data:125”,
tabPurchase Invoice Item.qty as “Qty:Int/Float:80”,
tabPurchase Invoice Item.rate as “Rate:Float/Currency:80”,
tabPurchase Invoice Item.amount as “Amount:Float/Currency:100”,
tabPurchase Invoice.total as “Total:Float/Currency:105”,
tabPurchase Invoice.base_total_taxes_and_charges as “Total Taxes:Float/Currency:80”,
tabPurchase Invoice.discount_amount as “Discount:Float/Currency:80”,
tabPurchase Invoice.grand_total as “Grand Total:Float/Currency:100”
from
tabPurchase Invoice,
tabPurchase Invoice Item
where
tabPurchase Invoice Item.parent=tabPurchase Invoice.name and form_types=%(form_types)s and supplier=%(supplier)s and posting_date=%(posting_date)s
you have to manage it in your script.
prepare filter condition if filters are available.
e.g.
if filters.get('supplier'):
filters_condition = " and supplier = %(supplier)s"%(filters.get('supplier'))
and merge it in your query : final query = query + filter_condition
try Script Report instead
2 Likes
@Sangram Thanks for the code, it helped me too… I have another question… Is there a possibility to select multiple from the options list? TIA
Can you elaborate on this? you mean multiple selections from the filter dropdown?
Yes… I will share a screenshot for the same here.
In the attached image where it shows ‘Select Columns’, I would like to select both ‘Is Contracted’ and ‘Company’ at the same time. Kindly advise…
1 Like
Currently, there is no such provision to select multiple options from a list. But if you are not applying any filter by default it gives all data. So, I think there is no need for multiple selections.
Also, there is by defaults filters- for listview and report builder report
@Sangram, thanks for your quick response.
My case is, I would add the new column to the report dynamically upon selection by user. (In the above example, the column ‘Contract’ is added dynamically once I select ‘Is Contracted’ from the drop down.
I should also provide option to select multiple columns for the user… I will be adding many more column names to the list where the user should be able to select more than 1 from the list… How should I go about this? Can I add it in some other form rather than filters?
Then you can add Data field as the filter and add your selection with comma separated.
e.g. Is Contracted, Company
So you can use this in a query with the help of IN operator.
1 Like
Say if I have 10 columns(the data you see in the drop down) to be selected, I should give the user the flexibility of selecting any number of columns from 1 to 10… I do not want to select all the columns at a stretch… Only the names selected by the user should appear in the report apart from the few fixed columns that will be already available… Your solution will not solve this purpose right? Please correct me if I’m wrong
yes. It just works around. Because multiple selections not available for now.