Filter date between using FrappeClient get_value

Hello,
I have a hosted ERPNext system needs a customization, I want to get a total of the field from the database
so I have used get_value function but I have a problem when I using between operand in the filter for a date, this is my code:

		frappe.db.get_value('Attendance', {
    		'employee': cur_frm.doc.employee,
    		'docstatus': 1,
    		'attendance_date': ["<", '2018-09-21' ],
    		'attendance_date': [">=", '2018-08-21' ]
    	}, 'sum(actual_working_hours)', function(r) {
    		console.log(r)
		});

In the above code, it seems that it goes for the first filter of the same field
so this line wont run ā€˜attendance_dateā€™: [ā€œ>=ā€, ā€˜2018-08-21ā€™ ]
but in my situation, I need both greater than and less than operand to run

I have used between in the filter as followed:

		frappe.db.get_value('Attendance', {
    		'employee': cur_frm.doc.employee,
    		'docstatus': 1,
    		'attendance_date': ["between", ["2018-08-21", "2018-09-21"]]
    	}, 'sum(actual_working_hours)', function(r) {
    		console.log(r)
		});

but I have bellow error!

InternalError: (1241, u'Operand should contain 1 column(s)')

Is there Any other solution?
Thanks

Modify your code something like this and then give it a try

 filters: [
          ["start_date","<=", frappe.datetime.nowdate()],
          ["end_date",">=", frappe.datetime.nowdate()]

same problem :confused:
InternalError: (1241, uā€™Operand should contain 1 column(s)')

Done
I have solved this issue by getting an array of all dates is between the interval I have(start_date,end_date)
and then use in operand, and it works

Would this work?

	frappe.db.get_value('Attendance', {
		'employee': cur_frm.doc.employee,
		'docstatus': 1,
		'attendance_date': (">=", "2018-08-21"),
		'attendance_date': ("<=", "2018-09-21")
        },
        'sum(actual_working_hours)',
        function(r) {
		    console.log(r)
        }
    });
2 Likes

can you please share the code?

I want to add one more filter to this such as start date and end date so that i can filter the data between two specific dates.
currently i get data for all 365 days.

frappe.ui.form.on(ā€œDaily Status Reportā€,ā€œbefore_submitā€ ,function(frm) {
frappe.call({
ā€œmethodā€: ā€œfrappe.client.getā€,
args: {
doctype: ā€œCalendar DSRā€,
filters:{
employee: cur_frm.doc.employee_id,
date: cur_frm.doc.date
},
fieldname: [ā€œdsrā€]
},
callback: function (data) {
frappe.call({
ā€œmethodā€: ā€œfrappe.client.set_valueā€,
ā€œargsā€: {
ā€œdoctypeā€: ā€œCalendar DSRā€,
ā€œnameā€: data.message.name,
ā€œfieldnameā€: ā€œdsrā€,
ā€œvalueā€: 1
}
});
}
});
});

@Ashutosh_Halape , You can use between operator,

var sDate = cur_frm.doc.start_date;
var eDate = cur_frm.doc.end_date;

date: [ā€˜Betweenā€™,sDate,ā€˜andā€™,eDate],

	frappe.db.get_value('Attendance', {
		'employee': cur_frm.doc.employee,
		'docstatus': 1,
		'attendance_date': ["in", listDate(convert(previous_month1),convert(end_date)) ]
	}, 'sum(day_total_points)', function(r) {
		
	});




	function convert(timestamp) {
		var date = new Date(timestamp);
		return [date.getFullYear(),("0" + (date.getMonth()+1)).slice(-2),("0" + date.getDate()).slice(-2),].join('-');
	}


	function listDate(startDate,endDate){
		var listDate = [];
		var dateMove = new Date(startDate);
		var strDate = startDate;

		while (strDate < endDate){
		  var strDate = dateMove.toISOString().slice(0,10);
		  listDate.push(strDate);
		  dateMove.setDate(dateMove.getDate()+1);
		};
		return listDate
	}
2 Likes

thank you @OmarJaber

@ROHAN_JAIN1 where do i add the var part and the date part

In place of this,
date: [ā€˜Betweenā€™,sDate,ā€˜andā€™,eDate],

thank you @ROHAN_JAIN1 iā€™ll try this
i am new to ERPNext so donā€™t know much about it

you canā€™t get two values in a Python dictionary with the same key

Tried out this way, and itā€™s brought an error.
Trying to filter using the current date where I donā€™t have to do it manually but it picks the current date automatically

I tried a lot with it but finally, I create my own function to get all dates in an array and then check them

Thank you.

I was able to filter current date in my code by using frappe.utils.nowdate() where i donā€™t have to enter the date manually

filters = {
ā€˜docstatusā€™ : 1,
ā€˜closing_dateā€™ : [ ā€œ>=ā€, frappe.utils.nowdate() ]
},