Need to add custom field on general Ledger Report ?
I added custom filed called “description” in document type (Journal Entry Account)
and I need to show this filed in General Ledger Report.
in general_ledger.py file
modify the following list by add last member (from line 90 to 96]
columns += [
_(“Voucher Type”) + “::120”, (“Voucher No”) + “:Dynamic Link/”+(“Voucher Type”)+“:160”,
_(“Against Account”) + “::120”, _(“Party Type”) + “::80”, _(“Party”) + “::150”,
_(“Project”) + “:Link/Project:100”, _(“Cost Center”) + “:Link/Cost Center:100”,
_(“Against Voucher Type”) + “::120”, (“Against Voucher”) + “:Dynamic Link/”+(“Against Voucher Type”)+“:160”,
(“Remarks”) + “::400” ,(“Description”) + “::400”
]
then in get_result_as_list function i amend it to get discription as the following :
def get_result_as_list(data, filters):
result = []
for d in data:
row = [d.get(“posting_date”), d.get(“account”), d.get(“debit”), d.get(“credit”)]
if filters.get("show_in_account_currency"):
row += [d.get("debit_in_account_currency"), d.get("credit_in_account_currency")]
row += [d.get("voucher_type"), d.get("voucher_no"), d.get("against"),
d.get("party_type"), d.get("party"), d.get("project"), d.get("cost_center"), d.get("against_voucher_type"), d.get("against_voucher"), d.get("remarks"), d.get("description")
]
result.append(row)
the filed appeared but without data.
Data is obtained by the following query:
gl_entries = frappe.db.sql(“”"
select
posting_date, account, party_type, party,
sum(debit) as debit, sum(credit) as credit,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher,
remarks , against, is_opening {select_fields}
from tabGL Entry
where company=%(company)s {conditions}
{group_by_condition}
order by posting_date, account"“”
.format(select_fields=select_fields, conditions=get_conditions(filters),
group_by_condition=group_by_condition), filters, as_dict=1)
To get data I noted that party_type and party fileds belong to “Journal Entry Account” document type so I ammend the query to include description after “party”
as the following :
gl_entries = frappe.db.sql(“”"
select
posting_date, account, party_type, party, description,
sum(debit) as debit, sum(credit) as credit,
voucher_type, voucher_no, cost_center, project,
against_voucher_type, against_voucher,
remarks , against, is_opening {select_fields}
from tabGL Entry
where company=%(company)s {conditions}
{group_by_condition}
order by posting_date, account"“”
.format(select_fields=select_fields, conditions=get_conditions(filters),
group_by_condition=group_by_condition), filters, as_dict=1)
an error occured
Traceback (most recent call last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/app.py”, line 55, in application
response = frappe.handler.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 19, in handle
execute_cmd(cmd)
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 40, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 897, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py”, line 93, in run
res = frappe.get_attr(method_name)(frappe._dict(filters))
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/general_ledger/general_ledger.py”, line 23, in execute
res = get_result(filters, account_details)
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/general_ledger/general_ledger.py”, line 101, in get_result
gl_entries = get_gl_entries(filters)
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/general_ledger/general_ledger.py”, line 129, in get_gl_entries
group_by_condition=group_by_condition), filters, as_dict=1)
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 137, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1054, “Unknown column ‘description’ in ‘field list’”)