Total by Item Group - Print Format for Quotation

taljishi · June 27, 2013, 10:43am

I created a new Print Format for a Quotation/Estimate that includes the Item Group name instead of the Product Name or Description. We use this to provide a summary to our clients in a proposal format.

I have setup the system so that each Item is part of a child Item Group. For example, Bose Home Theater Speaker are classified as Speakers (a child of Multimedia). In some cases, there’s one more level. With the the current modified Quotation/Estimate, I’m able to show child in the Item Group, such as Speakers in the previous example.

However, I would like to have the total of each main Item Group (Multimedia in my above example) shown in the Estimate.

Is that possible? Or do you need more details to clarify my question.

Thank you.

–

You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

system · June 27, 2013, 10:54am

Hi Taljishi,

Are you looking for a single common ancestor Item Group of all Items in that Quotation?

Can you give an example with more than one Item in the Quotation? Just one line item doesn't give a complete idea of the possibilities.

Thanks,

Anand.

On 27-Jun-2013, at 4:13 PM, taljishi <ta...@gmail.com> wrote:

I created a new Print Format for a Quotation/Estimate that includes the Item Group name instead of the Product Name or Description. We use this to provide a summary to our clients in a proposal format.

I have setup the system so that each Item is part of a child Item Group. For example, Bose Home Theater Speaker are classified as Speakers (a child of Multimedia). In some cases, there's one more level. With the the current modified Quotation/Estimate, I'm able to show child in the Item Group, such as Speakers in the previous example.

However, I would like to have the total of each main Item Group (Multimedia in my above example) shown in the Estimate.

Is that possible? Or do you need more details to clarify my question.

Thank you.

–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

–

You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

taljishi · June 28, 2013, 10:15am

Yes more details are needed for my example.

Bose Home Theater Speaker - Speakers (Multimedia > Speakers) - $180
Monster HDMI Ultimate High Speed with Ethernet - HDMI Cables (Multimedia > AV Cables > HDMI Cables) - $20
Bose Outdoor Speakers - Speakers -$150
Monster Speaker Cable 1000m - Speaker Cables - $140
Logitech Univeral Remote Control - Univeral Remote (Control > Remote Control > Univeral Remote) - $60
Denon AV Receiver - AV Receivers (Multimedia > AV Receivers) - $150

My current modified Quotation will display the following:

1 Speakers $180
2 HDMI Cables $20
3 Speakers $150
4 Speaker Cables $140
5 Universal Remote $60
6 AV Receivers $150
Total $700

I would like to have the Quotation to display the following:

1 Multimedia $640
2 Control $60
Total $700

Or:

1 Speakers $330
2 HDMI Cables $20
3 Speaker Cables $140
4 Universal Remote $60
5 AV Receivers $150
Total $700

Hope that clarified. Thank you in advance.

–
You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

taljishi · June 28, 2013, 10:22am

I made a mistake in my second example. It should be as follows:

1 Speakers $330
2 AV Cables $160

3 Remote Controls $60
4 AV Receivers $150
Total $700

–

You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

system · June 28, 2013, 10:30am

Hi Taljishi,

You will need to write custom server script which stores the value of required ancestral Item Group for each Item in a Custom Field of the Item table of Quotation.

Then, you can calculate totals in the Print Format directly.

If you need help with the code, please post your question on the developer forum.

Thanks,

Anand.

On 28-Jun-2013, at 3:52 PM, taljishi <ta...@gmail.com> wrote:

I made a mistake in my second example. It should be as follows:

1 Speakers $330
2 AV Cables $160
3 Remote Controls $60
4 AV Receivers $150
Total $700

–

You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un…@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

–

You received this message because you are subscribed to the Google Groups "ERPNext User's Forum" group.

To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.

taljishi · June 29, 2013, 9:30am

I’ll give it a try. And if I need help I’ll post in the developers forum.

Thank you Anand.

–
You received this message because you are subscribed to the Google Groups “ERPNext User’s Forum” group.
To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-user-forum+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

uma13 · April 15, 2016, 9:01am

Hi,

I have a similar request. I need to get totals of item-groups, rather than items on the sales invoice. Though, I have created a new print format for the same and I have the item-group field showing in the Sales Invoice Items file, I am not sure how to break and total on the item groups. Can you please help in this regard?

Inline image 1

Instead of showing the Item Group so many times, I would like a sub-total of the item-groups, which will show Consumable on one line and Services on the next line, so that my invoice will get shortened. My code is as follows:

The code for the same is:

<style>
	.print-format table, .print-format tr, 
	.print-format td, .print-format div, .print-format p {
		font-family: Times;
		line-height: 100%;
		vertical-align: middle;
	}
	@media screen {
		.print-format {
			width: 8in;
			padding: 0.25in;
			min-height: 8in;
		}
	}
</style>

<p class="text-center">
	{{ doc.company }}<br>
	{{ doc.select_print_heading or _("Invoice") }}<br>
</p>
<p>
	<b>{{ _("Invoice No") }}:</b> {{ doc.name }}<br>
	<b>{{ _("Date") }}:</b> {{ doc.get_formatted("posting_date") }}<br>
	<b>{{ _("Customer") }}:</b> {{ doc.customer_name }}
</p>

<hr>
<table class="table table-condensed cart no-border">
	<thead>
		<tr>
			<th width="30%">{{ _("Item Group") }}</b></th>
			<th width="20%" class="text-right">{{ _("Qty") }}</th>
                        <th width="20%" class="text-right">{{ _("Rate") }}</th>
			<th width="30%" class="text-right">{{ _("Amount") }}</th>
		</tr>
	</thead>
	<tbody>
		{%- for item in doc.items -%}
		<tr>
			<td>
				{{ item.item_group }}
                                 
			</td>
			<td class="text-right">{{ item.qty }} </td>
                        <td class="text-right">@ {{ item.get_formatted("rate") }}</td>
			<td class="text-right">{{ item.get_formatted("amount") }}</td>
		</tr>
		{%- endfor -%}
	</tbody>
</table>
<table class="table table-condensed no-border">
	<tbody>
		<tr>
			<td class="text-right" style="width: 70%">
				{{ _("Net Total") }}
			</td>
			<td class="text-right">
				{{ doc.get_formatted("net_total") }}
			</td>
		</tr>
		{%- for row in doc.taxes -%}
		{%- if not row.included_in_print_rate -%}
		<tr>
			<td class="text-right" style="width: 70%">
				{{ row.description }}
			</td>
			<td class="text-right">
				{{ row.get_formatted("tax_amount", doc) }}
			</td>
		</tr>
		{%- endif -%}
		{%- endfor -%}
		{%- if doc.discount_amount -%}
		<tr>
			<td class="text-right" style="width: 75%">
				{{ _("Discount") }}
			</td>
			<td class="text-right">
				{{ doc.get_formatted("discount_amount") }}
			</td>
		</tr>
		{%- endif -%}
		<tr>
			<td class="text-right" style="width: 75%">
				<b>{{ _("Grand Total") }}</b>
			</td>
			<td class="text-right">
				{{ doc.get_formatted("grand_total") }}
			</td>
		</tr>
	</tbody>
</table>
{% if doc.get("taxes", filters={"included_in_print_rate": 1}) %}
<hr>
<p><b>Taxes Included:</b></p>
<table class="table table-condensed no-border">
	<tbody>
		{%- for row in doc.taxes -%}
		{%- if row.included_in_print_rate -%}
		<tr>
			<td class="text-right" style="width: 70%">
				{{ row.description }}
			</td>
			<td class="text-right">
				{{ row.get_formatted("tax_amount_after_discount_amount", doc) }}
			</td>
		<tr>
		{%- endif -%}
		{%- endfor -%}
	</tbody>
</table>
{%- endif -%}
<hr>
<p>{{ doc.terms or "" }}</p>

Thanks
Uma

rmehta · April 16, 2016, 6:39am

@uma13 can you show a mockup of what you are trying to achieve?

Tip: Also format your code by adding enclosing it in 3 back-ticks: ``` - see how I edited your previous post.

Suggestion: you can add custom fields in your item table to calculate the sub-totals and print them in Jinja. templates are not designed for calculating stuff.

uma13 · April 16, 2016, 10:05am

Thanks for the reply, Rushabh. Also, thanks for the tip on how to format the code. Will keep that in mind for the next time.

Actually, I can add custom field and create custom-scripts for calculating the sub-totals in the Items table but the challenge would come when the number of item groups are not fixed. I will have to keep creating more custom fields when the new item groups are added.

Anyways, will try it out as a temporary solution and see how it goes.

Thanks
Uma

vladucoju · November 29, 2016, 9:33pm

Hello @uma13 ,
i use your example but he give me error

Could you help me with this?

UmaG · November 30, 2016, 3:56am

Hi @vladucoju,

The code that you had used was just a part of the entire code and it was not working that time. I created my report and it is working fine but am not able to attach the code here. Can you give me your email ID or something where I can send you the code in a Word format?

Thanks
Uma

gio-avk · March 2, 2017, 9:58am

@UmaG did you menage to do this without modifying files on the server? I’m a bit confused on how to integrate calculation done in a custom script to a print format… and if you are referring to server custom scripts or client custom scripts.

UmaG · March 2, 2017, 10:45am

Hi @gio-avk,

Please send me your email address so that i can share the code with you. I am not able to copy the jinja template over here.

Regards
Uma

gio-avk · March 2, 2017, 2:51pm

Thanks for the code Uma, this is the error I get when trying to use the print format I’ve created using your code, maybe someone can help me understand why it’s not working.

Traceback (most recent call last):
File “/home/frappe/benches/bench-2017-02-27/apps/frappe/frappe/app.py”, line 55, in application
response = frappe.handler.handle()
File “/home/frappe/benches/bench-2017-02-27/apps/frappe/frappe/handler.py”, line 19, in handle
execute_cmd(cmd)
File “/home/frappe/benches/bench-2017-02-27/apps/frappe/frappe/handler.py”, line 40, in execute_cmd
ret = frappe.call(method, **frappe.form_dict)
File “/home/frappe/benches/bench-2017-02-27/apps/frappe/frappe/init.py”, line 897, in call
return fn(*args, **newargs)
File “/home/frappe/benches/bench-2017-02-27/apps/frappe/frappe/www/print.py”, line 181, in get_html_and_style
no_letterhead=no_letterhead, trigger_print=trigger_print),
File “/home/frappe/benches/bench-2017-02-27/apps/frappe/frappe/www/print.py”, line 152, in get_html
html = template.render(args, filters={“len”: len})
File “/home/frappe/benches/bench-2017-02-27/env/lib/python2.7/site-packages/jinja2/environment.py”, line 1008, in render
return self.environment.handle_exception(exc_info, True)
File “/home/frappe/benches/bench-2017-02-27/env/lib/python2.7/site-packages/jinja2/environment.py”, line 780, in handle_exception
reraise(exc_type, exc_value, tb)
File "