SFTP CSV File Imports

For our enterprise customers, an SFTP CSV file import service is available at no extra charge. The service is disabled by default, and may be enabled upon request. Contact support@sadiebluesoftware.com to reqeust the service be installed.

Connecting to the SFTP Server

Protocol: SFTP – SSH File Transfer Protocol
Port: 22
Logon Type: Normal/UserPass
Username, password, and host information will be provided by support during the setup process.

Once connected, you will see a list of folders. The folders represent the objects that will be imported into if a csv file is uploaded into it.

• $logs
• $processed
• billingentries
• clients
• contacts
• matters
• medialogentries
• customobjects

Example: Connect to the SFTP Using the FileZilla Client

To connect to the SFTP using the FileZilla client, first make sure that you have FileZilla installed on your computer. You can find the latest version here.

After FileZilla has been installed, run it and click on the Site Manager button. This is the first button on the toolbar. Once the site manager is open you will see the following:

• For Protocol, select "SFTP - SSH File Transfer Protocol"
• For Host, enter in your specific host address that was provided to you by support
• For Port, you can leave it blank, or put in 22 (the default port)
• For Logon Type, select "Normal"
• For User, enter in your specific username that was provided to you by support
• For Password, enter in your specific password that was provided to you by support

Click on the "Connect" button. You may be presented with a "Unknown Host Key" window the first time you connect to your SFTP. If you do, click on the checkbox labeled "Always trust this host, add this key to the cache" and click the OK button to continue.

You should now see the following folders in the center bottom panel:

You have now successfully connected to the SFTP and can proceed to drag and drop your CSV files from your computer into the appropriate folder on the SFTP. Continue reading the existing sections for detailed information about the files the SFTP accepts.

Import Guidelines

The CSV file may be named however you want to name it, It’s the folder the file is in that determines where they get imported. The CSV files do need headers with the field names. By default, the service is setup to use the RFC-4180 standard with UTF-8 file encoding to import the file Quote-Comma delimited, but we can configure the service to use whatever delimiters you want (a popular alternative is Caret-Pipe).

• The service will not create task, project, matter, or client references - they must already exist when other objects reference them.
• Custom fields need to be named exactly as they appear within Agility Blue and must already exist before importing them. The service will not create fields that don't already exist.
• Blank entries are interpreted as NULL values by the system.
• Quotes around values are generally only required if there are quotes or commas as part of the value itself.
• Clients need to exist before importing new matters, so be sure to upload the clients file first. The system will queue files in the order they are uploaded and will perform imports one at a time.
• Multiple choice values must be separated with: \n

Reference Fields

You may import data into Reference fields using the following guide:

• Values can only be referenced by their unique Agility Blue ID.
• The IDs of the values being referenced must already exist within Agility Blue. If a value does not exist, it will be ignored.
• Duplicate values are removed prior to import.
• Existing values are replaced when updating data.
• Multiple values can be separated using the following string: "\n"

The sample image below shows a "Billing Contacts" custom field that references the contact object. The values 12 and 28 within the data represent the Agility Blue IDs for two different contacts that already exist within the system. The last three records showcase how multiple values can be imported with the "\n" separator.

The results of this import in Agility Blue for the billing contacts field:

Import Folder Structure

The $logs folder

The $logs folder is used by the system to place daily log files of activity. This is the place to go to check on the status of an import and to perform any troubleshooting. Any errors that ocurred during the import will be listed in the log file.

The $processed folder

The $processed folder is used by the system to place previously processed files. Import files are held for up to 7 days and then removed.

The clients folder

The clients folder is where you may place a CSV file that contains client records using the following configuration.

CSV Required fields:

• “Client Name” (Text-based field, Unique),

Optional fields:

• “Client Reference” (Text-based field. This field may be used as a client identifier to update existing clients if the values are unique).
• “Active” (Boolean-based field: Can accept True, False; Yes, No; or 1, 0. If not provided, the system defaults to TRUE).
• Any custom fields defined for the object.

An example clients CSV file can be found here

The matters folder

The matters folder is where you may place a CSV file that contains matter records using the following configuration. The service does not create clients that do not already exist, so it's important to import clients first so that they exist for the matters to reference.

CSV Required fields:

• “Matter Name” (Text-based, Unique).
• One of the following fields are needed as a reference back to an existing Client (If this field is not provided, or the client does not exist, an error will be logged):
  • "Client Name" (Text-based. Should match the "Client Name" field in Agility Blue to link up the matter with the associated client).
  • “Client Reference” (Text-based, Should match the “Client Reference” field in Agility Blue to link up the matter with the associated client. Using this field only works if the Client References exist and are unique).
  • “Client Id” (Number-based, Should match the “Client Id” field in Agility Blue to link up the matter with the associated client).

Optional fields:

• “Matter Reference” (Text-based).
• “Active” (Boolean-based field: Can accept True, False; Yes, No; or 1, 0. If not provided, the system defaults to TRUE).
• Any custom fields defined for the object.

An example matters CSV file can be found here

The contacts folder

The contacts folder is where you may place a CSV file that contains contact records.

CSV Required fields:

• “Email” (Text-based, Unique).

Optional fields:

• “First Name” (Text-based).
• “Last Name” (Text-based).
• Any custom fields defined for the object.

The medialogentries folder

The medialogentries folder is where you may place a CSV file that contains media log entry records.

CSV required fields to create new records:

• "Media Type Id" (Number-based) OR "Media Type Name" (Text-based)

Optional fields:

• "Client Id" (Number-based)
• "Client Name" (Text-based)
• "Client Reference" (Text-based)
• "Matter Id" (Number-based)
• "Matter Name" (Text-based)
• "Matter Reference" (Text-based)
• "Project Id" (Number-based)
• "Task Id" (Number-based)
• "From" (Text-based)
• "To" (Text-based)
• "Reference" (Text-based)
• "Location" (Text-based)
• "Custodian" (Text-based)
• "Notes" (Text-based)
• "Date" (Date-based)
• Any custom fields defined for the object.

Rules:

• Any combination of system fields may be used for the purposes of updating existing media log entries.
• If the media type needs to be updated on an existing media log entry, be sure to include the MediaTypeId field. If you only have the media type name, each media type id field should be empty or set to 0.
• To associate a media log entry with an existing client, provide one of the following fields:
  • Client Id (a system-generated id located in Agility Blue)
  • Client Name
  • Client Reference
• If the client needs to be updated on an existing media log entry, be sure to include the ClientId field. If you are only providing the client name or reference, each client id field should be empty or set to 0.
• To associate a media log entry with an existing matter, provide one of the following:
  • The Matter Id (a system-genereated id located in Agility Blue)
  • The Matter Name
  • The Matter Reference
  • The Client Id + The Matter Name (2 separate fields)
  • The Client Name + The Matter Name (2 separate fields)
  • The Client Reference + The Matter Name (2 separate fields)
  • The Client Id + The Matter Reference (2 separate fields)
  • The Client Name + The Matter Reference (2 separate fields)
  • The Client Reference + The Matter Reference (2 separate fields
• If the matter needs to be updated, be sure to include the MatterId field. If you are only providing the matter name or reference, each matter id field should be empty or set to 0.
• To associate a media log entry with an existing project, the Project Id field must be provided (a system-generated id in Agility Blue).
• To associate a media log entry with an existing task, the Task Id field must be provided (a system-generated id in Agility Blue).
• If multiple association levels are present (client, matter, project, and task fields), the order of priority is bottom-up, meaning the task fields will get the highest priority.
• If any provided association is not found or more than one instance is found for the association, the media log entry will not be created or updated and an error will be issued for the record.

The billingentries folder

The billingentries folder is where you may place a CSV file that contains billing entry records.

CSV required fields to create new records:

• "Billing Type Id" (Number-based), OR "Billing Type Billing Code" (Text-based), OR "Billing Type Description" (Text-based - The global billing type description, not an overriden profile description)
• "Unit Price" (Decimal-based)

Optional fields:

• "Client Id" (Number-based)
• "Client Name" (Text-based)
• "Client Reference" (Text-based)
• "Matter Id" (Number-based)
• "Matter Name" (Text-based)
• "Matter Reference" (Text-based)
• “Project Id” (Number-based).
• "Task Id" (Number-based).
• "Date" (Date-based).
• "Quantity" (Decimal-based).
• "Billing Type Description Override" (Text-based).
• "Narrative" (Text-based).
• "Notes" (Text-based).
• Any custom fields defined for the object.

Rules:

• If updating an existing billing entry, the "Billing Entry Id" field must be provided (a system-generated id in Agility Blue).
• If the billing type needs to be updated on an existing billing entry, be sure to include the Billing Type Id field. If you only have the billing type code or description, each billing type id field should be empty or set to 0.
• To associate a billing entry with an existing client, provide one of the following fields:
  • Client Id (a system-generated id located in Agility Blue)
  • Client Name
  • Client Reference
• If the client needs to be updated on an existing billing entry, be sure to include the Client Id field. If you are only providing the client name or reference, each client id field should be empty or set to 0.
• To associate a billing entry with an existing matter, provide one of the following:
  • The Matter Id (a system-genereated id located in Agility Blue)
  • The Matter Name
  • The Matter Reference
  • The Client Id + The Matter Name (2 separate fields)
  • The Client Name + The Matter Name (2 separate fields)
  • The Client Reference + The Matter Name (2 separate fields)
  • The Client Id + The Matter Reference (2 separate fields)
  • The Client Name + The Matter Reference (2 separate fields)
  • The Client Reference + The Matter Reference (2 separate fields
• If the matter needs to be updated, be sure to include the Matter Id field. If you are only providing the matter name or reference, each matter id field should be empty or set to 0.
• To associate a billing entry with an existing project, the Project Id field must be provided (a system-generated id in Agility Blue).
• To associate a billing entry with an existing task, the Task Id field must be provided (a system-generated id in Agility Blue).
• If multiple association levels are present (client, matter, project, and task fields), the order of priority is bottom-up, meaning the task fields will get the highest priority.
• If any provided association is not found or more than one instance is found for the association, the billing entry will not be created or updated and an error will be issued for the record.

The customobjects folder

The customobjects folder is where you may place a CSV file that contains custom object entry records.

CSV Required fields:

• “Object Id” (Number-based).

Optional fields:

• “Id” (Number-based, Unique).
• Any custom fields defined for the object.

Rules:

• The required "Object Id" field is used to determine which custom object the data belongs to. This value should be the same for every record in the CSV (do not mix and match object ids). To locate your object id within the app, head into your workspace, click on the settings gear icon on the top right navigation bar, and choose "Objects". Ensure that the "Id" field is in view - this will be the value you will need to use.
• This folder is only for creating/updating custom object entries. Do not try to use system object ids - it won't work.
• If updating existing custom object entries, the "Id" field must be provided (a system-generated id in Agility Blue). No other fields may be used to identify an existing custom object entry at this time.

An example custom object custodians CSV file can be found here.