Customer Statement

[lasalesi]

Hi @hifyaan,

have you considered adding a custom print format to the customer record? This way you can aggregate the data from this customer and get a statement of account. Something like this

<!-- FIND OUTSTANDING INVOICES -->
 {% set unpaid_sales_invoices = frappe.get_all('Sales Invoice', filters={'docstatus': 1, 'customer': doc.name}, fields=['name', 'outstanding_amount', 'posting_date', 'due_date', 'rounded_total', 'grand_total' ]) %} 

<!-- positions -->
<p><br /></p>
<table style="width: 100%;">
  <tr style="border-bottom: 1px solid silver;">
	<td style="width: 18%; "><strong>{{ _("Document number") }}</strong></td>
	<td style="width: 15%; "><strong>{{ _("Date") }}</strong></td>
	<td style="width: 17%; "><strong>{{ _("Text") }}</strong></td>
	<td style="width: 12%; text-align: right; "><strong>{{ _("Amount") }}</strong></td>
	<td style="width: 13%; text-align: right; "><strong>{{ _("Due till") }}</strong></td>
	<td style="width: 12%; text-align: right; "><strong>{{ _("Reminder") }}</strong></td>
	<td style="width: 13%; text-align: right; "><strong>{{ _("Outstanding") }}</strong></td>
 </tr>
 {% set now = frappe.utils.now() %}
 {% set vars = {'sum': 0} %}
 {% for sales_invoice in unpaid_sales_invoices %}
   {% if sales_invoice.outstanding_amount %}
	 <tr style="border-top: 1px solid silver; ">
	   <td>{{ sales_invoice.name }}</td>
	   <td>{{ frappe.format_value(sales_invoice.posting_date, {'fieldtype': 'Date'}) }}</td>
	   <td>{{ _("Sales Invoice") }}</td>
	   <td style="text-align: right; ">{{ "{:,.2f}".format(sales_invoice.grand_total).replace(",", "'").replace(".", ",").replace("'", ".") }}</td>
	   <td style="text-align: right; ">{{ frappe.format_value(sales_invoice.due_date, {'fieldtype': 'Date'}) }}</td>
	   {% set age = frappe.utils.date_diff(now, sales_invoice.due_date) %}
	   <td style="text-align: right; ">
		 {% if age > 60 %}3{% elif age > 30 %}2{% elif age > 0 %}1{% else %}0{% endif %}</td>
	   <td style="text-align: right; ">{{ "{:,.2f}".format(sales_invoice.outstanding_amount).replace(",", "'").replace(".", ",").replace("'", ".") }}</td>
	   {% if vars.update({'sum': vars.sum + sales_invoice.outstanding_amount}) %}{% endif %}
	 </tr>
  {% endif %}
{% endfor %}
	 <tr style="border-top: 1px solid silver; ">
	   <td colspan=5><strong>{{ _("Balance") }}</strong></td>
	   <td colspan=2 style="text-align: right; "><strong>EUR {{ "{:,.2f}".format(vars.sum).replace(",", "'").replace(".", ",").replace("'", ".") }}</strong></td>
	 </tr>
</table>

Hope this helps.

[Albertus]

Good Day

Hope this could help someone:

Multiple Customer Account Statements

Hope someone could implement this into ERPNext.

Thank You

Albertus Geyser

[charlie-cook]

I think the point here is that it should be standard in any ERP software and your workaround may be fine for one individual customer statement. The issue is when you need to run customer statements at the end/beginning of every month for ALL customers at one time. Ideally, there would be a Customer Statements module and you’d click to email/print customer statements to everyone at the same time.

[lasalesi]

Why do you consider this a workaround? This is actually a neat solution to generate statements of account from the customer (or supplier). It also works on a range/selection of customers, simply filter your list for the ones with outstanding_balance > 0, select all and click print…

If you need this in bulk, the use case might be different. For example payment reminders. But that then is not only a statement of account, but also includes payment reminder levels and charges. A module for that is available (not in the core though)…

[charlie-cook]

Ahh… that’s a good idea to filter and the select multiple. I was thinking v10, which would not have allowed the multiple selection with as many options. I will give this a shot.

What about emailing the customer statements? I’m assuming this will only email to the primary contact on the account? We need to email to the Accounts Payable contact.

Thank you!

[olamide_shodunke]

How do we add Logo at the top ?

Where do we add start and end dates ? Do we have to change data in the code for this ?

[lasalesi]

It is a normal print format, you can include for example a normal letter head to have your logo and header printed:

<!-- HEAD -->
<div id="header-html" class="hidden-pdf">
  {% set letter_head = frappe.get_doc("Letter Head", "Standard") %}
  {% if letter_head %}
    {{ letter_head.content }}
  {% else %}
    <p>Letter head Standard not found. Please define the letter head under print settings.</p>
  {% endif %}
</div>

In this form it is not date-drive, it will shown all sales invoices based on outstanding_amount. If you want this based on a time period, you will have to change the lookup code, e.g. like this (current year):

<!-- FIND OUTSTANDING INVOICES -->
 {% set year = frappe.utils.now()[0:4] | int %}
 {% set sales_invoices = frappe.get_all('Sales Invoice', 
  filters=[['docstatus', '=', 1], 
  ['customer', '=', doc.name],
  ['posting_date', '>=', "{0}-01-01".format(year)],
  ['posting_date', '<', "{0}-01-01".format(year + 1)]], 
  fields=['name', 'outstanding_amount', 'posting_date', 'due_date', 'rounded_total', 'grand_total' ]) %}

[Albertus]

Good Day

We using my Jasper Reports Statement and must say a nice alternative for nothing else.
In my report you select first day of month and will calculate last day of month and then you select customer and will generate your statement ready to send out as pdf with links on document number to open actual document on ERPNext.

I agree that this should be standard on ERPNext.

Multiple Customer Account Statements

Good Day My new Jasper Reports Statement promised to post: [Statement2] Please show me how many interested then i will upload Jasper Reports file to use on your ERPNext Database. Thank You Albertus Geyser

[olamide_shodunke]

This is Elegant and Beautiful.

Works as advertised

Thanks @lasalesi

[hifyaan]

HI LASALESI
sorry was out of town for some time
i had a look at this it looks good …but its only for outstanding inv the wau i see it
a statements should run from month to month like how albertus has shown
or maybe i have missed something
the second one that is date driven i get an error
expected name name tag but got something else
i must gave done something wrong

[charlie-cook]

Just to note: you can’t select multiple and email it automatically to the contact on file. You still have a lot of manual steps to use this if you plan on emailing your statements rather than print and send via mail.

[hifyaan]

am i missing something but is there still no customer statements build into erp next
just dumb struck at this
can anyone shed some light on this
oh not any work arounds just old plain fashioned statements
which is so vital to any business
this was a comment from jaichavan " Hello,
We will evaluate the request and see how it can be implemented in the future versions.
We appreciate your patience and cooperation. Thanks." when nov 2018
one year ago
https://github.com/frappe/erpnext/issues/14653#issuecomment-437647397
one year later niks nada nothing

[crafter]

I posted a solution for this a few days ago.

Client Statements available for download

I have forked a solution for generating Client Statements from the ’ version-12’ branch. You can get a copy of the latest version from the URL below. Before you do anything, read the readme This is a work in progress and I would not call it stable at this stage. If you install and test it, please provide me feedback. [Screenshot%20from%202019-11-08%2014-31-22] [Screenshot%20from%202019-11-08%2014-32-48]!

Please wait a day or two before evaulating. There was a bug in calculating the balance in the general ledger report from which this was based introduced by the update_payment_reference() function call. I just corrected the bug in my statement solution and I am busy testing.

[crafter]

The bug fix been applied and is available on the Client Statements branch.

[DamGrynn]

I would really like to see this basic feature too.

[laurence]

The solution posted above seems to be developed for server side scripting. Is there a client side solution?

[lasalesi]

The Jinja solution, altough being server-sided, can be configured in the print format… Or is there a specific reason for a client-side solution?

[laurence]

Client side because prefer to use erpnext.com hosted

[lasalesi]

This solution is based on a custom print format, which can be implemented on erpnext.com hosted instances without any issue:

Customer Statement

Hi @hifyaan, have you considered adding a custom print format to the customer record? This way you can aggregate the data from this customer and get a statement of account. Something like this <!-- FIND OUTSTANDING INVOICES --> {% set unpaid_sales_invoices = frappe.get_all('Sales Invoice', filters={'docstatus': 1, 'customer': doc.name}, fields=['name', 'outstanding_amount', 'posting_date', 'due_date', 'rounded_total', 'grand_total' ]) %} <!-- positions --> <p><br /></p> <table style="width:…

[charlie-cook]

I am getting this same error message