Happy Business Starts Here

Enable Nested IFs on Invoice Templates

I have been customizing our Company's invoice template and would like certain information presented to be more informative. In some cases, nested IF functionality (in combination with merge fields) would permit me flexibility in designing templates that can display certain information given multiple conditions. It is occasionally difficult to design a template given limitations such as being forced to use a table only once, or not having the ability to display certain text provided multiple conditions are met. In one instance, I was attempted to display a transaction date only when the transaction type was refund or payment. Had I had nested IF functionality, I could have easily achieved this but due to Zuora's limited support of merge fields, conditional operators, and other conditional logic parameters, I was unable to accomplish this. I worked with Zuora support on this but ultimately pushed aside this enhancement due to this constraint. I am therefore supporting Zuora's functionality of nested IFs and other advanced conditional logical setups that users would benefit from to ultimately deliver an invoice that is informative for its clients.

8 Comments
Zuora Alumni

Hi,

 

We are currently working on some new capabilities for the invoice template system. In particular, we have added enhanced support for nested tables in the usage table (with table-nesting in other invoice tables on the way.) I would be interested in your feedback on this feature when we release it at the end of February. Nested conditional statements are a good idea. I will consider this for inclusion in a future set of template enhancements. Thanks for the suggestion!

 

Nick

Zuora Alumni
Status changed to: More Feedback Needed
 
Community Manager
Status changed to: Under Consideration
 
Guru

@nikkisanchez not 100% sure what you mean by nested-if but I am assuming the following:

 

{IF {MERGEFIELD Account.Currency} = USD {IF {MERGEFIELD Account.AutoPay} = "true" "Show this when USD + Autopay" ""} ""}

 

There are also some clever ways to do AND and ORs on fields using word field calculations:

 

AND example:

 

{IF { = ({IF {MERGEFIELD Account.Currency} = USD 1 0 }  + {IF {MERGEFIELD Account.Currency} = USD 1 0 }) } = 2 "True" "False"}

this works by summing the results and if they sum to 2 (i.e. all two conditions are true, then and is satisfied). If you added a third comparison it would be = 3 and so on. It may seem a tad convoluted, but it scales better then the first one where you end up with the cascade of "false" entries  "" } "" } "" } "" } as you close all the nested fields.

 

OR can be implemented in a very similar manner, all you change is the end comparison and make sure its >= 1

 

There are a ton of useful resources here:

http://www.gmayor.com/formatting_word_fields.htm

 

And the official Word field documentation is here:

https://support.office.com/en-us/article/Field-codes-MergeField-field-7a6d24a1-68a6-4b05-8359-1dc087...

EDIT: This is the proper URL: https://support.office.com/en-us/article/List-of-field-codes-in-Word-1ad6d91a-55a7-4a8d-b535-cf78886...

 

The big limitation you mentioned is the inability to repeat a table. We want to use the same table twice to allow some region specific formatting (i.e. EU has a table for VAT) vs other places show normall "Tax" table. Mostly cosmetic formatting but to the finance and compliance people its very important. @nharlow I am going to open a feature request specificly for allowing repeated tables in invoice templates. The quote template engine allows it without issue so it appears to be an artifically imposed limitation rather than a technical one (other than perhaps to help users catch errors, but in this case it should be a warning not an error)

Guru
Zuora Alumni

@feisley - Thanks for the details and reference links on nested conditional, very interesting and useful stuff. I'll consider the repetition of tables in the invoice for a future release. Thanks.

 

Nick

Scholar ct
Scholar

I'm not sure if Zuora changed their templating engine since the comments in 2016, but I can't get any type of nested IFs to work.  Support has also told me that nested IFs aren't supported.  Just wanted to add a recent comment and vote for this idea- since invoice templates are set at the Account level we need more flexibility inside the templates to make them work for Accounts with multiple product line subscriptions.

Scholar ct
Scholar

Ok, please disregard my above comment, but I'll leave it there so that people can see what solved my problem.  It's been a long few days trying to get one template to work for our complicated subscription structure, but it appears to be working now.

 

Background:

 

I initially contacted supported when my nested IFs weren't working.  The template was saving into the Billing Document section, but when I would generate an invoice PDF it wouldn't create the PDF yet it wouldn't give any error.  There just wouldn't be a PDF attached to the Invoice record.  I contacted support about it and, after escalating to have their engineers look at it, they told me that nested IFs were not supported and I could create an idea for this feature.  That's when I found this page as well as some others that showed different ways that nested IFs were sometimes supported in Word merge templates.  (I'm sorry, I don't have a copy of the markup I was using that caused this bug... i've since saved over it and don't know how to replicate it)

 

I then attempted to use an IF formula inside of an IF merge field, based on what I had read on other sites.  Here is what I was trying to do:

{ IF "{ MERGEFIELD InvoiceItem_1.RatePlanName \* MERGEFORMAT }" = "Marketplace Leads" "" "{ =({ IF "{ MERGEFIELD InvoiceItem_1.Name \* MERGEFORMAT }" = "Volume charges - SDP*" "{ MERGEFIELD InvoiceItem_1.Quantity \#$#,##0.00 MERGEFORMAT }" "{ MERGEFIELD InvoiceItem_1.Quantity \# # MERGEFORMAT }" }) }" }

 

SOLUTION - Correct Nested IF Merge Field:

 

{ IF "{ MERGEFIELD InvoiceItem_1.RatePlanName \* MERGEFORMAT }" = "Marketplace Leads" "" "{ IF "{ MERGEFIELD InvoiceItem_1.Name \* MERGEFORMAT }" = "Volume charges - SDP*" "{ MERGEFIELD InvoiceItem_1.Quantity \#$#,##0.00 MERGEFORMAT }" "{ MERGEFIELD InvoiceItem_1.Quantity \# # MERGEFORMAT }" }" }