Including Merge Names in Excel Templates

You can bring in content from your practice management software and from FYI by including merge fields in a Template for Excel. These are set up in the Excel spreadsheet that is used as the Template, for example, to include the "Client Name", "Manager", etc.

There is no way to directly add merge fields to external sources in Excel. For Spreadsheet Templates in FYI, the recommended way to include these is:

1. In the Excel Template in FYI, enable Merge Names.
   
   
2. Within the spreadsheet used for the Template, for each Merge Field you want to use in that Template, define an Excel Name that matches and references the Merge Field used within FYI.
   
   Note: In the definition, the values entered for "Name" and "Refers to" are case sensitive.
   
   
3. In the cell in which you want to bring in the content for the Merge Field, select the Excel Name that has been defined.

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).

Content for Merge Fields can also be included as Merge Fields via the Email Template Editor (refer to Including Merge Fields in Excel Templates). However FYI recommend using Merge Names and to not use Merge Fields via the Email Template Editor. Merge Names are more reliable, especially in large spreadsheets, and allow you to bring in content to macro enabled spreadsheets. Using Merge Fields via Email Template Editor to FYI Spreadsheet Templates will be phased out in the near future.

This article contains the following sections:

• Enable Merge Names in the Spreadsheet Template
• Using Merge Names in combination with Merge Fields
• Setting up the Excel Spreadsheet with Merge Names
  • Downloading the FYI Merge Fields.docx
  • Using the Email Template Editor to Determine the Text to reference the Merge Fields
• Important - Use the Desktop Version of Excel when adding Merge Names
• Defining the Names in the Excel Spreadsheet
• Adding the Named Field to a Cell in the Spreadsheet
• Defining Merge Names for Custom Fields
• Today's Date
• Adding FYI Custom Fields to Excel Spreadsheets as Merge Names
• Download Files

Enable Merge Names in the Spreadsheet Template

To include Merge Fields as Merge Names in a Template for an Excel Spreadsheet, in the Template in FYI you must enable Merge Names in the Template.

When creating or editing the Spreadsheet Template in FYI, click Merge Names in the Filing section of the template to set this as "On".

Using Merge Names in combination with Merge Fields

It is possible to use a combination of Merge Names and Merge Fields by enabling both of these in the Filing section of the Template. This is not recommended. Using Merge Fields via the Email Template Editor in Excel Templates will be phased out in the in the near future.

Setting up the Excel Spreadsheet with Merge Names

The examples below are for Custom Fields from Xero Practice Manager which have the prefix XPM_custom.

• For Custom Fields from GreatSoft, these are added in the same way but with the prefix to the Field name entered as greatsoft_custom.
• For Custom Fields added in FYI (refer to Managing Custom Fields) they are added in the same way but with the prefix to the Field name entered as FYI_custom_*
  Refer also below to Adding FYI Custom Fields to Excel Spreadsheets as Merge Names.
• For Custom Fields from the Generic Practice Management Software using the FYI CSV importer, these are added in the same way, but the prefix to the Field name entered as FYI_custom.

Refer to your Excel documentation for information on defining Names in a spreadsheet. The following is a summary of setting these up for use within Excel Templates in FYI and examples of how to set up the Name and Refer to in the definition.

1. Determine the exact name of the Merge Field used in FYI by downloading the FYI Merge Fields.docx or using the Email Template Editor which is useful to find the name of any Custom Fields set up for your practice.
2. Using the desktop version of Excel, either edit the spreadsheet when creating the Template in FYI or prepare the spreadsheet so it can be added to the Template using Drag and Drop or the Upload function (refer to Creating Templates for Word, Spreadsheet or Presentation).
3. Use the examples in the table below as a guide to defining the Excel Name in the spreadsheet. The Excel Name needs both the "Name" and the "Refers to" to be added.

Downloading the FYI Merge Fields.docx

To determine the exact name of the Merge Fields when setting up the Names in the Excel spreadsheet, you can refer to the FYI Merge Fields.docx,

Click here to download the FYI Merge Fields.docx for a list of Merge Fields that you can copy and paste into your Templates and Stationery (or download the file from the bottom of this article).

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.

Using the Email Template Editor to Determine the Text to reference the Merge Fields

You can also use the Merge Fields section of the Email Template as a reference for the names of Merge Fields you want to include in the Spreadsheet template. This is useful for any Custom Fields that have been set up for your practice. Refer to Creating Email Templates and Signatures and Including Merge Fields in Email Templates and Signatures.

Important - Use the Desktop Version of Excel when adding Merge Names

When adding 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 you have prepared the Excel spreadsheet and will be using Drag and Drop or Upload to import it as a Template, add the Merge Names to the spreadsheet using the desktop version of Excel.
• If you will be editing the Template from FYI, you need to use the Edit from Desktop function.

Defining the Names in the Excel Spreadsheet

You define the Names for any Merge Fields you want to use in the Spreadsheet Template as follows:

1. In FYI, create the Spreadsheet Template in the usual way. Refer to Creating Templates for Word, Spreadsheet or Presentation.
   
   
2. Edit the contents of the template.
   Note: When defining Names, this must be done using the desktop version of Excel.
   
   
3. Determine the exact name of any Merge Fields you want to use in the Template (from the FYI Merge Fields.docx or using the Email Template Editor).
   
   
4. You can set up any text in the spreadsheet, with any formatting, before or after defining the Names. You may find it useful to add the relevant Name as part of the text to indicate where the contents of the Merge Field will be included.
   In the following example, the text "ClientName", "CompanyNumber" and "Manager" will be overwritten when the content for the Merge Fields is populated so this can be added to the spreadsheet with whatever text you want.
   
   
   
   
5. Position the cursor over the text that will be used to define the Name.
6. Display the Excel Formulas menu.
7. Click Define Name.
   The text from the selected cell displays initially as the Name.
   
   
8. Edit the Name to prefix this with "FYI_" and check that the Name is exactly the same text and in the same case as the name of the Merge Field.
   For example FYI_ClientName
   
9. Edit the Refer to field to be exactly the same text, and in exactly the same case, as the name of the Merge Field. This is enclosed in double quotes ("") and prefixed with the equals sign (=).
   For example, ="ClientName".
   
   
10. Click OK.
    
    
11. Repeat this to set up Names for all the Merge Fields you want to use in the Template.
    
    You can use the Excel Name Manager function to display the Names that have been defined.
    
    

Adding the Named Field to a Cell in the Spreadsheet

The following is one way to add the Excel Name to a cell.

1. Position the cursor in the cell you want the content for the Merge Field to be included.
2. Type =FY to display a list of the Names that start with "FY"
3. Double-click the Named field that relates to that cell.
   
   
   The selected Name is added to the formula bar.
   
   
   
   
4. Repeat for all the Merge Name Fields.

Note: When Excel Names have been defined within a spreadsheet, they can also be used as references as usual within Excel, such as in formulas.

Defining Merge Names for Custom Fields

You define the Names for the Merge Fields for any Custom Fields you want to use in the Spreadsheet template as follows:

1. Determine the exact name of the Merge Field used for the Custom Fields. The Email Template Editor is useful for this.
   
   
2. Define the Excel Name in the spreadsheet as above.
   
   For an XPM Custom Field, this needs to have "xpm" in lowercase.
   
   For example, for a Custom Field XPM_custom_Employees
   The Name is FYI_xpm_custom_Employees
   Refers to is  ="xpm_custom_Employees"
   
   
3. Add the Named field for a Custom Field to a cell in the same way as above.
   
   

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 Merge Name for the FYI CreatedDate 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.

Adding FYI Custom Fields to Excel Spreadsheets as Merge Names

Custom Fields that have been added in FYI need to be added as Merge Names in an Excel spreadsheet in a specific format (refer to Managing Custom Fields).

If you want to use Merge Names for Custom Fields that you have added in FYI, to indicate it was added in FYI, in the Name, the "XPM" in the Merge Field needs to be changed to "fyi", for example as "FYI_fyi_customname".

As an example, a Custom Field "Reason for Change" added in FYI displays in the Email Template Editor as follows:

{{ XPM_custom_Reason_for_Change }}

When adding this as a Merge Name it needs to be changed so it is added as follows:

Download Files