Merge Fields can be used to insert data from FYI or your practice management software into an Excel Template, making it efficient to repeatedly create spreadsheets for your customers using a standard layout, for example, Workpapers.
Compared to Email Templates, there is no direct way to insert a Merge Field into an Excel Spreadsheet.
Merge Fields can be included either using:
- The Excel Names function (recommended). This method is covered in the article below.
- As Merge Fields, via the Email Template Editor. Refer to Including Merge Fields in Excel Templates.
Creating the Excel Template with Merge Fields
Step 1 - Create a List of Merge Field Names
When adding your Merge Fields to the spreadsheet template, you will need to enter the name of the Merge Field from FYI.
To locate the Merge Field Names, you can use either of the options below:
- FYI Merge Field Glossary.
- FYI Email Template function.
Merge Fields Glossary
The Merge Fields Glossary contains a list of all Merge Fields available to FYI. Refer to FYI Merge Field Glossary.
When using the Glossary, fields available to Excel Templates will display the code in the Merge Field Code column. The code can be copied and pasted into Excel when creating the Merge Field as per Step 6 - Define Names for Merge Fields using the Name Manager.
Note: Custom Fields are unique to each practice, and not available in the Merge Field Glossary. To insert Custom Fields, refer to the Email Template method below.
For example, to use the Client Name Merge Field, the following code is copied from the Glossary:
Email Template
To use the Email Template function, a draft email template can be created to access the Merge Field selection window. The template does not need to be active.
- Open Knowledge - Templates, from the Create Template drop-down select Email.
- In the Merge Fields section of the Email Template, click to select the required Merge Field, for example the Name field in the Client section.
The Merge Field is added to the body of the Email Template. When the Merge Field is inserted into the document, only the value between the curly brackets will be used. In the image below, the Merge Field would be ClientName.
Repeat for any other Merge Fields to be added to the spreadsheet.
Step 2 - Create a Template in FYI
In FYI, create the Spreadsheet Template in the usual way (refer to Creating Templates for Word, Spreadsheet or Presentation).
Merge Fields can be added to the Name field to customise the name of spreadsheets created from the template. Copy the required Merge Field as per Step 1 above and paste it into the Name field.
Step 3 - Enable Merge Names
When creating the Template, ensure Merge Names is enabled in the Filing section of the template.
Note: "Merge Fields" must be toggled off.
Step 4 - Open the Excel Spreadsheet in Desktop Version
When adding Merge Names to an Excel Spreadsheet, this must be done using the desktop version of Excel. The functionality is not available in the Online version of Excel.
- If preparing the Template in Excel first and importing it to FYI later, ensure the file is created in the Excel desktop version.
- If creating the Template in FYI first and then editing it, use the Edit in Desktop function in FYI to open the file in the Excel desktop version.
Step 5 - Design your Template
Begin adding the content to your Template.
For now, leave blank cells for where the data will be inserted by merge fields. This will be covered in the next step.
Step 6 - Define Names for Merge Fields using the Name Manager
Each merge field that will be added to the spreadsheet will need to be defined as a Name in Excel.
You will need the list of Merge Fields obtained in Step 1 - Create a List of Merge Field Names above.
- Click on the Formulas tab on the ribbon.
- Click Define Name (or click Name Manager and then click New).
- The New Name window is displayed. Some fields will be automatically populated depending on your spreadsheet.
- In the Name field, enter the Merge Field name as per Step 1 - Determine the Merge Field Names above, using the format of FYI_(merge field). For example, FYI_ClientName or FYI_Job_custom_Segmentation.
- The Scope should remain as "Workbook".
- Enter a Comment if needed (optional), for example, "The Client Name field in FYI"
- The Refers to field is used to reference the Merge Field specifically coming from FYI. This is only shown in the Template spreadsheet, and will be overwritten when a document is created from the Template. The document will display the data from the Merge Field, or blanked out if there is no available value.
Note: The formatting of this field is crucial to ensure it inserts the data from FYI correctly. If this field is entered incorrectly, you may encounter a "#NAME" error, or the data may not be displayed.
The Refers To field format must always use begin with an equals sign and quotation marks, a prefix of FYI_, followed by the merge field, and then ending with a closing quotation mark:
="FYI_(merge field)"
For example, ="FYI_ClientName" or ="FYI_Job_custom_Segmentation" (for a Custom Field). - Click OK
-
Repeat the steps for all other Merge Fields to be used in the Template.
Step 7 - Add Merge Fields to Excel
- Position the cursor in the cell where you want the data to be inserted.
- Type =FY to display a list of the Names that start with "FY"
- Double-click the Name field that relates to that cell.
- The selected Name is added to the cell.
-
Repeat for all the Merge Fields.
When a document is created from the template, the Merge Fields will be overwritten and be displayed similar to the below.
If #NAME is displayed, check the Refers to field contains the correct formatting of ="FYI_(merge name)". If missing the quotation marks or the equals sign, the content will not be displayed correctly.
Step 8 - Test the Spreadsheet Template
Save the changes to the Template, and either upload it to FYI or click I am finished editing to synchronise the changes. Refer to Creating Templates for Word, Spreadsheet or Presentation for instructions.
You should now test the file by creating a Spreadsheet from the Template. The Spreadsheet that is created can be previewed or edited using the online version of Excel (the Desktop version is only required when creating the template).
If the cell shows a formula instead of the data, confirm the Show Formulas option on the Formulas menu is disabled.
If using the desktop version of Excel, you can display the Name Manager and you will see the Value and Refers to show the content for the Merge Names that have been brought in by the merge.
If the data has not been inserted correctly, confirm the Merge Fields are following the correct format, and that you have configured the Name in the Name Manager correctly.