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 jobs in FYI, for example, bulk updating Custom Fields.
This process uses the following:
- The CSV Data Import filter.
- The Update Job Automation Step.
Note: Imports can only be processed for a single Job Type at a time, i.e. Billing Jobs, Workflow Jobs, or Standard Jobs.
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 for each job. Refer to CSV Columns below for details on the columns that can be added to the file, including mandatory columns.
The following example shows a CSV file used to update jobs in FYI.
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 configuring the Automation Filter in FYI, ensure you select the correct Filter Type.
| Field | Required | Type |
|---|---|---|
| FYI Client ID |
Required Used to match the Job to an existing client in FYI |
FYI Client ID |
| Job Template |
Optional Must match an existing Job Template in FYI, and the type of Job being created, e.g. Billing. |
Job Template |
| Name |
Optional The name of the Job in FYI. |
String |
| Job Number |
Required Used to match both the Job and the Client assigned to the job in FYI. This field is required to enable the Match Jobs During Validation option in the Automation Filter |
Job Number |
| State |
Optional 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 |
Optional Ensure the CSV does not contain symbols, including currency symbols. Decimals will be rounded to the next whole number, e.g. 5.4 will be rounded to 5. |
Numeric |
| Budget Hours | Optional | Numeric |
| Start Date |
Optional Must be formatted using dd/mm/yyyy |
Date |
| Due Date |
Optional Must be formatted using dd/mm/yyyy |
Date |
| Manager |
Optional Must match the name of an existing user in FYI |
User |
| Partner |
Optional Must match the name of an existing user in FYI |
User |
| Description |
Optional Line breaks in the CSV file are 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 |
Using Custom Fields
Each Custom Field must 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 is selected in the Update 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 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, confirm the following:
- 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.
The following is an example of a Custom Process used to import jobs. 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 Jobs During Validation option is used to search for an existing job in FYI.
If the job does not already exist, or the Match Jobs During Validation step is unticked, the Look Up Job step must be used to locate an existing job in FYI. If a matching job cannot be found, the data will not be imported, and an Error Report will be generated.
To select the job in the Update Job step, use either the Trigger Data field for the Job Number in the CSV, or the Job Merge Field located in the Trigger Job section.
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.
Add the Update Job Step
When using the Update Job step, 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.
Using Merge Fields to select Columns
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 are 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.
Additional Field Considerations
Configure the following fields as required for the practice.
The Job State can be selected from the drop-down or the imported data using a Merge Field.
- Drop-down - All jobs will be updated to the Job State selected in the drop-down.
- Imported - When using a Merge Field to select imported CSV data, the Job State column is mandatory for all rows. Any rows with a blank Job State will not be imported.
If using the Update Job step to change the Job Type, for example, to convert Standard Jobs to Workflow Jobs, you will only be able to select Workflow from the drop-down.
When the field displays Unchanged, the current Job Type selection will be retained.
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 jobs updated by the import, 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 with 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.