Features

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

Elite Only.png
Banner - Beta.png

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.

This feature is Beta only, and requires Universal Merge Fields to be enabled. Currently, the function is limited to the Look Up Job and Create Disbursement process steps.

Creating and Importing a CSV File

When creating the Import File:

  1. The file must be saved in the .CSV file format.

  2. We recommend using clearly defined column headings within the file. This will make it easier to identify the data when using merge fields in the automation steps.

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

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

Creating 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.
    2013 Custom Process Edit Filter.gif

    The Filter Items window is displayed.
    987 Custom Process Filter Items.gif

  3. Select the Filter Type of CSV Data Import.
    3459_CSV_Data_Import_Filter_Items.gif
  4. Update the fields for the CSV Import file.

    Client - If the CSV file has been imported to a client, select the client by typing at least three characters. Matching clients will be displayed in the drop-down, click to select the client. The document list at the bottom of the screen will be refreshed to show a list of CSV files. If the file was uploaded to a Knowledge Cabinet, the Client field can be left blank.

    Cabinet - Select the Cabinet to filter the document list to where the Import Template is stored. This can be the Cabinet used for the document filed against a Client, or the Knowledge Cabinet.

  5. Select the CSV file from the document results. The columns in the file will be displayed, along with a preview of the data on the right.
    3460_CSV_Data_Import_Columns.gif
  6. Update CSV File configuration options as required.

    Has Header - Tick this option if the CSV File contains column headings in the first row. These headings will be displayed in the Key field further below.

    Skip Rows - Used 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 ignored. Enter the number of rows to be ignored during the import.

  7. 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.
    3461_CSV_Data_Import_Client_Column_Type.gif
    Sample - An example of the data detected in the CSV File will be displayed.

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

  9. 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 Universal Merge Fields.

  1. Click Add Step.
    Note: This feature is currently only available with the Look Up Job and Create Disbursement steps.

  2. Select FYI Actions and choose the required step.

  3. Click the UMF 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.
    3462_CSV_Data_Import_Trigger_Data_Merge_Fields.gif

  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.

Running the Import

If the process has been scheduled to run manually, start the process by clicking the Run button.

3467_Custom_Process_CSV_Import_Run.gif

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.

3463_CSV_Data_Error_Popup.gif

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.

  • Source CSV contained invalid cells

3464_CSV_Data_Practice_Activity_Error.gif

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. 

Invalid cells can be caused by:

  • 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 is archived, and must be active before they can be updated using the Automation process. Refer to Archived Clients.

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.
    3465_CSV_Data_Import_Issue_Report_Link.gif
  4. You will be redirected to the document, displaying a preview of the invalid data and Failure Reason.
    3466_CSV_Data_Issue_Report_Preview.gif

The data must be corrected in the original file, or using the Issue Report CSV, before reimporting. 

Note: If using the original file, remove rows that were successfully imported to avoid creating duplicates during the next import.

Was this article helpful?
0 out of 0 found this helpful