Loyalty Points on Invoice

Hello Community… I have a customer print format that is currently working well, but i would like to display the customer outstanding balance on the invoice and also print the accumulated customer Loyalty Points on the invoice. I have pulled from the tabSales Invoice the " loyalty_points" Field but it outputs zero. Also the “Outstanding_amount” prints the current invoice amount. See my code for the print format below.

indent preformatted text by 4 spaces
{%- macro add_header(page_num, max_pages, doc, letter_head, no_letterhead) -%}
{% if letter_head and not no_letterhead %}
<div class="letter-head">{{ letter_head }}</div>
{% endif %}
{%- if doc.meta.is_submittable and doc.docstatus==0-%}
<div class="alert alert-info text-center">
    <h4 style="margin: 0px;">{{ _("PROFORMA") }}</h4></div>
{%- endif -%}
{%- if doc.meta.is_submittable and doc.docstatus==2-%}
<div class="alert alert-danger text-center">
    <h4 style="margin: 0px;">{{ _("CANCELLED") }}</h4></div>
{%- endif -%}
{% if max_pages > 1 %}
<p class="text-right">{{ _("Page #{0} of {1}").format(page_num, max_pages) }}</p>
{% endif %} {%- endmacro -%}
{{ add_header(0,1,doc,letter_head, no_letterhead) }}
<table class="table table-bordered">
        <th width=50%>Customer Details</th>
		<th width=50%><b>Invoice#: {{doc.name}} Details </b></th>
        <td>{{ doc.customer }}<br>
        {{ doc.address_display or '' }}</td>
		<td>INVOICE #: {{doc.name}} <br>
		{% set u = frappe.get_doc("User", frappe.user) %} 
		    INVOICE DATE: {{ doc.get_formatted("posting_date") or '' }}<br>
		    DUE DATE: {{ doc.get_formatted("due_date") or '' }}<br>
		 PREPARED BY :<b>{{ u.first_name }} &nbsp; {{ u.last_name }}</b><br>
<table class="table table-bordered">
			<th>Item Code</th>
			<th class="text-right">Qty</th>
			<th class="text-right">Rate</th>
			<th class="text-right">Amount</th>
		{%- for row in doc.items -%}
			<td style="width: 3%;">{{ row.idx }}</td>
			<td style="width: 20%;">
				{{ row.item_code}}
			<td style="width: 37%;">
				<div style="border: 0px;">{{ row.description }}</div></td>
			<td style="width: 10%; text-align: right;">{{ row.qty }} {{ row.uom or row.stock_uom }}</td>
			<td style="width: 15%; text-align: right;">{{
				row.get_formatted("rate", doc) }}</td>
			<td style="width: 15%; text-align: right;">{{row.get_formatted("amount",doc) }}</td>
		{%- endfor -%}
        <TD colspan=4 rowspan=3>
            Amount in Words<br>
            <B>{{ doc.base_in_words }}</B>
	<TD WIDTH=15% align=right><B>SUB TOTAL</B></TD>
	<TD WIDTH=15% align=right><B>{{ doc.get_formatted("total", doc) }}</B></TD>
	<TD align=right><B>VAT</B></TD>
	<TD WIDTH=20% align=right><B>{{ doc.get_formatted("total_taxes_and_charges", doc) }}</B></td>
	<td align=right><b>TOTAL</b></td>
	<td WIDTH=15% align=right><B>{{ doc.get_formatted("grand_total", doc) }}</b></td>
<table class="table table-bordered">
       <th>Sales Person</th>
        <th width=25%>Invoice Amount</th>
        <th>Total Outstanding</th>
        <th>Loyalty Point</th>
              <td>{%- for row in doc.sales_team -%}
{{ row.sales_person }}<br>
{%- endfor -%}</td>
           <td>{{ doc.get_formatted("grand_total", doc) }}</td>
           <td>{{doc.loyalty_points}} {{doc.loyalty_amount}}{{doc.redeem_loyalty_points}}</td>
<table class="table table-bordered">
            	<th>Terms and Conditions</th>
              <tr><td>{{ doc.terms or '' }}</td></tr>      	

Loyalty points are calculated on submit of invoice. You need to find way of getting data after submitting.

I am Having trouble with something like this myself, Can you post your css?

Hellow My css is as below… Am wondering if there is way of getting fields from loyalty points table into the sales invoice…This whole code is an improvement of a print format shared by @trentmu

.table table-bordered{
border: 1px solid #000000;
 /* My personal syle abbreviations to use minimal characters while coding the HTML portions*/
   /* .bd0 {   border: 1px dotted red;   padding: 0px;} */
   .lf  {   text-align: left;}
   .cn  {   text-align: center;}
   .rt  {   text-align: right;}
   .vt  {   vertical-align: top;}
   .vc  {   vertical-align: middle;}
   .vb  {   vertical-align: bottom;}
   .xs  {   font-size: x-small;}
   .sm  {   font-size: small;}
   .md  {   font-size: medium;}
   .lg  {   font-size: large;}
   .xl  {   font-size: x-large;}
   .xxl {   font-size: xx-large;}
   .b   {   font-weight: bold;}
   .bb  {   font-weight: bolder;}
   .i   {   font-style: italic;}
   .u   {   text-decoration: underline;}
   .bd0 {   border: 1px dashed;}
   .bd1 {   border: 1px dotted;}
   .bd2 {   border: 1px solid;}
   .blk {   color: black;}
   .lgr {   color: lightgrey;}
   .lblu{   color: lightblue;}
   .blu {   color: blue;}
   .grn {   color: green;}
   .red {   color: red;}
   .ylw {   color: yellow;}
   .org {   color: orange;}
   .ord {   color: orangered;}
   .prp {   color: purple;}
   .lim {   color: lime;}
   .bg0 {   background-color: white; }
   .bg1 {   background-color: #dcdcdc; }
   .bg2 {   background-color: #c9adce; }
   .spacer {   color: white;   font-weight: bold;}
   .print-format table, .print-format tr, 
   .print-format td, .print-format div, .print-format p {
      vertical-align: middle;
      font-family: Helvetica, Arial, sans-serif;
      -webkit-font-smoothing: antialiased;
      -moz-osx-font-smoothing: grayscale;
      padding: 0px;
      z-index: 10;
   @media screen {
      .print-format {
         padding: 10mm;
   @media print {
      #footer {
         position: fixed;
         bottom: 0;
1 Like

Have you tried something like this

{%- frappe.db.get_value("Customer", doc.customer, "loyalty_amount") -%}

It tried it and popped the error below;

I also tried the below and the results were blank
{%- for row in doc.loyalty_point_entry -%}
{{ row.loyalty_points }}

{%- endfor -%}

Sorry my mistake it should be like this

{{ frappe.db.get_value("Customer", doc.customer, "loyalty_points") }}

although it still won’t work since i showed you that as an example as of another way to get the value you need.
Since the results were blank but the invoice has those values i can’t think of a reason why

Hi, thanks for this code, but there exists no loyalty_points filed in the tabCustomer.So it threw an error of missing column.