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, which is the recommended way. This method is covered in the article below.
- 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. Refer to Including Merge Fields in Excel Templates.
This article outlines how to use the Excel "Name Manager" feature to insert Merge Fields into Templates.
Understanding Merge Field Names
When adding your Merge Fields to the spreadsheet, you will need to use a Merge Field Name. Which name you use varies slightly depending on whether it's a standard or custom field in FYI.
Standard Fields
Standard fields generally follow the format of "FYI_(field name)", for example, FYI_ClientName.
To make it easy, the FYI Merge Fields Comprehensive Guide contains a list of all standard FYI fields that can be added to Excel Templates. These can be copied and pasted when instructed in the steps further below.
Click here to download the FYI Merge Fields Comprehensive Guide.
Note: If you are using Chrome as your browser, the link may not automatically download the document. You need to right-click on the download link above, select Copy link address and paste it into a new browser tab in the browser's URL field.
Custom Fields
Custom Fields generally follow the format of "FYI_custom_(fieldname)", for example "FYI_custom_ClientLevel".
To ensure the field names are inserted correctly, there are rules that must be followed:
-
Prefix the Custom Field name with "FYI_custom_"
- The Field name must exactly match the Custom Field name as it appears in FYI, including the same case. Refer to Managing Custom Fields for instructions on how to find the Custom Field name.
- For Custom Field names with spaces, replace the space with an underscore (_).
- Remove special characters from Custom Field names like full stops (.) or any other special characters (such as , or &).
For users of Xero Practice Manager, please note that Custom Merge Fields will only retrieve data from the Client, and not the Job.
For example:
| Custom Field Name | Merge Field Name |
| Types | FYI_custom_Types |
| Skype Call | FYI_custom_Skype_Call |
| z2.1 Name | FYI_custom_z21_Name |
Creating the Excel Template with Merge Fields
Step 1 - Create a Template in FYI and enable Merge Names
In FYI, create the Spreadsheet Template in the usual way (refer to Creating Templates for Word, Spreadsheet or Presentation).
When creating the Template ensure you enable Merge Names in the Filing section of the template.
Note: Ensure that "Merge Fields" is switched Off.
Step 2 - 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 3 - 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 4 - 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.
Note: Refer to the section Understanding Merge Field Names for details on the format used to enter the Merge Field into Excel.
- 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. Fields will be automatically populated depending on your spreadsheet.
For the Name enter the Merge Field name, for example, "FYI_ClientName" (for a standard field) or "FYI_custom_ClientLevel" (for a custom field). Standard Names can be copied and pasted from the FYI Merge Fields Comprehensive Guide.
The Scope should remain as "Workbook".
Enter a Comment if needed (optional), for example, "Merge field from FYI"
- The Refers to field is used to reference the Merge Field specifically coming from FYI. This is only shown in the Template spreadsheet. When a spreadsheet is created from the Template in FYI, this will be overwritten by the contents of the Custom Field or blanked out if there is no value for a particular Custom Field.
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 correctly.
The Refers To field format must always use begin with an equals sign and quotation marks, followed by the merge field, and then ending with a closing quotation mark:
="(merge name)"
For example, ="FYI_ClientName" (for a standard field) or ="FYI_custom_ClientLevel" (for a custom field).
Replace the (merge name) section, including the brackets, using the Merge Field for either standard or custom fields, as per the section Understanding Merge Field Names.
- Click OK
-
Repeat the steps for all other Merge Fields to be used in the Template.
Step 5 - 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 Custom Field Names.
The following is an example of the spreadsheet that is created from the Template created with Merge Names with both standard and Custom Field Merge Fields.
If #NAME is displayed, check the Refers to field contains the correct formatting of ="(merge name)". If missing the quotation marks or the equals sign, the content will not be displayed correctly.
Step 6 - 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 format in Understanding Merge Field Names, and that you have configured the Name in the Name Manager correctly.
