Importing and Updating Data using the Automation Process CSV Import Filter (Beta)

This article explains the process to import a CSV file into FYI, then run a Custom Automation Process using the data contained in the file to create Disbursement entries.

Importing CSV Files

Step 1 - Creating and Importing a CSV File

File Setup

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.
  
  
• The file must be imported into FYI before running the automation, and must be stored against a Client or a Knowledge Cabinet.
  
  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.
  
  Where a file is not required to be filed against a client, you can create a separate Knowledge Cabinet, 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.

Matching Clients

When configuring the CSV Data Import Filter, one of the columns in the Import file will be nominated as the column used to match the clients in FYI.

Ensure at least one of the following fields has been included in the Import file.

• FYI Client ID
• Client Code
• Client Name
• Client Export Code
• Company Number
• Business Number
• NZBN
• Job Number
• Tax Number
• IRD Number
• VAT Number

Step 2 - Create a CSV Document View

When creating the Automation, you will be prompted to select a document view. This view must be filtered to the Type of "CSV" only. Refer to Sorting and Filtering Documents Lists. 

Save the view, using a descriptive name. You will need to select this View when creating the Automation process. Refer to Saving Changes to the View Layout, Modifying and Deleting Views.

Step 3 - Create the Automation Process

Once the CSV File has been imported, you can create a custom Automation Process to use the data to perform updates in FYI.

For detailed instructions on creating a Custom Automation, refer to Setting up Automation Processes.

Filter type CSV Import

The Filter Type "CSV Import" is used to select the Import Template, and configure the data ready for import. For example, selecting which columns should be imported, and the type of data contained in each column. You will also be able to nominate which column should be used to match the client when running the automation process.

To configure the CSV Import filter:

1. Create a Custom Process. 
   For detailed information, refer to Setting up Custom Processes
   
   

2. Click the edit icon next to Filter.
   
   
   The Filter Items window is displayed.
   

3. Select the Filter Type of CSV Data Import.
   
4. Select the Document View created in Step 2 - Create a CSV Document View.
   
   
5. The Sample Document field will be displayed. Click the dropdown to view a list of CSV documents displayed in the selected view. 
   
   
6. 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.
   
7. Enter a value into the Skip Rows if there is data contained at the top of the file that should not be included. For example, if the file contains instructions in the first three rows, these can be excluded from the import. The value entered is the number of rows to be ignored.
   
   
8. Configure the Import Template Columns to ensure the data is imported in the correct format.
   
   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, "String" is used for text, "Numeric" for numbers.
   
   Ensure that one of the columns has a type selected used to identify the client. Refer to Matching Clients above.
   
   Sample - An example of the data detected in the CSV File will be displayed.
   
   
9. The Run Only Once Per Row option allows the process to run only once against the criteria, to avoid duplicates being created against the same client or job.
   
   
10. The Match Clients during Validation option will be selected by default. If a client cannot be found in FYI matching the details in the CSV file, a new client will be created. If the option is not selected, no match will be performed, and a new client will be created for each row in the CSV file. 
    
    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.
    
    
11. Click Save to save the changes to the CSV Import File configuration.

Inserting Data from the CSV Import File

Once the filter has been created, the data will be able to be inserted against the Custom Automation Process Steps by using Merge Fields.

Refer to Process Step Details - Look Up Job and Process Step Details - Create Disbursement.

1. 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.
   
   
2. Select FYI Actions and choose the required step.
   
   
3. Click the Merge Field icon for the field to insert data from the CSV file.
   
4. The available merge fields will be displayed, grouped by where the data is pulled from.
   
   
5. Click the Trigger Data section to display the Import File columns as Merge Fields.
   
   
   
6. Click the field to be used to automatically insert the field on the left-hand side in the correct format. The Merge Field will be displayed using "triggerdata" followed by the column name. For example, Client Tax Number will be displayed as {{ TriggerDataClientTaxNumber }}.
   
   
7. Click Save.
   
   
8. Repeat for any other fields, then press Save on the step to save the changes.

Step 4 - Testing the Process

It is important to test the process to ensure it is working as you expected.

1. Click Test. The Select Test pop-up will display.
   
   
2. Select a Source CSV from the drop-down.
   
   
3. If required, enter an exact value from a row within the Source CSV into the Search field. Select the filtered Source Document.
   
   Note: If the Match Clients during Validation option is enabled, you cannot search or select a row in the CSV during testing if no matching client is found in FYI.
   
   
   
4. Click Run Test.

Once the Test has run, an entry will be added to the Process History tab for each row in the CSV file. 

Step 5 - Running the Import

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. The CSV file will be validated, and if there are rows that contain invalid data, a message will be displayed stating how many rows will not be executed by the automation process. 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

Note: 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. Refer to Displaying and Updating Time and Disbursements.

Error Report

When one or more rows of the CSV file could not be imported, an Issue Report file will be generated, and a Failure added to Practice Activity for the FYI Data Import app.

View the Issue Report CSV

To view the Issue Report:

1. Click on the Failure in Practice Activity to display the Activity drawer.
   
   
2. Click the Details section, if not already expanded.
   
   
3. Click the View issue report CSV link.
   
4. You will be redirected to the document, which will display a preview of the invalid data and the reason for failure.
   
5. 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. 

Error - Source CSV contained invalid cells

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. 

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.