Plan: Not Available: Intermediate Available: Pro or Elite
Users: Not Available: All users Available: FYI Admins or User Group permissions
Practice Management Source: Available: All sources
This example details how to use a Custom Automation Process to import data from a CSV file and update clients in FYI. The Update Client step can be used with the CSV Import Filter to update existing clients in FYI, for example, to update Custom Fields.
This process uses:
- The CSV Data Import filter.
- The Update Client Automation Step.
Practices must be on the Elite plan with FYI as the Practice Management Source, or have the FYI as Primary for Client Maintenance setting enabled.
For practices on the Pro plan, it's important to consider the impact of Xero Practice Manager Rate Limits when using this feature.
This feature is currently in beta for the FYI Pro Plan. Learn more about beta registrations.
Creating the CSV File
The CSV File contains all the data to be imported into FYI, relevant to each client. Refer to CSV Columns below for details on columns that can be added to the file, including the required mandatory columns.
The following is an example of a CSV file used to update clients in FYI.
To successfully match data to an existing client in FYI, one of the following client fields must be included in the CSV import file. Ensure the columns have been mapped to the correct Type in the CSV Import filter.
| Column | Type |
|---|---|
| Client Name | Client Name |
| Client Code | Client Code |
| Client Export Code | Client Export Code |
| Business Number NZBN |
String |
| Company Number | Company Number |
| Tax Number IRD Number VAT Number |
Client Tax Number |
CSV Columns
The following columns can be added to the file and imported into FYI. Fields marked as Required are mandatory fields in FYI.
When updating any of the below client fields, ensure the columns have been mapped to the correct Type in the CSV Import filter.
| Field | Notes | Type |
|---|---|---|
| Group | String | |
| Structure | String | |
| First Name | Used for Individuals only | String |
| Last Name | Used for Individuals only | String |
| Other Name | String | |
| Salutation | String | |
| Addressee | String | |
| String | ||
| Phone Number | Ensure the Phone Number in the CSV starts with a 0 | String |
| Mobile Number | Ensure the Phone Number in the CSV starts with a 0 | String |
| Manager | Field must match the name of an active user in FYI | User |
| Partner | Field must match the name of an active user in FYI | User |
| Custom Fields | Custom Fields must be active in the FYI platform | String, Date, Numeric, or Toggle, depending on the Custom Field |
| Custom User Role | Field must match the name of an active user in FYI | User |
| Balance Month | Ensure the full name of the month is entered, e.g. July, not Jul | String |
| Prepare AS Prepare GST Prepare Tax Form Signed Tax Authority GST Registered GST Period GST Basis |
Enter True or False | String |
| Physical Address (all fields) Postal Address (all fields) |
String |
Using Custom Fields
Each Custom Field will need to have a separate column in the CSV file, with each row containing the Custom Field option relevant to that client.
Confirm you select the correct Type for each Custom Field in the Automation Process Filter. In the image above, the columns would be set to Toggle, String, and Date.
When a Client Custom Field is selected in the Update Client step, the Custom Field is added as another field to the drawer.
Enter the value for the Custom Field, or click the Merge Field icon to insert data from the CSV Import File.
When selecting the Trigger Data from the Merge Field Editor, only columns with a Process Filter Type relevant to the Custom Field Type will be displayed. Date fields will display columns with a "Date" Process Filter Type, while "Drop-down" Custom Fields will display "String" Process Filter Type columns.
If there are no columns in the CSV Import File with a matching "Type", a warning will be displayed: "No compatible merge fields available for target field".
Creating the Custom Process
Before creating the Custom Process:
- Ensure the CSV file has been saved in CSV UTF-8 format and uploaded into FYI.
- A CSV view has been configured, displaying the required CSV file. This view will be selected in the Automation Filter window.
- Confirm all users referenced in the file exist as active FYI users.
- Review your Custom Field columns to confirm they match the fields in FYI.
- Check that the required fields are included in the CSV file for each Business Structure.
The following is an example of the Custom Process used to import clients. For detailed instructions, refer to Process Filter Details - CSV Data Import and Setting up Custom Processes.
Configure the CSV Import Filter
The following is an example of the CSV Import filter. Refer to CSV Columns above for details on the Type required to be selected for each of the columns from your Import File.
The Match Clients During Validation option in the Automation Filter settings must be selected to avoid creating duplicate clients.
If this option is not ticked, you will only be able to create new clients in FYI.
Add the Update Client Step
The fields available in the Update Client Automation step will depend on whether your practice has enabled FYI as Primary for Client Maintenance. Data can be inserted from the imported CSV file, or entered manually by typing or selecting values from the available field options.
For practices where FYI as Primary for Client Maintenance is enabled, the Update Client Automation Step will allow additional client fields to be updated.
For practices on the Pro plan, where FYI as Primary for Client Maintenance is not enabled, the Update Client step can be used to update the default Cabinet used by AutoFile, Client Custom Fields, Client Code and Export Code.
Managing Existing Client Data
Merge Fields are used to select which CSV column will update client data via the Automation step.
Click the Merge Fields icon to display the list of available Merge Fields.
Column Headings will be displayed in the Trigger Data section of the Merge Fields window. Click the relevant Merge Field and press Save. The Merge Field will be displayed in the Step for the relevant field.
When selecting the Trigger Data Merge Field, select whether the existing value in FYI will be retained or cleared if the import file does not contain a value for every client. If the option is set to Retained, and the value for the column is empty for that client in the import file, the existing data entered in FYI will not be affected. To clear the data instead, refer to Clearing Data from the Client.
When using the Update Client step, you can select which fields will be updated and which fields will not be changed.
Any field that displays as Unchanged will not be impacted by the import. To set a field to Unchanged, for example, if the field displays a Merge Field, click into the field and delete the value.
In addition, you can control whether to retain the data in FYI if not all clients have a value entered for a column in the CSV Import file.
When selecting the Trigger Data Merge Field, set the Merge Field option to Retained. This means that if the field for that client is blank in the import file, the existing data entered in FYI will not be removed.
Please note that this setting is not available for the following fields:
- Client Group
- Business Structure
- Prepare Activity Statement
- Prepare Tax Form
- Email - ensure the Email field shows "Unchanged" to avoid updating the Email field. Refer to Separate File when Updating Email Addresses below.
The Update Client step includes the ability to clear fields currently populated for the client. Where the Eraser icon is displayed, the field can be set back to blank when the Automation Process is run, for example, to remove the value selected for a Custom Field.
The following fields cannot be cleared from the client using the Update Client step:
- Structure
- Client Group
- Include in AutoFile
You can also control whether to clear the data in FYI if not all clients have a value entered for a column in the CSV Import file.
When selecting the Trigger Data Merge Field in the Update Client step, ensure the Merge Field option is set to Cleared. When the Automation Process is run, if the file does not contain a value for the client, the existing data for that field in FYI will be removed.
Additional Field Configurations
The following fields should be configured as required for the practice.
The Email field is only available on the Update Client step for practices on the Elite plan, or where FYI as Primary for Client Maintenance has been enabled.
Within FYI, where an email address has not been entered for a client, the client's Email field will default to the Primary Contact's email address, indicated by an address book icon. If a Primary Contact is not selected, the email address field will remain blank.
In the Update Client step, the Email field does not contain a Clear Data function. Instead, data can be replaced by leaving the field blank in the import file and selecting the Merge Field in the Update Client step.
When the import is run, and the Merge Field has been selected for the Email field, FYI will either update or clear the email address field based on the following:
- If an email address is entered in the CSV Import file, the client's email will be updated to match the import, including where the client was previously defaulting to the Primary Contact's email address.
- If an email address is not entered in the CSV Import file:
- If the client in FYI currently has an email value, the email will be removed by the import.
- If the client in FYI does not have an email address, and has defaulted to the Primary Contact's email address, or no Primary Contact is selected, there will be no impact.
It's recommended to use a separate import file when updating email addresses, containing only clients where the email address is intended to be updated.
If using the Update Client step, and the Email field will not be updated, ensure the Email field is displayed as Unchanged in the Update Client automation step.
In the Update Client step, users can select a Client Group name from the imported file by selecting the relevant Trigger Data Merge Field.
If the Client Group in the CSV Import file does not already exist in FYI, the new group will be created at the same time as creating or updating the client via the Automation process. Any other clients in the CSV Import File with the same Client Group value will be automatically added as members in FYI.
If updating the Client Group for an existing client in FYI, the client will be removed as a member of the previous group, and added to the new group listed in the CSV Import file.
Please note that Client Groups cannot be cleared using the CSV Import, i.e. you cannot set the Client Group field to blank.
When selecting a Cabinet, you will only be able to select a cabinet from the drop-down field. This will apply the same cabinet to all clients.
When setting the Client Partner and Job Manager, you can use the Merge Fields to select from the Automation Initiator, the user importing the file using the Automation, or Trigger Data from the CSV Import File.
If the same user will be assigned to all imported clients, select the user from the Partner or Manager drop-down, rather than using a Merge Field.
Previewing and Running the Process
The Custom Process can be run from Automations - Processes by an FYI Admin, or a user in a User Group that has Permissions enabled for Automations.
Data will only be imported into FYI when the Automation Process is tested (a single entry only) or when the process is run (all CSV files in the selected Document View, up to a total of 10,000 rows).
A preview of the import will be available when testing and running the Automation process. If any of the rows contain invalid data, click the link to download the Error Report. Update the CSV file to correct the data as required before running the import.
Review the Process History
When a Process has been run, you can review the status and check what was processed. The Process can be reviewed by opening the process and clicking the History tab. Refer to Automation History.