Column due_date not found in custom report

jcm · December 11, 2017, 4:48am

Hi everyone! I have a custom module for reporting and scripting.

I’ve created a new Script report and copy the exact content of the “Accounts Receivable Report” incluiding the html file, js and py. When I run the report I get the following error:

Traceback (most recent call last):
  File "/home/frappe/frappe-bench/apps/frappe/frappe/app.py", line 57, in application
    response = frappe.handler.handle()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 22, in handle
    data = execute_cmd(cmd)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 53, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 935, in call
    return fn(*args, **newargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py", line 96, in run
    res = frappe.get_attr(method_name)(frappe._dict(filters))
  File "/home/frappe/frappe-bench/apps/p4d/p4d/p4d/report/cobros/cobros.py", line 331, in execute
    return ReceivablePayableReports(filters).run(args)
  File "/home/frappe/frappe-bench/apps/p4d/p4d/p4d/report/cobros/cobros.py", line 20, in run
    data = self.get_data(party_naming_by, args)
  File "/home/frappe/frappe-bench/apps/p4d/p4d/p4d/report/cobros/cobros.py", line 94, in get_data
    future_vouchers = self.get_entries_after(self.filters.report_date, args.get("party_type"))
  File "/home/frappe/frappe-bench/apps/p4d/p4d/p4d/report/cobros/cobros.py", line 160, in get_entries_after
    return list(set([(e.voucher_type, e.voucher_no) for e in self.get_gl_entries(party_type)
  File "/home/frappe/frappe-bench/apps/p4d/p4d/p4d/report/cobros/cobros.py", line 260, in get_gl_entries
    .format(select_fields, conditions), values, as_dict=True)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 152, in sql
    self._cursor.execute(query, values)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
OperationalError: (1054, "Unknown column 'due_date' in 'field list'")

I checked 252 line which is:

			self.gl_entries = frappe.db.sql("""select name, posting_date, account, party_type, party, 
				voucher_type, voucher_no, against_voucher_type, against_voucher, due_date,
				account_currency, remarks, {0}
				from `tabGL Entry`
				where docstatus < 2 and party_type=%s and (party is not null and party != '') {1}
				group by voucher_type, voucher_no, against_voucher_type, against_voucher, party, due_date
				order by posting_date, party"""
				.format(select_fields, conditions), values, as_dict=True)

It seems that due_date is not defined in the “tabGL Entry” table. When I check the database structure I don’t find it:

+----------------------------+---------------+------+-----+----------+-------+
| Field                      | Type          | Null | Key | Default  | Extra |
+----------------------------+---------------+------+-----+----------+-------+
| name                       | varchar(140)  | NO   | PRI | NULL     |       |
| creation                   | datetime(6)   | YES  |     | NULL     |       |
| modified                   | datetime(6)   | YES  |     | NULL     |       |
| modified_by                | varchar(140)  | YES  |     | NULL     |       |
| owner                      | varchar(140)  | YES  |     | NULL     |       |
| docstatus                  | int(1)        | NO   |     | 0        |       |
| parent                     | varchar(140)  | YES  | MUL | NULL     |       |
| parentfield                | varchar(140)  | YES  |     | NULL     |       |
| parenttype                 | varchar(140)  | YES  |     | NULL     |       |
| idx                        | int(8)        | NO   |     | 0        |       |
| voucher_type               | varchar(140)  | YES  |     | NULL     |       |
| voucher_no                 | varchar(140)  | YES  | MUL | NULL     |       |
| cost_center                | varchar(140)  | YES  |     | NULL     |       |
| credit                     | decimal(18,6) | NO   |     | 0.000000 |       |
| party_type                 | varchar(140)  | YES  |     | NULL     |       |
| transaction_date           | date          | YES  |     | NULL     |       |
| debit                      | decimal(18,6) | NO   |     | 0.000000 |       |
| party                      | varchar(140)  | YES  | MUL | NULL     |       |
| _liked_by                  | text          | YES  |     | NULL     |       |
| company                    | varchar(140)  | YES  |     | NULL     |       |
| _assign                    | text          | YES  |     | NULL     |       |
| fiscal_year                | varchar(140)  | YES  |     | NULL     |       |
| _comments                  | text          | YES  |     | NULL     |       |
| is_advance                 | varchar(140)  | YES  |     | NULL     |       |
| remarks                    | text          | YES  |     | NULL     |       |
| account_currency           | varchar(140)  | YES  |     | NULL     |       |
| debit_in_account_currency  | decimal(18,6) | NO   |     | 0.000000 |       |
| _user_tags                 | text          | YES  |     | NULL     |       |
| account                    | varchar(140)  | YES  | MUL | NULL     |       |
| against_voucher_type       | varchar(140)  | YES  |     | NULL     |       |
| against                    | text          | YES  |     | NULL     |       |
| project                    | varchar(140)  | YES  |     | NULL     |       |
| against_voucher            | varchar(140)  | YES  | MUL | NULL     |       |
| is_opening                 | varchar(140)  | YES  |     | NULL     |       |
| posting_date               | date          | YES  | MUL | NULL     |       |
| credit_in_account_currency | decimal(18,6) | NO   |     | 0.000000 |       |
+----------------------------+---------------+------+-----+----------+-------+

The question is… why is this script working in the accouts receivable report but the same script is not in my custom app? Does anyone have a clue on this?

jcm · December 11, 2017, 3:15pm

If I remove the “due_date” column in the sql query it works, but I don’t understand why. Any ideas?

jcm · December 11, 2017, 4:34pm

I setted the loggin level in 2, the main diffrence between my custom report and the standard report is this:

My report:

select name, posting_date, account, party_type, party, 
				voucher_type, voucher_no, against_voucher_type, against_voucher, due_date,
				account_currency, remarks, sum(debit) as debit, sum(credit) as credit
				from `tabGL Entry`
				where docstatus < 2 and party_type=%s and (party is not null and party != '')  and company=%s
				group by voucher_type, voucher_no, against_voucher_type, against_voucher, party, due_date
				order by posting_date, party

Default Accounts receivable report:

select name, posting_date, account, party_type, party,
				voucher_type, voucher_no, against_voucher_type, against_voucher,
				account_currency, remarks, sum(debit) as debit, sum(credit) as credit
				from `tabGL Entry`
				where docstatus < 2 and party_type=%s and (party is not null and party != '')  and company=%s
				group by voucher_type, voucher_no, against_voucher_type, against_voucher, party
				order by posting_date, party

Somehow the “due_date” field is removed from the query. I can’t find that in the code.

tundebabzy · December 11, 2017, 5:20pm

Two suggestions:

Make sure you are not having a branch mismatch e.g ERPNext (hotfix), Frappe (develop)
Make sure migrations were run properly. Just run bench migrate

jcm · December 11, 2017, 5:29pm

Thanks for the answer!

I don’t have a branch mismactch.
Already did the bench migrate, still got the same result.