In order to use the Dynamic Lookups Module, you need to provide your RefTracker system with the data you’d like to be looked up. It is important to first define what data you’d like to import. You can discuss these details with your RefTracker Support Representative, and your IT department to determine what information you’d like made available to your system. It’s important to note that the data you upload to your RefTracker system’s Dynamic Lookup Module doesn’t get imported into the same database that RefTracker exists on – however, once the lookup data is imported into a question, it will be.

You can choose to import any data necessary to assist in your workflow. You can upload more than one type of data file, and we can work with you to update your data at a rate most convenient to you.

The following steps will help guide the file creation and import procedure to provide this file for your RefTracker instance.

Before you Begin

The most common lookup definition is that of a client; common fields include an email address, first name, last name, and possibly location or department. Each client record imported needs to include one unique field to act as the lookup item. In many scenarios, this is the email address field, however, some prefer using the name as the unique lookup item. Either way, this field will be required in your data imports; if the required field is not in the uploaded file, it will not be imported into the database.

In general, the import can include as much information as necessary that pertains to that record. Not all information imported needs to be used in forms. You’ll be specifying Dynamic Lookup Definitions to determine where you’d like the data you are importing mapped to.

Creating the File

File Type and Naming Convention

The file extensions acceptable are CSV and TXT files. If you choose the TXT option, you can choose the delimiter to use, but you cannot have any spaces within an empty delimiter. We’d strongly suggest using a TXT file name to upload records with, as CSV files have comma limitations (for titles, departments, practice names, etc). It is not recommended that you use the CSV file option unless you are certain that the fields you are uploading will not contain commas.

There is a naming convention to the file uploaded. Please use the following: CLIENTNAME_Client_YYYYMMDD, where CLIENTNAME=predetermined client identifier, the keyword ‘Client’ within underscores, YYYY=4 character year, MM=2 character month and DD=2 character day. All files uploaded are retained after processed for troubleshooting purposes.

File Content

It’s recommended that unless you have a very large data set to manage (greater than 250,000 records), you truncate the table beforehand. This can be done with the DELETE_ALL_RECORDS directive set as the very first line in the file submitted. If using a CSV import, no commas can follow this first line.

You can upload as much information as you’d like, but each record must begin with the type of record (N=new record, U=update record, D=delete record) and have a unique numerical ID associated with it.

Record Type|Unique ID|First Name|Last Name | Email | Phone
A line break ends the record.
Other fields can be added to this record – order of these records is important in the way the file gets imported. Please clearly define with your support team contact what data needs to be loaded with what information.

For Your IT Department

To upload to our server, your IT department will need to have ports 989 and 990 open.

We will create you an account to upload to – you will need to connect via FTPS (not SFTP), and send you the credentials in a different email.

Many choose to automate the creation and upload, and I can offer some suggestions if you’d like. Popular, command line applications include WinSCP and FileZilla – we can customize a script for you if you’d like to get you started.

You can have more than one upload. Typically, there is one upload for clients, and one upload for non-client related information, such as a client-matter number.