How to add FROM Date and TO Date in ERPNext Query Report

ERPNext
1

Hi everyone,

Can anybody guide me to add FROM Date and TO Date in ERPNext Query Report.

2

The Report Builder can select Date Between . Would that work?

3

Add this to report_name.js file

frappe.query_reports[“Report Name”] = {
“filters”: [
{
“fieldname”: “from_date”,
“label”: __(“In Date From”),
“fieldtype”: “Date”,
“default”: frappe.datetime.get_today(),
“reqd”: 1
},
{
“fieldname”: “to_date”,
“label”: __(“To”),
“fieldtype”: “Date”,
“default”: frappe.datetime.get_today(),
“reqd”: 1
}
]
}

3 Likes
4

SanRam added the client-side filters here, but don’t forget to process this information also server-side in the .py file or else it won’t do anything.

You can for example set an emtpy directory as the variable “filters”

filters = {}

Then you can fill this empty dictionary with the from and to dates like so:

from_date = filters.get('from_date')
to_date = filters.get('to_date')

Now you can use these variables in your report.

1 Like
5

report_name.js:

frappe.query_reports[“Report Name”] = {
“filters”: [
{
“fieldname”: “from_date”,
“label”: __(“In Date From”),
“fieldtype”: “Date”,
“default”: frappe.datetime.get_today(),
“reqd”: 1
},
{
“fieldname”: “to_date”,
“label”: __(“To”),
“fieldtype”: “Date”,
“default”: frappe.datetime.get_today(),
“reqd”: 1
}
]
}

then query report:

SELECT ‘tabTable Name’.‘field_name’
FROM ‘tabTable Name’
WHERE (DATE(‘tabTable Name’.‘date_field’) between %(from_date)s and %(to_date)s)

6

Hi

How to fetch the transaction date is Today for query report

7

@ValS -

Thank you for highlighting the server side config. I’ve been trying the following but with no success:
`from future import unicode_literals

import frappe
from frappe import _
from frappe.utils import flt, cint, getdate
from frappe.utils import nowdate

def execute(filters=None):
columns, data = [], []
return columns, data
from_date = filters.get(‘from_date’)
to_date = filters.get(‘to_date’)`

I’d appreciate if you could advise where I’m getting it wrong.

Thanks in advance

8

Is this your full code? Because it seems that you didn’t declare filters as variable first and well, that means you can’t use it in any way.

9

@ValS

Thanks for the prompt response.

I greatly appreciate it.

Yes- that is my full .py code. :pensive:

Thanks for pointing out where I went wrong.
Let me try to get it right and if/when I do- I’ll post the working code here.

10

There is something wrong with that code.

On line 3, you return from the execute function()
return columns, data

This means that lines 4 and 5 will never be executed.

11

@crafter @ValS - Thank you for the pointers .

I’ve tried the following but can’t seem to find joy. Could you advise where I’m getting it wrong still:

from future import unicode_literals
import frappe
from frappe import msgprint, _
from frappe.utils import getdate, cstr, flt, fmt_money


def execute(filters=None):
	if not filters:
		return [], []	
	if not filters.get("to_date"):
		frappe.throw(_("'To Date' is required"))
	if not filters.get("from_date"):
		frappe.throw(_("'From Date' is required"))
12

Have a look at my code here for a custom script report :

The file “client_statement.js” contains the filter parameters, which includes a start and end date.

The file “client_statement.py” contains the handling of the dates.
The dates are used within local functions, but you can see they are referred to by the notation as in the following example code

from_date, to_date = getdate(filters.from_date), getdate(filters.to_date)

13

@crafter :clap::clap::clap::clap::clap:

Thank you very much. I’ll check it out right now

14

@crafter - May you grant me some grace today but I have tried the below and still no success .
May you advise what I’m mssing?

from future import unicode_literals
import frappe
from frappe import msgprint, _
from frappe.utils import getdate, cstr, flt, fmt_money


def execute(filters=None):

	if not filters:
		return [], []	
	

		

def get_conditions(filters):
	conditions = []
	from_date, to_date = getdate(filters.from_date), getdate(filters.to_date)
15

Do you have any errors when you run this code?
What does the console return, if you print filters?

If it returns nothing, then if not filters will always be true, meaning your script ends at this point, if I’m not mistaken. I’m afraid I can’t help much more beyond that

16

@ValS

Thank you for replying.

The console gives the message below.

Uncaught SyntaxError: Invalid or unexpected token
    at Object.eval (dom.js:33)
    at query_report.js:172
query_report.js:177 Uncaught (in promise) TypeError: Cannot set property 'html_format' of undefined
    at query_report.js:177
    at request.js:381
    at new Promise (<anonymous>)
    at Object.frappe.after_ajax (request.js:374)
    at query_report.js:175
17

@crafter - any ideas?

18

@ValS

I don’t suppose you have any other pointers?

19

Can you post your whole .py code AND .js code. Declare the filters in .js just like @SanRam suggested . Right after declaring it in js you should be able to see the filters on your report page after refreshing it. You can then move on to retrieving and manipulating data via server side.

20

I’m sorry, I can’t be of any more help right now.