ERPNext Foundation ERPNext Cloud User Manual Blog Discuss Frappé* Donate

Customer Statement


#1

Is there any report to get the Customer Statement. Example : Customer A get a sales invoice and he paid that in 5 instalment. Before he pay the last part he requested a statement for all his transactions. That what I am after.

Malo
Sione


#2

I think no, but you can make this using script report


#3

Thanks Omar. I will try and create a custom Report and see if others want to use that too. Will update on the progress


#4

Hi Sione,

You can actually get a statement of the customer by using the General Ledger and filtering based on

  1. Party Type = Customer
    2, Select your customer
  2. Select a date range that you want
    4,. And even a voucher number if you want to be more specific.

That should give you the statement for the customer. You can then simply send to PDF, Export or Print.

Hope this helps


#5

This works but is very time consuming if you have hundreds of customers. Should be able to push a button and generate a customer statement for every customer. It should show their transactions for set time period (monthly for us) and then have option to print or email these directly to the customer. Transactions should consist of orders/dates plus payments/dates and then an outstanding balance total. This is pretty standard in most accounting softwares.


#6

absolutely agreed that is standard on any software


#7

i cant for the hell of it understand how is it possible to have an erp system without having customer statements or supplier statements … is it only us few that need it or is it just that this was an over sight by the developers or is it that in there part of the world they don’t issue any statements mmmm

strange one this …this in fact is the first time i have seen no statements i mean thats the basics of having a debtors or creditors module if it was purely a point of sale cash only yes then i would understand …
lol just wee bit of frustration creeping in there


#8

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.


#9

Good Day

Hope this could help someone:

Hope someone could implement this into ERPNext.

Thank You

Albertus Geyser


#10

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.


#11

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)…


#12

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!


#13

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 ?


#14

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' ]) %}

#15

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.


#16

This is Elegant and Beautiful.

Works as advertised

Thanks @lasalesi


#17

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


#18

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.