Sales invoice custom Print format with multiple payment entry

ERPNext Customize ERPNext
1

Hi

I am trying to pull the payment entries for a particular Invoice in custom print format.

The problem is “Payment Entry doctype” doesn’t have the invoice number but has the posting date , where as the “Payment Entry Reference” doctype does have the Invoice no. but not the posting date.

When I use frappe.db.get_value ,getting the invoice number will be fine when there is only a single payment against it.

Using frappe.db.get_list doesn’t work due to the issue of references mentioned above ??

How do you get multiple payment for a single invoice in Jinja??

2

This might be a good use for the new query builder, which can do table joins.

https://frappeframework.com/docs/v13/user/en/api/query-builder#joins-and-sub-queries

Barring that, you could always just poll each of the Payment Entry docs individually, based on the list you generate from matching Payment Entry References against Invoices. You’ve presumably already got a loop set up for the output, so you can just use a regular get_doc call to fetch things like invoice date.

3

Thanks @peterg for the reply

this is what i have used…

{% set c = doc.name %}
{% set d = frappe.db.get_value("Payment Entry Reference", {"reference_name": c }, "parent") %}
{% set e = frappe.db.get_value("Payment Entry", {"name": d }, "posting_date") %}
{% set f = frappe.db.get_value("Payment Entry", {"name": d }, "paid_amount") %}
<td colspan='4' style="text-align: right;border:none; color:green !important;">Payment Received on {{frappe.format( e ) }}</td>
<td colspan='1' style="text-align: right; font-weight: normal;border:none;color:green !important;"> {{ frappe.utils.fmt_money(f, precision=2, currency=doc.currency) }}</td>

Now the above is all fine if you only have a single payment against the invoice.

If i change the second line frappe.db.get_value to frappe.db.get_list

I get the “Parent” references in an array… I am stumped how use that to move forward.

Regarding Query builder… don’t you think thats an overkill for this print format ?

4

I’m not sure why it’d be overkill. A single database call gets you everything you need as one consolidated list.

Reference = frappe.qb.DocType("Payment Entry Reference")
Payment = frappe.qb.DocType("Payment Entry")
results = ( frappe.qb.from_(Reference)
            .inner_join(Payment)
            .on(Reference.parent == Payment.name)
            .select(Payment.posting_date, Reference.allocated_amount)
            .where(Reference.reference_name == doc.name)
            .run(as_dict=True) )

# [{'posting_date': datetime.date(2022, 1, 17), 'allocated_amount': 5000.0}, {'posting_date': datetime.date(2022, 1, 17), 'allocated_amount': 23251.79}, {'posting_date': datetime.date(2022, 1, 17), 'allocated_amount': 10000.0}, {'posting_date': datetime.date(2022, 1, 17), 'allocated_amount': 10251.79}]

In the end, it’s really just a question of whether you’re more comfortable with procedural code or database queries. Both work fine.

In either case:

If you need to handle an arbitrary number of payments, that’s what loops are for. Something like:

{% for result in results %}
   {{ result.posting_date }} : {{ result.allocated_amount }}
{% endfor %}
1 Like
5

Thanks @peterg… I haven’t used the query builder before, hence the ignorance.

But looking at these code, its much cleaner than going the convoluted way I have used.

6

Hey there!
im just facing the same problem here as we need to list our payments on our final invoice.
I cant seem to get your code to work further than the first payment in line.
Tbh i dont understand how to use the query builder or where i can use it.

Thank you in advance … maybe youve already got a solution. @centaur

7

The solution is using the query builder (using frappe.qb.Doctype)

In a single database call you get all you need and then join them together as shown by peterg

You use it in your custom print format

8

this is useful information