Adding Formulas and IF Statements to a Word 2016 Document Template

Adding Formulas and IF Statements to a Word 2016 Document Template

Adding a Formula to a Word 2016 Document Template


With Word 2016 and higher, a new technique is required to add data-driven formula fields to ASPIRE Document Templates.


  1. Start by creating a new Document Template (Administration menu - Document Templates screen), Name it and select a Document Type.
  2. Click the Create/Edit Template button to launch a new blank document in Word and the Data Tag Selection dialog window.






Create a Simple Formula

Here are the steps to create a simple formula, one that adds two currency fields together.

1.   Select the Contract data view from the left pane, and then the Amount Financed field from the right.
2.   Click the Insert Tag button. 
      Note: Don’t add any modifiers or formatters.















  1. Type a plus sign (+) after the first field’s closing </LTF> tag with spaces before and after. 
  2. Then Insert the second field from the Data Tag Selection window.  

In this example, Contract.Total of Payments is chosen. 













  1. When the second field is inserted into the document, highlight to select the entire formula, right-click and select Copy.














  1. While still in the original document template after copying the formula, backspace through what was highlighted and copied and delete that content. 


  1. Then, from the Word Insert menu:

  1. Click Quick Parts – Field - Formula.
  1. Paste the copied formula (including a Space after the first “=” sign), and select the currency (or other numeric) formatter from the dropdown. 
  2. Then click the OK button.





  1. The formula in the document should now look similar to this:





  1. Click the Word Save button and close the document. 
  2. Back in ASPIRE on the Documents Template screen, highlight the newly created template in the grid and click the Preview Template with Word button. 
  3. Supply a valid Contract/Transaction ID on the ensuing pop-up, click the OK button on that dialog, and the new document will open. 
  4. Verify that the formula is properly calculated and formatted in the document. 

Adding an IF Statement to a Word 2016 Document Template in ASPIRE

There are known limitations to this work around.

  1. You are unable to have one of the IF/ELSE results be blank by utilizing “”
  2. You are unable to have an spaces in the IF/ELSE results – so phrases or multiple words inside the results need to be done with multiple IF statements if possible
  3. It’s possible and/or likely that not all of the modifiers inside a create LT doc tag will work within the IF Statement.  For sure, do not use the formatting modifiers for Currency, Date, etc.  Those need to be done using or formatting guide that is at the end of this.  
  4. If none of these work, then we will need to Open a ticket to create a custom View to handle the statement within the customer’s database.




Create an IF Statement

The statement is going to be “If my amount financed is $10,000, display the Commencement Date, if it’s not $10,000, display the First Payment Date”.  

  1. In the Word document, pull in all of the fields that you are going to want for your formula.  

This example will use Amount Financed, Acceptance Date, First Payment Date. 

List of my document tags I want to use:

Amount Financed<LTF>DocGenConContract.Amount Financed</LTF>

Acceptance Date: <LTF>DocGenConContract.Acceptance Date</LTF>

First Payment Date: <LTF>DocGenConContract.1st Payment Date</LTF> 


  1. Then the structure of the formula is built using those fields.



  1. Doing a Preview of the doc will validate the fields are pulling in correctly.









  1. Now that the data is valid, the formula can be built. 
  2. Close the Preview and back on the ASPIRE document template, copy the formula that has been created so far, and then go to the Word Insert menu - Quick Parts - Field.  
  3. Choose If from the Field names: list on the left, and then paste the formula into the Field codes: text box after the IF. 
  4. Click OK and the formula will be created in the document. (It will look like a single field until the next step.) 
  5. Right-click on that field, and click “Toggle Field Codes” to display the actual formula.  
  6. Then highlight the formula INSIDE the brackets and copy it (highlighted in grey below). 









  1. Open Notepad and Paste the formula into Notepad.  




  1. After the formula has been pasted, modifiers can be added. Modifiers are not required but this step is necessary whether modifiers are added or not.  
  2. Delete the \*MERGEFORMAT and add the modifier. If not adding a modifier, leave the \*MERGEFORMAT in place.
  3. This example will use the “Long Date” format.  

  1. Then Select All in Notepad to capture the entire formula and Copy it again.
  2. Then go back into the Word document and Right click on your Statement and select “Edit Field”.  
  3. Delete everything after the IF in the Field Codes text box and paste in the updated formula.  
  4. Save the doc, then go back and preview:





Below is a list of modifiers.

The standard ASPIRE field tag modifiers do not work within formulas. The Word Number Format switch  ( \# ) must be used to format number results, including percentages and currency. To use the switch, follow the \# with a space, a quotation mark, the format for the numbers and a closing quotation mark 

Number and Currency Formats


Result 



\# "$#,##0.00" - Currency 

Example: { =<LTF>DocGenConContract.Equipment Original Cost</LTF> - <LTF>DocGenConContract.Finance Charge</LTF>\# "$#,##0.00")}

Result: $717,312.44




\# "$#,##0.00;($#,##0.00) - Currency with negative numbers displayed within parenthesis

Example: { =<LTF>DocGenConContract.Finance Charge</LTF> - <LTF>DocGenConContract.Equipment Original Cost</LTF>\# "$#,##0.00;($#,##0.00")}

Result: ($45,983.12)

\# "$#,##0.00;-$#,##0.00 - Currency with negative numbers displayed after a negative sign

Example: { =<LTF>DocGenConContract.Finance Charge</LTF> - <LTF>DocGenConContract.Equipment Original Cost</LTF>\# "$#,##0.00;-$#,##0.00"}

Result: -$45,983.12


Percentage formats the results with a percentage sign. The presence of a % character in a format string causes a number to be multiplied by 100 before it is formatted.

Be aware of the format of the fields referenced. If the values are already in a percentage format. (E.g. 0 Yield vs Yield %. Without formatting, Yield would display as .98 versus Yield % as 98.) The Percent modifier converts the decimal to a percentage display and uses the # and 0 the same as Number and Currency formatting.

 

    Percentage Formats 


Result 



Formatting Formula Dates 

The Format as Short Date and Format as Long Date tags do not work when the displayed date is the result of a formula. You will need to use the Word Date Format switch \@ . 

Formula Example: { IF "<LTF>DocGenConContract.Start Date</LTF>" <> "" "<LTF>DocGenConContract.Start Date</LTF>" "" \@ "MMMM dd,yyyy"}

Result: March 03, 2008

Options: 

\@ "dddd, MMMM dd, yyyy" = Displays as - Tuesday, October 21, 2008 

\@ "MMMM d, yyyy" = Displays as - October 21, 2008 

\@ "d MMMM yyyy" = Displays as - 21 October 2008 

\@ "M/d/yyyy h:mm tt" = Displays as - 10/21/2008 10:19 AM 


d - Represents the day of the month as a number from 1 through 31. A single-digit day is formatted without a leading zero.

dd - Represents the day of the month as a number from 01 through 31. A single-digit day is formatted with a leading zero.

ddd - Represents the abbreviated name of the day of the week.

dddd - Represents the full name of the day of the week.

M - Represents the month as a number from 1 through 12. A single-digit month is formatted without a leading zero.

MM - Represents the month as a number from 01 through 12. A single-digit month is formatted with a leading zero.

MMM - Represents the abbreviated name of the month.

MMMM - Represents the full name of the month.

y - Represents the year as at most a two-digit number. If the year has more than two digits, only the two low-order digits appear in the result. If the year has fewer than two digits, the number is formatted without a leading zero.

yy - Represents the year as a two-digit number. If the year has more than two digits, only the two low-order digits appear in the result. If the year has fewer than two digits, the number is padded with leading zeroes to achieve two digits.

yyyy - Represents the year as a four-digit number. 

h - Represents the hour as a number from 1 through 12. A single-digit hour is formatted without a leading zero.

hh - Represents the hour as a number from 01 through 12. A single-digit hour is formatted with a leading zero.

H - Represents the hour as a number from 0 through 23. A single-digit hour is formatted without a leading zero.

HH - Represents the hour as a number from 00 through 23. A single-digit hour is formatted with a leading zero.

m - Represents the minute as a number from 0 through 59. A single-digit minute is formatted without a leading zero.

mm - Represents the minute as a number from 00 through 59. A single-digit minute is formatted with a leading zero.

t - Represents the first character of the AM/PM designator.

tt - Represents the AM/PM designator. 







    • Related Articles

    • Doc Template Repair Utility

      ADO 22113 A Document Template repair utility has been added to the Administration menu – Document Templates screen that repairs any fragmented or broken document tags.   This repair utility will repair all existing document templates and only needs ...
    • Adding and Booking a Contract in ASPIRE

      See the attached document for complete details. The purpose of the attached How to Guide is to show a generic example of how one can go about adding a contract to ASPIRE, and subsequently how to book that contract. Note: This document outlines only ...
    • DocuSign - Signer unable to sign/initial the document

      When a signer is unable to successfully activate a DocuSign action (sign, initial, etc.) on a document that originated as an ASPIRE document template, verify whether the action is configured using an ASPIRE DocuSign data tag in the header or footer ...
    • Contract Modification - Adding Equipment

      Midterm Contract Modification – Asset Addition A typical midterm contract modification would be required in the event a customer would like to add accessories or additional equipment to the lease. Before you begin any modification, you need to ...
    • Scheduled Billing - Invoice Generation and Regeneration

      See the attached document for complete information. The attached document will provide an overview of the steps a user, with no security restrictions, will take in order to generate scheduled invoices (billings) within ASPIRE.  The concept of a ...