You can include Merge Fields in a Template for Excel to bring in content from your practice management software and from FYI. These are added to the Excel spreadsheet that will be used as the template, for example, to include the "Client Name", "Manager", etc.
Merge Fields can be included either using:
- The Excel Names function, which is the recommended way. Refer to Including Merge Names in Excel Templates.
- As Merge Fields, via the Email Template Editor, as described below. Using Merge Fields via the Email Template Editor will be phased out from FYI Spreadsheet Templates in the near future.
Templates can only be set up and maintained by an FYI Admin, or a user in a User Group that has Permissions enabled for Templates (refer to Managing User Groups).
Note: Merge Fields cannot be used in spreadsheets that contain macros (.xlsm).
Enable Merge Fields in the Spreadsheet Template
To include Merge Fields in a Template for an Excel Spreadsheet, you must enable Merge Fields in the Template.
- When creating the Spreadsheet Template in FYI, enable Merge Fields in the Filing section of the template.
Adding the Merge Fields to the Excel File
There is no way to directly add merge fields to external sources in Excel. You need to copy Merge Fields from the FYI Email Template function and paste these into the Excel file. Refer to Creating Email Templates and Signatures and Including Merge Fields in Email Templates and Signatures.
- In FYI, create the Spreadsheet Template in the usual way. Refer to Creating Templates for Word, Spreadsheet or Presentation.
- Edit the contents of the template. This can be done using Excel via the browser or the desktop version of Excel.
- Add the Merge Fields to the Spreadsheet template as follows:
In a separate browser, open Knowledge - Templates, from the Create Template drop-down select Email. - In the Merge Fields section of the Email Template, click to select a Merge Field you want to include in the Spreadsheet template.
The Merge Field is added to the body of the Email Template. - Select and copy the code for the Merge Field (Ctrl+c).
Make sure you include the curly brackets.
Make sure there is a space after the opening curly brackets and before the closing curly brackets. - In the Excel file, paste the code (Ctrl+v).
- Repeat for all the Merge Fields you want to include. You can apply formatting and include other text as required.
Note: There is a known issue where the values are not currently populated for the Merge Fields for Manager Phone, Manager Mobile, Partner Phone and Partner Mobile.
Enable Merge Fields in the Spreadsheet Template
When creating the Spreadsheet Template in FYI, enable Merge Fields in the Filing section of the template.
Today's Date
You can add 'today's date' to a spreadsheet using the FYI Merge Field Current Date or via an Excel template using the Excel TODAY function.
- Add the FYI Created Date Merge Field as above.
Using the FYI CreatedDate Merge Field, adds the current 'today's date' when a document created from the template and this original creation date is retained whenever the document is opened or downloaded. - Excel TODAY function.
Using the Excel =TODAY() function will add 'today's date' when the spreadsheet is created from the template, but it will reset the date to the current date whenever the spreadsheet is opened or downloaded.
