“Cheque/Reference Date” and the “Cheque/Reference No.” in “Sales Invoice”-Report

ERPNext Reports
1

Hi,
I have a question about the reports. In the reports for “Sales Invoice” I can select the “Date” of the invoice for the report. In the “Payment Entry” I can select the “Cheque/Reference Date” and the “Cheque/Reference No.”
But I need the the “Cheque/Reference Date” and the “Cheque/Reference No.” in the “Sales Invoice”-Report or the “Date” of the invoice in the “Payment Entry”.
Is there any solution to this problem? I would prefer the first one. (“Cheque/Reference Date” and the “Cheque/Reference No.” in the “Sales Invoice”-Report)

The reason is that our tax consultant would like to have one sheet with the invoice date, reference date when an amount has been transferred to the bank account and reference no. of the transaction.
In my case I save this data under the TRANSACTION IC – section to “Cheque/Reference Date” and the “Cheque/Reference No.” under “payment entry”.

Thanks for your help! Gratings
christophK

Sales Invoice
Sales Invoice1152×257 19.4 KB

Payment Entry
Payment Entry1146×246 23.7 KB

2

@christophK you will need to make a custom report for this. Maybe you can take help form someone in the community, or start a bounty (I would recommend $200)

https://erpnext.org/bounties

3

Hi @rmehta

Trust you’re doing great. Sorry to digress here but this has been one of my concerns when it comes to bounties and I know a lot of community members feel the same as they have expressed it openly on the forum:

How does one know what to set as a target especially if you’re not a developer yourself?

In this particular case, you’ve taken away that burden by making a recommendation. Would it be possible to add some sort of mechanism to bounties so that Service Providers or other knowledgeable Community members could suggest a ‘reasonable’ target?

Thanks

4

Okay thx. And is it possible to generate a custom report without editing the source code? If yes: I’m familiar with sql queering. Are there some hints on how to do that.

Otherwise I will create a bounty and hope the feature will come soon.

5

You can make a Query Report to achieve this.

https://frappe.io/docs/user/en/guides/reports-and-printing/how-to-make-query-report.html

1 Like
6

Thanks again for this solution. If it may helps someone, this is my sql query:

SELECT 

invoice.name AS 'Rechnungsnummer', 
invoice.customer_name AS 'Kunde:Link:150',
invoice.grand_total AS 'Gesamtbetrag:Int:100',
invoice.outstanding_amount AS 'Ausstehender Betrag',
invoice.status AS 'Status:Link:60',
invoice.contact_person AS 'Kontaktperson:Link:250',
invoice.posting_date AS 'Rechnungsdatum:Date/Date:150',
payment.reference_date AS 'Referenzdatum:Date:100',
payment.reference_no AS 'Referenznummer:Link:190'

FROM `tabPayment Entry Reference` AS reference RIGHT OUTER JOIN `tabSales Invoice` AS invoice ON reference.reference_name=invoice.name LEFT OUTER JOIN `tabPayment Entry` AS payment ON reference.parent=payment.name

WHERE DATE(invoice.posting_date) >= '2017-01-01'  AND DATE(invoice.posting_date) < '2018-01-01'

ORDER BY `Rechnungsnummer`  DESC

By the way. I tried to use the DATE_FORMAT function to only show the month of the date in a column for easier filtering per month in the report, but the function does not work.