How to filter blank values in a script report columns

ERPNext Reports
1

I have created a script report with filters.
Along with the filters if i type value in the columns the report filters the respective value in the column.

The report table has got blank values in many columns.
by using the standard columns I want to filter only non blank values
is there a way to do this in script report

below is the screen shot. say I want to filter only non blank values in incoterms, how can i achieve the same.

image
image1322×1096 94 KB

2

Hi @gsarunk,

Add incoterms filter in your_report.js and set the condition of the filter in the your_report.py

def get_data(filters):
    conditions = ""
    values = []

    if filters.get("incoterms"):
        conditions += " AND incoterms IS NOT NULL AND incoterms != ''"
        values.append(filters.get("incoterms"))
        # ... your query

Thank You!

3

If you want filter blank values you can use not equal operator <>

image
image978×1200 53.3 KB

4

@erpadmin_pspl
Thats fantastic. Similarly how to exclude blanks.

5

@gsarunk
Just click space bar and you will get all values exclude blanks.

6

@NCP defining filters for individual columns is not an ideal solution in this case. the report has more than 50 columns and at least 30 columns will have blank values. Also value change in filter will trigger the server side every time, and this will put report sluggish. Maybe something like what @erpadmin_pspl is what I am looking at.

7

@gsarunk
did you get the all values exclude blanks ?

8

Space bar did not solve the problem but >< solved the problem

1 Like
9

Hi @gsarunk,

You can also set like

frappe.query_reports["Your Report Name"] = {
	"filters": [
		{
			"fieldname": "incoterms_filter",
			"label": __("Incoterms Filter"),
			"fieldtype": "Select",
			"options": ["Non Blank", "Blank"]
		}
	]
};

def get_data(filters):
    conditions = ""
    values = []

    incoterms_filter = filters.get("incoterms_filter")

    if incoterms_filter:
        if incoterms_filter == "Non Blank":
            conditions += " AND incoterms IS NOT NULL AND incoterms != ''"
        elif incoterms_filter == "Blank":
            conditions += " AND (incoterms IS NULL OR incoterms = '')"

    # Add more filters for other columns if needed
    # ... your query

I hope this helps.

Thank You!

1 Like
10

You could try this approach for multi columns