How to make the most of Outside List Processing - Part 1

Research Tracker® II's Outside List Processing feature is a terrific time-saver when recruiting from a client-provided list.  Some advantages of Outside List Processing are:

1)  Eliminate the need to keep job information in two separate systems depending on whether recruiting was done from the database or from an outside list.  Both database and outside list jobs can be processed and archived in Research Tracker® II.

2)  Quickly determine which, if any, respondents on the client list are also in your database.    No need to do lengthy cross-checking of names.  Use the "Check Outside List" feature to identify any respondents on the list who already exist in the database.

3)  Use any of Research Tracker® II's printed recruiting lists.  Recruiters will be working with a printed format they are familiar with.

4)  Easily flag participants for future studies.  If participants in the outside list job want to be added to your database, simply eliminate the Ineligible code before closing the job.  Those who cannot be used again will remain in the database, but will not be used for subsequent jobs.

In order to use Outside List Processing, the list needs to be a spreadsheet.  In some cases, this will require converting from another file type to an Excel spreadsheet. Also, the data fields must be correctly labeled and the data must be in a specified format.  This may require some additional preprocessing.

Creating an Excel spreadsheet from another file type.

Excel is a versatile application.  It can directly read data in a variety of file types that are determined by the 3-letter file extension.  For example, if the outside list name is called patients.txt, the .txt file extension means "text file".  Excel can directly read the following file types:

.xls or .xlw Excel files
.htm or .html Web pages
.txt or .csv Text files
.wk# - Lotus files (# refers to the version)
.wq1 Quattro Pro files
.wks MS Works files
.dbf dBase files

Certain other types of files, for example Access database files (.mdb) and Word documents (.doc), cannot be read directly by Excel.  However, an .mdb file can be exported as an Excel spreadsheet or text file and a .doc file can be exported as a text file.  So, if you receive a file that Excel can't read, ask the client or list source to export the file as a delimited text or spreadsheet file.  Then, your Excel program will be able to read it.

Preprocessing outside list data for loading.

Once the outside list can be opened as an Excel spreadsheet, you will need to re-label field names or add field names to conform with Research Tracker® II's conventions.

    Delete Columns of Extraneous Data and/or Hidden Columns
    Check the columns of data in the spreadsheet.  Any data that will not be loaded should be deleted.  Also check the spreadsheet for hidden columns.  Unhide and delete any data that will not be loaded.

    Field Names
    Row 1 of the spreadsheet must contain field names used by Research Tracker® II.  Check the Research Tracker® II Reference Manual for details.  (see Chapter 6 Outside List Processing).  For example, the entry for the field containing first name must be labeled "fname".   Make sure each column of data has the correct field name in Row 1.

    Required Formatting
    Check the Required Formatting for each field.  Refer to the Research Tracker® II Reference Manual for details.  In some cases, preprocessing of the data may be required. 

      How to separate first and last names in the same field

      Look for a common delimiter in the name field.  This is a character or space that is consistently used in each name to distinguish between first and last name.  For example, if the names appear as...
      Smith, Barbara
      Jones, Nancy
      Johnson, Bob common delimiter is the comma, a second delimiter is the space between the comma and the first name

      Insert a column to the right of the existing column containing the combined first and last names

      Select the column containing the combined first and last names

      Click Data, then select Text to Columns

      Follow the steps outlined in the Text to Columns Wizard

         1.  You should see a message indicating "Text Wizard has determined your data is delimited".  Click Next
         2.  Select the Delimiter(s) in your data.  In the above example, click both Comma and Space.  Keep the default option of "Treat consecutive delimiters as one."  The preview screen should now display first and last names in two columns instead of one.  Click Next
         3.  Set data format.  For text entries such as name, keep the default "General" format.  Click Finish

      The old name column should now hold last name, and the next column should now hold first names.

      Rename row 1 with the correct Research Tracker® II field names, lname and fname.

      Remember to save the spreadsheet.

Go to "How to Make the Most of Outside List Processing - Part 2" for more information.