Filter date between using FrappeClient get_value

ERPNext
1

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

2

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

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

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

4

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

5

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
6

can you please share the code?

7

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
}
});
}
});
});

8

@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],

9 
	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
10

thank you @OmarJaber

11

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

12

In place of this,
date: [‘Between’,sDate,‘and’,eDate],

13

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

14

you can’t get two values in a Python dictionary with the same key

15

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

16

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

17

Thank you.

18

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() ]
},