CSV Files for the integration with Generic Practice Management Software

This article gives details, explanations and samples of the files used to integrate with Generic Practice Management Software using the FYI CSV importer.

For information about how to set up the integration with Generic Practice Management Software, refer to Integration with Generic Practice Management Software. 

A sample of each of the .csv files used in the import can be downloaded from the bottom of this article.

Important Notes

• Do not change the names of the columns - If required you can change the position of the columns but the column names must be exactly as they are shown in the sample files.
  
  
•  You can include commas in the values in a column, except for Keywords. Only single values will be taken for each column, except for Keywords where the comma is used to differentiate where more than one Keyword is being imported.
  
  
• Files must be saved in CSV format to be imported. In Excel, this can be done by clicking File > Save as, changing the name to match the import (see below for filenames), and the Save as type: CSV UTF-8 (Comma delimited) (*.csv). Save the file to the FYI - Practice Sync folder in OneDrive to begin the import process.
  
  
• Data is imported import into FYI only, and will not synchronise with Xero Practice Manager. If data is imported into FYI for Jobs, Clients or any other data that exists in Xero Practice Manager, the data may be overwritten by the overnight synchronisation back to the values stored in Xero Practice Manager.

Sending All or Only Updated Information

The .csv files can include all the Clients, Contacts, Users and Jobs every time you send them, or you can send only new and updated information. Any duplicate or unchanged information is detected and not updated in FYI.

Clients

The FYI CSV Importer imports Client data.

If the record in the .csv file does not have a ClientCode, then the Client_ID is used. Updates to the Client Name can be made if both the Client_ID and ClientCode fields are correct.

Clients are imported with the Source set as “Practice Sync” and with the Status set as "Active". Client Custom Field details are imported via a separate file (refer below to Client Custom Fields).

You cannot delete clients using the CSV import.

Note: 

• Client_ID and ClientCode values must be unique
• The filename must be: clients.csv

The ContactAddressee is used for the name of the person used for address details.

When entering a phone number using the Phone or Mobile columns, the number should have spaces removed so it's a string of numbers e.g. 0400111222.

The Salutation field is the first name of the contact and is used by merge fields when creating documents or emails.

The ContactID field must be numerical, for example, 240483.

The AccountManager column can be used to assign the client's Partner in FYI. Similarly, the client's Manager in FYI can be assigned using the JobManager column.

The CompanyNumber must be greater than six characters.

To update the Postal Address fields for clients, you will need to update the following columns in the file:

• Address1
• Address2
• City
• Region
• Postcode
• Country

Client Custom Fields

The FYI CSV Importer imports the Custom Fields for clients via a separate import file.

If the .csv contains ClientCode, the import will use this. If the .csv does not contain ClientCode, the ClientName is used.

As well as the value that is set for Custom Field for the client, the import file also needs to hold the Type of Custom Field. These are entered as follows:

The Custom Field Name and Type are case-sensitive. If a Custom Field is entered in a different case for the Name or Type, this will create a new Custom Field with the type set as "Text".

If the same Custom Field is specified more than once in the same import file, the later row will overwrite the value of the previous one.

Custom Field values that are the same as those already set in FYI are not updated.

The updated Custom Field will sync back to Xero Practice Manager.

The filename must be custom_field, custom field or customfield with any suffix and ending in .csv (case insensitive). For example, ‘Custom Fields 2021.csv’ is a valid filename.

Unmatched Clients

The FYI CSV Importer can be used to import "Unmatched Clients". This can be used to merge unmatched clients with the relevant active client. The unmatched clients are matched to the relevant active client using the Client Name or if this is not provided on the Client Code. If you want the unmatched clients to also be automatically archived, enter the name you want the archived client to be renamed to.

If the Client Code does not exist, the value in the column "Client Code Match" should be left empty.

For more details on importing Unmatched Clients, refer also to Unmatched Client Import. The Unmatched Client Import process uses an Excel spreadsheet as the import template. This spreadsheet can be requested from your Onboarding Consultant.

The filename must be: unmatched.csv

Contacts

The FYI CSV Importer imports details of the Client Contacts. It imports the Contact Name, Email and Phone Number.

Contacts are matched to the Client using the ClientCode in the contacts.csv file.

The ContactID column is a mandatory field. All contacts must have a unique ContactID value.

The following fields cannot be synced to FYI using the Generic Practice Management sync. 

• Contact Addressee
• Contact Mobile
• Contact Position

The filename must be: contacts.csv

Contacts Phone

As per the MYOB database, the ContactName column is designed to accept multiple phone numbers and email addresses for the Contact.

The ContactID is matched to the client using the ClientCode in the contacts phone.csv file.

The filename must be: contacts phone.csv

Staff

Users' data is imported with the users' Name, Phone, Email, Active status (Yes or No) and Role.

The filename must be: staff.csv

Jobs

Jobs are imported with the Job Number, Name, Client, Budget, Start Date, Category and Job State.

Custom fields can be imported for all field types, for example, dates, users, email addresses, dropdowns etc. Refer to Managing Custom Fields.

The Client field is case-sensitive i.e. the case must match the name exactly how it appears in FYI e.g. SAMPLE CLIENT and Sample Client are not a match.

The format of the Start Date can be:

• dd/mm/yyyy (for example, 30/09/2022), or
• dd/mm/yyyy hh:mm:ss AM/PM (for example, 30/09/202212:00:00 AM)

You may need to use the "Format Cells" function in Excel to enforce the correct date format.

The .csv will use 'Client' as the column when looking for the Client Name.

Note:

• Job Numbers must be unique
• The filename must be: jobs.csv

Documents

Generic document import will allow imports of additional metadata. Currently, the FYI CSV importer can be used to add Keyword(s) using the document Reference number in the import file.

Keywords can be more than one word (for example, Tax 2022).

Multiple Keywords can be added by separating them with a comma in the Keywords column (for example, "Tax 2022, CGT")

If you add the same Reference more than once (in the same or subsequent import) the import will overwrite the existing Keyword(s).

Note: 

• Reference Numbers must be unique
• The filename must be: documents.csv

File Notes

A File Note is created in FYI for any rows in the documents.csv file where:

• There is a value in the Reference column,
• The Reference does not match an existing document,
• The value in the Type is "Note".

The File Note is created with the unique Reference Number. The File Note can optionally be assigned to a Cabinet by specifying the ‘CabinetName’ field. The Reference number can be any random sequence number and there is no need to follow the format used for FYI Reference Numbers. 

If the Reference number matches an existing document, only the Keyword(s) will be updated.

Clients can be matched using either the ClientCode or ExportCode columns. The ClientCode will take precedence if values are present in both columns.

Keywords can be more than one word (for example, Tax 2022).

Multiple Keywords can be added by separating them with a comma in the Keywords column (for example, Tax 2022, CGT)

The format of the dates used for the CreatedOn and ModifiedOn fields need to be in one of the following formats:

• YYYY-MM-DD
• YYYY-MM-DD HH:mm
• YYYY-MM-DD HH:mm:ss

You may need to use the "Format Cells" function in Excel to enforce the correct date format. If you have trouble formatting your date fields to the required format, refer to the solution Why didn't my File Notes CSV file upload when using Generic Practice Sync?

The filename must be: documents.csv
Note: The sample file attached below has the name documents - File Note Importer.csv, but would need to be renamed before import.

Download Sample Files