ERPNext.com Frappe Cloud Support Partners Foundation Frappe School

Is Null in get_list for date or time field

Hi all ,
i have fields of time and date and i want to select fields are not null.
when i write

frappe.get_list("Attendance", fields=["employee", "status"],\
                            filters=[["attendance_date", "=", date], \
                                     ["attend_time", "!=", ""], ["leave_time", "!=", ""]]
		)

it gives me error

ValueError: (u'String does not contain a date:', u'')

any help please :frowning:

frappe.get_list("Attendance", fields=["employee", "status"],\
                            filters=[["attendance_date", "=", str(date)], \
                                     ["attend_time", "!=", ""], ["leave_time", "!=", ""]]
		)

what about these fileds

["attend_time", "!=", ""], ["leave_time", "!=", ""]

Leave as it is.

it gives me

raise ValueError("String does not contain a date:", timestr)
ValueError: (u'String does not contain a date:', u'')

Send me whole code

if doctype EmployeeAttendanceTool

@frappe.whitelist()
def get_employees(date, department=None, branch=None, company=None, attendance_type =None,end_date=None):
	attendance_not_marked = []
	attendance_marked = []
	filters = {"status": "Active"}
	if department != "All":
		filters["department"] = department
	if branch != "All":
		filters["branch"] = branch
	if company != "All":
		filters["company"] = company
	# if attendance_type != "All":
	# 	frappe.msgprint(attendance_type)
	# 	filters["attendance_type"] = frappe.get_list("Attendance Period", filters=[["attendance_type","=",attendance_type]], fields=["name"])
	
	employee_list = frappe.get_list("Employee", \
		fields=["employee", "employee_name"], \
		filters=filters, order_by="employee_name"
		)

	marked_employee = {}

	for emp in frappe.get_list("Attendance", fields=["employee", "status"],\
                            filters=[["attendance_date", "=", date], \
                                     ["attend_time", "=", 'Null']] # <<<<<<<<<<<< this condition gives Error 
		):
		marked_employee[emp['employee']] = emp['status']

	for employee in employee_list:
		employee['status'] = marked_employee.get(employee['employee'])
		if employee['employee'] not in marked_employee:
			attendance_not_marked.append(employee)
		else:
			attendance_marked.append(employee)
	return {
		"marked": attendance_marked,
		"unmarked": attendance_not_marked
	}
frappe.get_list("Attendance", fields=["employee", "status"],\
                            filters=[["attendance_date", "=", str(date)], \
                                     ["attend_time", "not in",null], ["leave_time", "not in", null]]
		)

["leave_time", "not in", null]

null here written as a variable
and is not defined

You can use None

Did this resolved your problem?

it does not work :frowning:
but i solved it by adding new condition in
function prepare_filter_condition in file db_query.py

		elif f.operator.lower() in ("is null", "is not null"):
			self.ignore_ifnull = True
			value = ""

and add value != “” in this condition

# put it inside double quotes
			if isinstance(value, string_types) and not f.operator.lower() == 'between' and value !="":
				value = '"{0}"'.format(frappe.db.escape(value, percent=False))

and

    valid_operators = ("=", "!=", ">", "<", ">=", "<=", "like", "not like", "in", "not in", "between","is null","is not null")
	if f.operator.lower() not in valid_operators:
		frappe.throw(frappe._("Operator must be one of {0}").format(", ".join(valid_operators)))
1 Like
frappe.get_list("Attendance", fields=["employee", "status"],\
                            filters=[["attendance_date", "in", null]]
		)