Plan Availability:
Starter
Intermediate
Pro
Elite
User Permissions:
This feature is currently in Beta. To request access, please contact Support.
This article explains the process of importing data from a CSV file into FYI using a new CSV Import Filter available when creating a Custom Automation Processes. Merge Fields are used to insert the data, available in a variety of steps, for example, Creating Clients, Jobs, or Disbursements.
Step 1 - Preparing for the Import
Create the CSV File
When creating the Import File:
- The file must be saved in a .CSV file format.
- There is a limit of 5,000 rows that can be imported at one time. Additional rows will need to be split into a new file and imported separately.
- The first row will be used as Column Headings, to identify the data when importing the file, and using merge fields in the automation steps.
- If not creating new clients during the Automation Process, at least one column must be nominated to match an existing client.
- When importing Custom Fields, each Custom Field must have its own column in the file. If the Custom Field is a drop-down, the contents of the column must match an existing option in FYI.
Matching Existing Clients
When configuring the CSV Data Import Filter, if the Match Clients During Validation option is not selected, you will only be able to add the Create Client step as the first step in the process. This client will be the client used for the remainder of the process.
If you don't wish to create a new client, and want to use the existing clients in your FYI platform, you will need to tick the Match Clients During Validation option. At least one of the following columns must be included in your CSV file:
- FYI Client ID
- Client Code
- Client Name
- Client Export Code
- Company Number
- Business Number
- NZBN
- Job Number
- Tax Number
- IRD Number
- VAT Number
The column will then need to be updated in the CSV Data Import Filter to select a Column Type to indicate it can be used to match clients. Refer to Process Filter Column Types below.
If one of these columns are not available, and has , you will be unable to save the filter, and the following warning will be displayed:
- At least one included column must have a client type selected.
Add the CSV File to FYI
For client-specific import files, the file can be imported using either Drag and Drop or File Import. Refer to the articles in the section Importing Documents to FYI.
If the file is not related to a client, create a separate Knowledge Cabinet to store your CSV Import files, for example, "Import files". CSV files can be added to Knowledge Cabinets using Drag and Drop. Refer to Using Knowledge Cabinets and Drag and Drop.
Create a CSV Document View
A Document View must be selected when configuring the CSV Import Filter in the Automation Process.
- Navigate to the Documents List.
- Update the Type Filter to select "CSV" only.
-
Save the view using a descriptive name. You will need to select this View when creating the Automation process.
Step 2 - Importing Data from the CSV File
Create the Automation Process
Once the CSV file has been added FYI, the data can be imported using a Custom Automation process.
For detailed instructions on creating a Custom Automation, refer to Setting up Automation Processes and Setting up Custom Processes.
Select the CSV Import Process Filter
When creating the Custom Automation Process, select the filter CSV Import. This filter will allow you to select the CSV file, and configure the columns ready for import.
- From the Process - Details tab, click the edit icon next to Filter.
- Select the Filter Type of CSV Data Import.
- Select the Document View created in Step 2 - Create a CSV Document View.
- The Sample Document field will be displayed. Click the dropdown to view a list of CSV documents displayed in the selected view.
- Once a CSV document is selected, the page will refresh to display the columns in the file, along with a preview of the data on the right.
- The Skip Rows field allows you to exclude a specific number of rows from the import. For example, if the file contains instructions in the first three rows, enter a value of 3. The first three rows will not be imported.
- Configure the Import Template Columns to ensure the data is imported in the correct format for each column.
Include - Tick to include the data in the column in the import, or untick if the data is not required.
Key - The name of the column from the CSV file.
Type - Used to indicate the format of the data in that column, for example, Date. Detailed information on each of the Column Types can be found below.
Please note that if an incorrect Type is selected, a preview of the data will not be displayed when running the import, and the data will not be imported. For example, columns with a "Numeric" type selected cannot contain symbols, e.g. $3000.
At least one column must be configured to identify the client. Refer to Matching Clients above.
Sample - An example of the data detected in the CSV file will be displayed.
- The Run Only Once Per Row option allows the process to run only once per row in the file to avoid duplicates being created against the same client or job.
Important: It's strongly recommended to keep this option ticked for the CSV Import.
- The Match Clients during Validation option will be selected by default. If a client cannot be found in FYI that matches the details in the CSV file, a new client will be created. If the option is unticked, a new client will be created for each row.
When this option is not selected, the first step in the Automation process must be the Create Client step. Refer to Process Step Details - Create Client.
- Click Save to save the changes to the CSV Import File configuration.
Use Merge Fields to select the CSV Columns
Once the filter has been created, you can begin to add your Process Steps to the Automation. Select the data to be imported for each field in the step using Merge Fields.
Data can be imported for any of the steps displayed when clicking Add Step. For detailed information on creating Automation Process steps, refer to the section Process Step Details.
- Click Add Step.
Note: If the Match Clients During Validation option is unticked, you will only be able to select Create Client as the first step.
- Select FYI Actions and choose the required step. Only the steps where data can be imported will be displayed.
- Click the Merge Field icon next to the step where you would like to import data.
- The available merge fields will be displayed. Click the Trigger Data section to display available columns from the CSV import file.
- Click the Merge Field for the relevant column name to insert the field on the left-hand side.
The Merge Field will be displayed as "TriggerData", followed by the column name. For example, the "Name" column will be displayed as{{ TriggerDataName }}.
Columns will not be displayed if the Type selected in the Process Filter does not match the field in the Process Step. For example, date fields will only display columns with the Type of "Date" selected in the Filter.
- Click Save.
- Repeat for any other fields, then press Save on the step to save the changes.
Test the Process
It is important to test the process with a single row to ensure it is working as you expected, prior to importing an entire file.
- Click Test to display the test pop-up window.
- Select a Source CSV from the drop-down.
- Click to select a row from the CSV to use for the test. If required, you can search for a specific row using the Search field.
Note: If the Match Clients during Validation option from the Process Filter is selected and a matching client cannot be found, you will not be able to search or select a row from the test window. - Click Run Test.
Once the Test has run, an entry will be added to the Process History tab.
Run the Import
Click the Status toggle to change the process to Active.
If the process has been scheduled to run manually, start the process by clicking the Run button.
The Run - CSV Data Import screen will be displayed. Where there are multiple rows, the total number of items the process will execute will be displayed.
The CSV file will be validated, and if there are rows that contain invalid data, a message will be displayed.
Click Run to continue or Close to return to the Automation to correct the data.
Once the Automation Process is run, an entry will be added to the Process History tab for each row in the CSV file. Refer to Process History and Activity.
When running the Automation Process:
- Clients must be active in FYI
- When creating a Disbursement entry, if the Job cannot be found, the Disbursement will be created as a Draft.
Process Filter Column Types
When updating the Automation Filter, you will be prompted to select a Type for each column, used to indicate to FYI what data should be expected in the column.
| Type | Details |
| Business Number |
Imported or matched to the Business Number or NZBN field for a client. |
| Client Code |
Imported or matched to the Client Code field for a client. |
| Client Export Code |
Imported or matched to the Export Code field for a client. |
| Client Name | Will be matched against the client name in FYI |
| Client Tax Number |
Data will be imported or matched to one of the appropriate client fields, depending on the location of the practice: Tax Number |
| Company Number | Data will be imported or matched to the Company Number field for a client. |
| Date | Data in the CSV file must be formatted using dd/mm/yyyy. |
| FYI Client ID |
Data is matched against the FYI Client ID for a client. The FYI Client ID is system-generated by FYI when the client is created, and cannot be edited. |
| Job Category |
Will be matched against a Job Category created in FYI. The Category must have an Active status in FYI. |
| Job Number | Will be matched against a Job Category created in FYI. |
| Job State |
Will be matched against a Job State in FYI. New States cannot be created during Import. The Job State must be active in FYI. |
| Job Template |
Will be matched against a Job Template in FYI. The Template must have an Active status, and must be for the same Job Type. For example, you cannot select a Billing Job Template for a Workflow Job. |
| Numeric | Numbers only. For example, $3000 should be entered as 3000 in the CSV file. |
| String | Used when entering numbers and text, for example a Client Name or a Business Address. |
| Toggle |
Used for True/False or Yes/No drop-down fields, where data can be inserted using a Merge Field, for example Custom Fields set to "Checkbox" or "Drop-Down". The value in the spreadsheet must be set to True, False, Yes, or No. |
| User |
Select a user in FYI. Must match the name of an active user in FYI. |
Error Report
If the CSV contains invalid data, when running the process, a message will be displayed that "Executions will not be created for (number) rows containing invalid data". An Issue Report file will be generated, and a Failure will be added to Practice Activity for the FYI Data Import app.
Please note the process will still be displayed in Automation History as "Success", as the process was able to be executed even where not all data could be imported.
View the Issue Report CSV
To view the Issue Report:
- Click on the Failure in Practice Activity to display the Activity drawer.
- Click the Details section, if not already expanded.
- Click the View issue report CSV link.
- You will be redirected to the document, which will display a preview of the invalid data and the reason for failure.
- The Issue Report CSV file will be filed where the original CSV used in the CSV Import Filter was located, using the same Cabinet, Keywords, Work Type, Tags, Job, and Categories.
Troubleshooting Error Messages
Click on the headings below for troubleshooting information on error messages.
Issue
When trying to import the CSV file, the Issue Report CSV file reports Invalid cells.
Cause
Invalid cells can be caused by:
-
Data format - The Data in the selected Column is not in the correct format. For example, selecting a Numerical Type column for a Date field, or a Date field being entered as "August".
- The Client has been archived - The client must be restored before they can be updated using the Automation process.
Solution
The data must be corrected in the original file or the Issue Report CSV before importing the file.
- If using the original file - remove rows that were successfully imported to avoid creating duplicates during the next import.
- If using the Issue Report file - for practices using the Tag functionality, you will need to remove the tag "Exclude-from-import" to ensure the Issue Report file can be used to re-import data. Files with this tag will not be able to be used.
Issue
When importing data from a CSV file, the user receives the following error message:
"Error: Unable to update CSV import mapping: fields still referenced in merge fields in steps would be removed."
Cause
This issue is caused by the column headers in the CSV import file(s) in the Filter not matching the column headers in the previously imported CSV file.
Solution
To resolve the error, practices can either:
- Update the CSV file column headers to reflect the same headers as the previously imported file, or
- Update the Automation Filter items by selecting Reset from CSV, to reflect the new column headings.
Examples of using the CSV Import Filter
The CSV Import process can be used with a variety of Automation Steps. Click on the headings below to view more detail on each example.
Plan availability: Pro Plan Elite Plan
Practices on the Pro plan must have FYI as Primary for Client Maintenance enabled to create clients in FYI.
The Create Client step can be used with the CSV Import Filter to create new clients in FYI in bulk, rather than one-by-one.
The fields available are the same as creating a client using the + button, although fields can be updated in multiple ways:
- Automatically - using Merge Fields to insert the data from the imported CSV file.
- Manually, by selecting from the field options or inserting the data directly.
Recommended Process Filter Column Types
Before adding the Create Client step, you should review your Process Filter to ensure your CSV Import File has been optimised and ready for import. Below are the recommended Type selections for each Client Field.
| Field | Required | Type |
| Group | Recommended | String |
| Structure | Required | String |
| Name |
Required At least one field used to match Clients is required |
Client Name |
| First Name | Required for Individuals only | String |
| Last Name | Required for Individuals only | String |
| Other Name | Optional | String |
| Salutation | Required | String |
| Required | String | |
| Phone Number |
Recommended Ensure the Phone Number in the CSV starts with a 0 |
String |
| Mobile Number |
Recommended Ensure the Phone Number in the CSV starts with a 0 |
String |
| Export Code | At least one field used to match Clients is required | Client Export Code |
| Client Code | At least one field used to match Clients is required | Client Code |
| Manager |
Recommended Field must match the name of an active user in FYI. |
User |
| Partner |
Recommended Field must match the name of an active user in FYI. |
User |
| Custom Fields |
Optional Custom Fields must be active in the FYI platform. |
String, Date, Numeric, or Toggle depending on the Custom Field |
| Custom User Role |
Optional Field must match the name of an active user in FYI |
User |
| Business Number NZBN |
Recommended At least one field used to match Clients is required |
String |
| Company Number |
Recommended At least one field used to match Clients is required |
Company Number |
| Tax Number IRD Number VAT Number |
Required |
Client Tax Number |
| Balance Month |
Optional 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 |
Optional Enter True or False |
String |
| Physical Address (all fields) Postal Address (all fields) |
Recommended, although Country is optional |
String |
Selecting the CSV Column for each Step Field
Merge Fields are used to select the CSV column to be used for each field in the Process 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.
Selecting a Client Group
Users can select a Client Group name from the imported file by selecting the relevant Trigger Data Merge Field.
If the Client Group from the file does not already exist in FYI, the Client Group will be created at the same time as creating a new client.
Setting up the CSV Import File for all Business Structures
The Business Structure, for example Company or Individual, is used to determine which fields are displayed for a client in FYI. For example, a Company will have a single "Name" field, while an Individual will display "First Name" and "Last Name".
When setting up the CSV file to import your client data, the file should include columns for all possible fields, regardless of Business Structure, however, you should only fill in data for the columns relevant to each specific client.
For example:
- For a Company, populate the "Name" field, and leave "First Name" and "Last Name" blank.
- For an Individual, populate the "First Name" and "Last Name" columns, and leave the "Name" column blank.
When setting up the Create Client step, ensure the appropriate Trigger Data Merge Field is selected for each field, for example, the Trigger Data Name Merge Field selected for the Name field.
This will allow users to run a single import for all Business Structures at once, rather than running separate imports for each type. FYI will only apply the relevant data for each client based on the CSV Import File.
Setting the Client Partner and Manager
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.
Selecting a Custom Field
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.
Ensure 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 has been selected in the Create 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 window, 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".
The Create Job step can be used with the CSV Import Filter to create new jobs in FYI. For example, if migrating to FYI from another Workflow Management System, you can export a list of your jobs from your previous system to bulk import into FYI.
Note: Imports can only be processed for a single Job Type at a time i.e. Billing Jobs, Workflow Jobs, or Standard Jobs.
Separate Imports for Each Job Type
The Create Job step only allows for one Job Type to be selected at a time, for example, Standard Jobs. You will need to perform separate imports for each Job Type.
Ensure you have created different CSV files containing jobs of the same Type only.
Recommended Process Filter Column Types
Before adding the Create Job step, you should review your Process Filter to ensure your CSV Import File has been optimised and is ready for import. Below are the recommended Type selections for each Job Field.
| Field | Required | Type |
| FYI Client ID |
Required Used to match the Job to an existing client in FYI |
FYI Client ID |
| Job Template |
Required Must match an existing Job Template in FYI, and the type of Job being created e.g. Billing. |
Job Template |
| Name |
Required The name of the Job in FYI |
String |
| State |
Required Must match an existing Job State in FYI |
Job State |
| Cabinet |
Optional Only required if using Cabinet Security. Must match an existing Job Cabinet in FYI |
String |
| Category |
Optional Must match an existing Job Category in FYI |
Job Category |
| Budget Amount |
Recommended Ensure the CSV does not contain symbols e.g. currency symbols |
Numeric |
| Budget Hours |
Recommended |
Numeric |
| Start Date |
Optional Must be formatted using dd/mm/yyyy |
Date |
| Due Date |
Optional Must be formatted using dd/mm/yyyy |
Date |
| Manager |
Recommended Must match the name of an existing user in FYI |
User |
| Partner |
Recommended Must match the name of an existing user in FYI |
User |
| Description |
Recommended Line breaks in the CSV file will be supported |
String |
| Custom Fields |
Optional Custom Fields must be active in the FYI platform. |
Type will vary depending on the Custom Field. Select from Date, Number, String, Toggle, or User. |
| Custom User Roles |
Optional Must match the name of an active user in FYI |
User |
Match Clients During Validation
During the import, FYI will need to assign a client to the job. If the client already exists in FYI, ensure the Match Clients During Validation step is ticked. If a matching client cannot be found, the data will not be imported, and an Error Report will be generated.
If the client doesn't already exist, or the Match Clients During Validation step is unticked, the Create Client step must be the first step in the Automation Process. You can then add the Create Job step after that, matching with clients created in the first step.
Selecting Columns for Specific Types
When updating the Process Filter to select column Types, you can select the Job Number, State, Category, and Template. This will ensure that the column is only displayed for the relevant field.
For example, if a column has the "Job Template" type selected, the column will only be displayed in the Trigger Data section for the Job Template field.
Selecting Imported Data that is a Date
The Automation Process filter allows the CSV Columns to be set with a "Date" Type. These columns are then available as Merge Fields in the Create Job step for any Date fields, for example, Start Date.
If the "Date" Column Type has not been selected in the Filter, the columns will not be available to select as a Date Merge Field.
Note: Dates must be formatted in the CSV file as dd/mm/yyyy.
Selecting Imported Data that's a Number
Number values can be imported from a CSV, for example Budget Hours, or Budget Value.
To ensure the CSV is configured properly, the values in the column must contain numerical values only e.g. 3000. Letters or symbols must not be added e.g. $3000 AUD.
When configuring the Automation Process filter, select "Numeric" from the Type drop-down.
Selecting a Primary Job
The Primary toggle must be configured on the Create Job step in FYI, and cannot be modified depending on the value of a Merge Field. This means the option cannot be set automatically by imported data.
To configure Primary Jobs, you will need to do a separate import of Primary Jobs only, with the option toggled on, followed by a different import for non-primary jobs with the option toggled off.
Setting the Job Partner and Manager
When setting the Job 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 jobs, select the user from the Partner or Manager drop-down rather than using a Merge Field.
Selecting 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 job.
Ensure you select the correct Type for each Custom Field in the Automation Process Filter.
When a Job Custom Field has been selected in the Create Job 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 window, 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".
Where Custom Fields are available on the Automation Step, data can be imported from the CSV file to set the Custom Field. For example, an "Office" Custom Field can be imported when creating a client to assign the client to a particular office.
Create Columns in the CSV File
Each Custom Field must have a separate column in the CSV file. To make it easy to identify, it's recommended that the Column Name should match the Custom Field Name in FYI.
Within the column, each row should contain the Custom Field Option relevant to the client.
When entering the value into the row:
- The option must already exist in FYI.
- The data must match the Custom Field Option in FYI exactly.
For example, the value "bi-weekly" is not one of the options in the image above, and would not be imported.
Automation Process Filter
When configuring the Automation Process Filter, ensure you select the correct Type for each Custom Field in the Automation Process Filter. For example, drop-down would be set to "String", while Date Custom Fields would be set to "Date".
Selecting the Trigger Data
When a Custom Field has been selected in the Automation step, the Custom Field will be added as another field to the drawer.
Click the Merge Field icon to insert data from the CSV Import File.
When selecting the Trigger Data from the Merge Field window, 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".
When the data is imported, the Custom Field will be updated accordingly.
Plan availability: Elite Plan
The Create Disbursement step can be used with the CSV Import Filter to create new disbursements for clients in bulk.
Note: The Look Up Job step must be added to the Automation Process before the Create Disbursement step, to allow FYI to create disbursements for the correct job.
Recommended Process Filter Column Types
You should review your Process Filter to ensure your CSV Import File has been optimised and ready for import. Below are the recommended Type selections for each Disbursement field.
| Field | Required | Type |
| Date |
Required Data in the CSV file must be formatted using dd/mm/yyyy |
Date |
| Client Name | Required | String |
| Job Number |
Required Either a Job Number or Job Name must be specified to create the Disbursement for a Job |
Job Number |
| Job Name |
Required Either a Job Number or Job Name must be specified to create the Disbursement for a Job |
String |
| Disbursement Name |
Required |
String |
| Rate |
Required Values should be entered as a number only without a currency symbol |
Numeric |
| Quantity |
Recommended If a Quantity is not provided in the CSV file, the quantity entered in the Automation step will apply to all imported disbursements |
Numeric |
| Notes | Optional | String |
| Custom Fields |
Optional Custom Fields must be active in the FYI platform and applied to Time and Disbursements. |
String, Date, Numeric, or Toggle depending on the Custom Field |
Job Lookup Step Requirement
Before you can import disbursements, the Automation Process must first contain the Look Up Job step. This will search FYI to locate the job the Disbursement Entry will be created for.
When creating your CSV file, ensure you add either the Job Number or the Job Name.
For Job Numbers that start with a 0, an apostrophe must be added to the field first to ensure the cell is recognised as Text, and the leading zero is not removed.
When adding the Look Up Job step to the Automation process, the Client field must be set to Current Client. This will search for jobs only for the clients in the CSV file.
Update the Job Number or Name field to select the data in the CSV file. 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.
The If Job Not Found field should be set to Continue Process. The process will continue for each job as per the selected Automation Filter, and displays a warning message for each step that cannot be completed for a matching job.
Using Merge Fields with the Create Disbursement step
After adding the Create Disbursement 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.
Selecting the Job
When selecting a Job using a Merge Field, you will only be able to select from steps earlier in the process, e.g. the Look Up Job step.
Expand the Merge Field section for the appropriate Step number, and click the Job Merge Field.
Selecting a Rate
If the Use Standard Rate field is set to Yes, you will only be able to select from the Rates available in the drop-down box. The selected Rate will then apply for all imported entries.
To use a rate imported from the CSV file, Use Standard Rate must be set to No. This will enable the Merge Field icon, so you can select from the Trigger Data Merge Fields.
Selecting a Quantity
The Quantity can be entered directly into the Create Disbursement step, or selected from a Merge Field. If entering a value in the step, the quantity will be applied to all imported entries.
Separate Import Files for Status
The Status cannot be imported and must be selected within the Create Disbursement step. This means the same Status will apply to all imported entries.
If required, create separate CSV files for Draft and Submitted disbursements.