Happy Business Starts Here

Guru

Invoice and Quote Template Currency Formatting

Wanted to share with the community the recent enhancements we made to our quote and invoice templates for Zuora.

 

Ever since we first implemented Zuora, we have attempted to maintain "one-template-to-rule-them-all". We did not want the hassle or the inconsistencies of managing 3 - 4 templates for the various currencies, regions, or other variations we needed to support.

 

Our first major need for segmenting the formatting came from multi currency and multi entity needs. (4 currencies and 7 subsidiaries to be exact)

 

First lets take a look at currencies. Out of the box on invoices, USD will show as $120.00 and other currencies like GBP will show as GBP120.00

We, however, wanted to use the symbols like "£". So our first version of the templates had the following:

 

{IF {MERGEFIELD Account.Currency} = USD “{MERGEFIELD Invoice.Total \# $,0.00}” “”}{IF {MERGEFIELD Account.Currency} = GBP “{MERGEFIELD Invoice.Total \# £,0.00}” “”}

 

Now for 4 currencies, we had to repeat that 4 times for every place in the template where we had numbers. This was less than ideal since it bloated the template up to about 14 pages when viewed with all markup. But it worked...

 

After some experimenting, however, we came upon a new solution. We would use bookmarks (aka variables in word field speak) to store the format...

 

At the top of our document we added the following initialization code:

 

Initialize Variables:

{ SET CurrencyFormat__c “$,0.00” }

 

{IF {MERGEFIELD Account.Currency} = GBP “

{ SET CurrencyFormat__c “£,0.00” }

“}

{IF {MERGEFIELD Account.Currency} = EUR “

{ SET CurrencyFormat__c “€,0.00” }

“}

 

 You can then highlight the entire block of text and then right click and go to "Font" and make it hidden so your comments and extraneous markup wont make it into the final output.

 

Finally i replaced all of my currency fields in the template with the following:

 

{ = { MERGEFIELD Invoice.Total \# 0.00 } \# { CurrencyFormat__c } }

 

So to break this down a bit... This actually does 4 steps to make it work with the word template generator.

 

1. It formatas the currency field (Invoice.Total) to a standard decimal. This removes the $ or GBP prefix from the number.

2. It places it into a formula field {=}. When rendered this will come out to { = 120.00 }

3. It applies formatting to the formula field. This formatting will apply to the output of the formula (which in this case is a static number) It gets that formatting string from the bookmark/variable we initialized at the begining. So the final formula for GBP looks like { = 120.00 \# “£,0.00”}

4. Word then applies formatting and you get: “£120.00

 

Side Note:

I initially attempted to do this using:

{MERGEFIELD Invoice.Total \# { CurrencyFormat__c } }

However, this will not work due to the way Zuora processes the MERGEFIELD field. It will not have access to the {CurrencyFormat__c} bookmark.

 

 

This can be expanded in a number of ways for other aspects of the template. For example:

1. Subsidiaries / Entities - We added a variable to store the subsidiary from the Zuora custom field "Account.Subsidiary__c" and use that to define what mailing address and remittance bank accounts to show on the invoice or quote.

2. Tax Names - Depending on the locality (i.e. Europe vs USA vs Australia) there are different names for "Tax". We have a variable we calculate at the top based on a handful of rules called "TaxName__c" then anywhere the word Tax appeared in the template, we replaced it with { TaxName__c }

So now the invoice will dynamically shift to show "Tax" in the US, "VAT" in Europe, and "GST" for Australia, and so on...

3. Variable Abstraction -  This is mainly future proofing the template. Rather than refer directly to Zuora custom fields in the template we tend to use a variable as an abstraction. (e.g. Instead of { MERGEFIELD Quote.Currency } we initialize { SET Currency__c { MERGEFIELD Quote.Currency } } and then reference { QuoteCurrency__c } throughout the document (i.e. for IF statements or other formatting). This allows us to easily redefine the meaning of that variable at any time without finding all its occurrences in the document.

 

There is one enhancement we have asked Zuora for to allow repeated tables in the Invoice template. This would allow you to format an entire table differently based on conditions (i.e. IF TaxType == VAT, show this formatted charge table ELSE show this other one). For more information or if you think this would be useful, please upvote the open Idea: http://community.zuora.com/t5/Billing-Payment-Ideas/Allow-repeated-tables-in-invoice-template/idi-p/...

 

Hope all this helps someone in their efforts to reduce the number of templates needed in the system. So far these tricks have allowed us to maintain exactly 1 quote template and exactly 1 invoice template for all customers.

 

If you have any questions (or ideas on how to improve this or other aspects of the template) please let me know.

 

Cheers!

-Jacob

4 REPLIES 4
Highlighted
Community Manager

Re: Invoice and Quote Template Currency Formatting

Thanks for sharing this with the Community, Jacob @feisley


Lana Lee | Senior Community Manager and Strategist
"A little consideration, a little thought for others, makes all the difference." —A. A. Milne
New Student

Re: Invoice and Quote Template Currency Formatting

 

Re: Invoice and Quote Template Currency Formatting

Hello,

 

I tried this solution you suggested, but, I could not get this solution. Can you please tell me where I might have missed the point?

Attached in this question: https://support.zuora.com/hc/en-us/requests/138861 is the word document for the Invoice Template. PDF attached is the Invoice generated.

 

Please, could you review it once?

 

Thank you for your time!

 

Regards,

Ramya

 

 

Support SME

Re: Invoice and Quote Template Currency Formatting

Hi @rullagaddimath, Not all the community users will have access to the support Ticket 138861 that you have with Zuora on this.

Can you please provide the detail here documenting what are not working.

Also, since you have already have the support Ticket open, can you also please update that if the suggestion provided to you last Wednesday did not work.

 

Thank you
Doyeli






If you found my answer helpful, please give me a kudo ↑
Help others find answers faster by accepting my post as a solution √