We have a Bahrain based client who is in B2B & B2C kitchen items business. Many times the client has more than 50 items per invoice. Client wanted to ensure they have complete track of qty ordered, delivered and balance to be delivered per item and total. The information was to also be printed in the the ERPNext Custom Delivery Note.

Native delivery note of ERPNext only displays the quantify that is being delivered. It neither displays total ordered or total delivered. Client threw a challenge at us to customize the delivery note print format to display all the three information. And to sum up the qty of each column at the bottom of print. The information is to be dynamic as an item can be delivered at multiple time. To achieve clients requirement we looked at various method.

After looking at all the available fields in sales invoice and delivery note we realized that a custom script is required. Firstly, we set the following script at stat of the items table.

{% set items_qty = [] %}
{% set items_ordered_qty = [] %}
{% for item in doc.items %}
{% set ordered_items = frappe.get_all("Sales Invoice Item", fields = ["qty"], filters = {
"parent": item.against_sales_invoice,
"item_code": item.item_code,
"docstatus": 1
}) %}
{% set delivered_items = frappe.get_all("Delivery Note Item", fields = ["qty"], filters = {
"against_sales_invoice": item.against_sales_invoice,
"item_code": item.item_code,
"docstatus": 1
}) %}

To above code fetches the items quantity for its corresponding sales invoice. It also fetches quantity fro previously submitted delivery note. After getting both information it is time to make Jinja template do some basic math. Firstly we need to display the total ordered quantity in the original invoice.

{% set qty = ordered_items | sum(attribute = "qty") %}
{{ qty }}
{% if items_qty.append(qty) -%}{% endif %}
This code displays the actual quantity under header named Qty.

Then we needed to display qty that was delivered in the current delivery note
{{item.qty}}

Finally we had to display the balance balance undelivered quantity that would be delivered in future
{% if doc.docstatus == 1 %}
{% set qty = ordered_items|sum(attribute="qty") - delivered_items|sum(attribute="qty") %}
{{ qty }}
{% if items_ordered_qty.append(qty) -%}{% endif %}
{% else %}
{% set qty = ordered_items|sum(attribute="qty") - ((item.qty) + delivered_items|sum(attribute="qty")) %}
{{ qty }}
{% if items_ordered_qty.append(qty) -%}{% endif %}
{% endif %}

All the numbers per column were to be summed up.
{{ items_qty|sum }} Displays sum of ordered qty.
{{doc.get_formatted("total_qty", doc)}} Displays sum of delivered qty.
{{ items_ordered_qty|sum }} Displays sum of residual/balance column.

The end result of our ERPNext Custom Delivery Note looked of the like this image