Uploading participant records

Professional > Activating questionnaires > Activation templates > Activate Current Project - Participants settings > Uploading participant records

This topic explains how to upload participant records by using the Activate dialog box.

Note You can upload participant records only if your UNICOM Intelligence Interviewer - Server Admin user profile is assigned the Can upload participants activity feature in UNICOM Intelligence Interviewer - Server Admin. For more information, see Assigning users or roles to activitiesUNICOM Intelligence Interviewer - Server Admin.

Using this method to load records is generally the same as loading records using the Participants activity in UNICOM Intelligence Interviewer - Server Admin, but there are some restrictions that apply to the participants text file. The Participants activity accepts any field names in the participants text file, and provides facilities for mapping fields in the file to the field names that the sample management system requires in the database, but the Activate component does not. This means that all fields that are in the text file, and that are required sample management fields, must have the correct names in the text file. For example, the record Id must be stored in a field called Id. If the field name is RecNum, you must change this in the header line of the text file before you upload records.

Make sure that fields (especially the authentication fields) do not contain leading or trailing spaces, because extra spaces cause authentication to fail.

Field names that cannot be used

Microsoft SQL Server reserved words cannot be used as field names. The current list of SQL Server reserved words is as follows:

ADD, EXTERNAL, PROCEDURE, ALL, FETCH, PUBLIC, ALTER, FILE, RAISERROR, AND, FILLFACTOR, READ, ANY, FOR, READTEXT, AS, FOREIGN, RECONFIGURE, ASC, FREETEXT, REFERENCES, AUTHORIZATION, FREETEXTTABLE, REPLICATION, BACKUP, FROM, RESTORE, BEGIN, FULL, RESTRICT, BETWEEN, FUNCTION, RETURN, BREAK, GOTO, REVERT, BROWSE, GRANT, REVOKE, BULK, GROUP, RIGHT, BY, HAVING, ROLLBACK, CASCADE, HOLDLOCK, ROWCOUNT, CASE, IDENTITY, ROWGUIDCOL, CHECK, IDENTITY_INSERT, RULE, CHECKPOINT, IDENTITYCOL, SAVE, CLOSE, IF, SCHEMA, CLUSTERED, IN, SECURITYAUDIT, COALESCE, INDEX, SELECT, COLLATE, INNER, SEMANTICKEYPHRASETABLE, COLUMN, INSERT, SEMANTICSIMILARITYDETAILSTABLE, COMMIT, INTERSECT, SEMANTICSIMILARITYTABLE, COMPUTE, INTO, SESSION_USER, CONSTRAINT, IS, SET, CONTAINS, JOIN, SETUSER, CONTAINSTABLE, KEY, SHUTDOWN, CONTINUE, KILL, SOME, CONVERT, LEFT, STATISTICS, CREATE, LIKE, SYSTEM_USER, CROSS, LINENO, TABLE, CURRENT, LOAD, TABLESAMPLE, CURRENT_DATE, MERGE, TEXTSIZE, CURRENT_TIME, NATIONAL, THEN, CURRENT_TIMESTAMP, NOCHECK, TO, CURRENT_USER, NONCLUSTERED, TOP, CURSOR, NOT, TRAN, DATABASE, NULL, TRANSACTION, DBCC, NULLIF, TRIGGER, DEALLOCATE, OF, TRUNCATE, DECLARE, OFF, TRY_CONVERT, DEFAULT, OFFSETS, TSEQUAL, DELETE, ON, UNION, DENY, OPEN, UNIQUE, DESC, OPENDATASOURCE, UNPIVOT, DISK, OPENQUERY, UPDATE, DISTINCT, OPENROWSET, UPDATETEXT, DISTRIBUTED, OPENXML, USE, DOUBLE, OPTION, USER, DROP, OR, VALUES, DUMP, ORDER, VARYING, ELSE, OUTER, VIEW, END, OVER, WAITFOR, ERRLVL, PERCENT, WHEN, ESCAPE, PIVOT, WHERE, EXCEPT, PLAN, WHILE, EXEC, PRECISION, WITH, EXECUTE, PRIMARY, WITHIN, GROUP, EXISTS, PRINT, WRITETEXT, EXIT, PROC

Required fields for Web interviews

Active

Set to 1 while the sample management functions are running; that is, while the record is in the ACTIVE queue.

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

ActivityStartTime

The StartTime of latest record in the history table for a specific sample.

The session engine will update this value for the latest activity on the sample record.

Data type and length: DateTime

Null permitted: Yes

Primary key: No

Default value: Null

CallOutcome

The contact outcome (return) code for the previous contact to this record.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

The sample record ID which uniquely identifies each sample record.

Data type and length: Text(64)

Null permitted: No

Primary key: Yes

InterviewMode

Written to by the sample script to record which interview mode was used for a particular interview: is set to Web for an inbound self-completion interview, Phone for outbound telephone interviewing, or Personal for personal interviewing. In projects that allow a mix of inbound and outbound calling, the sample management script will set this field.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Queue

Names the queue in which the record is currently held.

Data type and length: Text(255)

Null permitted: Yes

Primary key: No

Default value: FRESH

ReturnTime

The time at which the record was returned to sample management. This allows you to specify the amount of time that must elapse between repeat contacts to records whose interviews timed out or were stopped.

Data type and length: Date

Null permitted: Yes

Primary key: No

Screener

Identifies which respondents are the suitable candidates for the current survey. Screener questions are designed to filter respondents. If a respondent answers do not meet the Screener criteria, the respondent is not allowed to continue the survey, and the Screener field is recorded as Failed. If respondent answers meet the Screener criteria, they are allowed to continue the survey, and the Screener field is recorded as Passed.

This field can be set using the following IOM script in routing (it is the data source for the Incidence report).

Passed Screener:
IOM.SampleRecord.Item["Screener
"].Value = "Passed"
Failed Screener:
IOM.SampleRecord.Item["Screener
"].Value = "Failed"
IOM.Terminate(Signals.sigFailed
Screener, True)

In order to accurately calculate the project incidence, the Screener field is added to the sample table. The field is updated during the survey with three values – Null, Passed, and Failed. The sum of Passed is the incidence numerator; the sum of Passed and Failed is the incidence denominator. The incidence report is generated using TOM based on the data source, sample table, and sample history table.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: Null

Serial

The unique serial number that UNICOM Intelligence Interviewer - Server Admin assigns to each respondent's case data. Generally, this serial number is not the same as the sample record ID. When a respondent restarts an interview, UNICOM Intelligence Interviewer - Server Admin uses the serial number to retrieve the respondent's case data record and to display the responses (stored in the case data record) that the respondent has already given.

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

Test

Set to 1 if the record is test data, or 0 if it is real data (also known as live data). This column is used by the Interviewing activity to restrict the type of data that appears in phone reports. If the value is Null, the Interviewing activity will treat the record as if it is both real and test data.

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: Null

UserId

The UserId of latest record in the history table for a specific sample.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: NULL

Required fields for telephone interviews

Active

Set to 1 while the sample management functions are running; that is, while the record is in the ACTIVE queue.

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

ActivityStartTime

The StartTime of latest record in the history table for a specific sample.

The session engine will update this value for the latest activity on the sample record.

Data type and length: DateTime

Null permitted: Yes

Primary key: No

Default value: Null

AppointmentTime

The time in UTC at which the respondent asked to be called.

Data type and length: Date

Null permitted: Yes

Primary key: No

AppointmentTryCount

The number of calls or contacts made to this record after an appointment was set. When sample records are uploaded into the sample table, a non-null default value should be specified otherwise errors will occur during the upload.

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

Audit

Records changes made to other fields (except Comments) in the record.

This field was new in UNICOM Intelligence Interviewer - Server Admin 4.0. In earlier versions, these changes were stored in the Comments field.

If you reuse a pre-v4.0 sample table that contains a Comments field of SQL type ntext, the Audit field is created as nvarchar(2000) instead. This is due to an issue in the Microsoft OLE DB consumer templates that prevents a table containing two ntext columns.

Data type and length: Text(2000) but see Notes for more information

Null permitted: Yes

Primary key: No

CallOutcome

The call outcome (return) code for the previous call to this record.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

CallRecordingsCount

The number of call recordings for this record. Records loaded with this field empty have this field set to Null in the sample table.

Call recording is an option when a telephone interviewing project uses an autodialer.

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0 for telephone projects, otherwise Null

Comments

Additional information about the participant. Interviewers may update this field when they call or contact the participant.

In pre-v4.0 sample tables, the Comments field is created as ntext(16). If you reuse a pre-v4.0 sample table that contains a Comments field of type ntext, its data type remains unchanged and the Audit field is created as nvarchar(2000) instead. This is due to an issue in the Microsoft OLE DB consumer templates that prevents a table containing two ntext columns.

The standard multimode sample management scripts display records with comments before dialing so that the interviewer can read the comments before talking to the participant.

Data type and length: Text(2000) but see Notes for more information.

Null permitted: Yes

Primary key: No

ConnectCount

The number of times that the number has been connected. This field is updated when a sample is auto-dialed and connected.

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

DayPart

Records the call count for each specific day part.

For example, assume there are two day parts named aa, ab. The value for this field will be aa1|ab2 (or aa1). This means the sample was used to call one time in aa time range and two times in ab time range. If the sample has not yet been used, the value of this field is null.

Data type and length: Text(128)

Null permitted: Yes

Primary key: No

Default value: Null

ExpirationTime

Defines the participant record expiration date and time. For example, a project may dictate that participant records can only be called or contacted within a specific date range.

Expired records are not available for dialing (except for retrieving appointments).

Data type and length: DateTime

Null permitted: Yes

Primary key: No

Default value: 2099–12–31 23:59:000

The sample record ID that uniquely identifies each sample record.

Data type and length: Text(64)

Null permitted: No

Primary key: Yes

InternalDialerFlags

Used in conjunction with a 3rd party dialer. In full predictive mode, this field should accompany all numbers dialed commands. It is set to an initial value by the CATI system for the first dialer (for a different dialer, the initial value can be different).

IBM SPSS Dialer will return a new value for Internal Dialer Flag for the number. After dialing, this field will be updated with the new value, and this value will be permanently set with the sample record and passed through for all subsequent dialing attempts.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: NULL

InterviewMode

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

NoAnswerCount

How many times this sample has been called and received NoAnswer. This field is updated when a sample is dialed and returned with a call outcome of NoAnswer

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

PhoneNumber

Must contain a phone number if the record is to be used for telephone interviewing.

If the project uses an autodialer, phone numbers that start with a + sign will have that character replaced by the InternationalAccessCode defined in DPM. + signs preceded by white space or other characters are not replaced.

If the project allows inbound calling, you can add a question to the script that asks respondents to enter contact numbers, and then update the sample records with this information.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

PreviousInterviewerID

The name of the interviewer who contacted the participant. This allows appointments to be returned to the previous interviewer if the current time is before the AppointmentMarginAfter interval has passed. Appointments that are not kept within this period may be passed to any interviewer.

When interviews are reviewed, this field is updated with the name of the reviewer.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

PreviousQueue

The name of the queue in which the record was previously held. When records are displayed for interviewers, the record's current queue is always shown as ACTIVE because the record has been selected for interviewing.

Displaying the value of PreviousQueue can be useful to interviewers as it may provide additional information about the record's calling history. For example, if PreviousQueue is FRESH, the interviewer knows the record has not been called before, whereas if PreviousQueue is APPOINTMENT, they know that the respondent has already been contacted and has asked to be called back to be interviewed.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Queue

Names the queue in which the record is currently held.

When replicate identifiers are defined in the queue field for specific records, those records can then be used to create sample/participant record subsets.

See the topic Queues for more information.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: FRESH

RecallTime

The time in UTC that was set as the callback time for appointments that are set automatically by the sample management script.

Data type and length: Date

Null permitted: Yes

Primary key: No

RequiresManualDial

Indicates that the record must be manually dialed. The sample management script will set AutoDial=0 for these records.

The feature will not work if RequiresManualDial is not defined in the participants table

Data type and length: Long

Null permitted: Yes

Primary key: No

ReturnTime

Data type and length: Date

Null permitted: Yes

Primary key: No

Serial

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

Screener

This field can be set using the following IOM script in routing (it is the data source for the Incidence report).

Passed Screener:

IOM.SampleRecord.Item
["Screener"].Value =
"Passed"

Failed Screener:

IOM.SampleRecord.Item
["Screener"].Value =
"Failed"

IOM.Terminate(Signals.
sigFailedScreener, True)

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: Null

SortId

A random value that can be used for sorting records prior to selection. (Appointments and recalls are not affected by this property as they are always sorted in ascending date/time order.) The Participants activity can initialize this field with a random value when uploading records. If records are uploaded in batches, each record in the sample table receives a new random number, not just those being uploaded in the current batch. See “Naming the Database Server, Sample Database, and Sample Table” in the UNICOM Intelligence Interviewer - Server Admin User's Guide for details.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: Null

Test

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: Null

TimeZone

The respondent's timezone. This is used in the setting of appointments to ensure that any time differences between the respondent's and interviewer's locations are taken into account when the record is presented for recalling. See the Time zone management for more information.

Data type and length: Long

Null permitted: Yes

Primary key: No

TrunkGroup

If sample records are used in telephone interviewing projects, you can use the TrunkGroup field to specify which trunk group of the dialer will be used for dialing the sample record. If you want the dialer to automatically select the trunk group, the field should be set to NULL or empty.

Data type and length: Long or Text(64)

Null permitted: Yes

Primary key: No

Default value: NULL

TryCount

The number of calls made to this record. When sample records are uploaded into the sample table, a non-null default value should be specified otherwise errors will occur during the upload.

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

UserId

The UserId of latest record in the history table for a specific sample.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: NULL

Required fields for personal interviews

Active

Set to 1 while the sample management functions are running; that is, while the record is in the ACTIVE queue.

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

ActivityStartTime

The StartTime of latest record in the history table for a specific sample.

The session engine will update this value for the latest activity on the sample record.

Data type and length: DateTime

Null permitted: Yes

Primary key: No

Default value: Null

AppointmentTime

The time in UTC at which the respondent asked to be called.

For disconnected samples, UNICOM Intelligence Interviewer - Offline for Windows needs to update this value after the interviewer sets up an appointment time with the respondent.

Data type and length: Date

Null permitted: Yes

Primary key: No

AssignedInterviewerId

The field manager sets this value as the interviewer ID for distributing participant records.

Data type and length: Text(64)

Null permitted: No

Primary key: Yes

Audit

Records changes made to other fields (except Comments) in the record.

Data type and length: Text(2000) but see Notes for more information

Null permitted: Yes

Primary key: No

CallOutcome

The contact outcome (return) code for the previous call to this record.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Comments

Additional information about the participant. Interviewers may update this field when they call or contact the participant.

The standard multimode sample management scripts display records with comments before dialing so that the interviewer can read the comments before talking to the participant.

Data type and length: Text(2000) but see Notes for more information.

Null permitted: Yes

Primary key: No

DistributedTo

The interviewer ID or IDs to which the participant is distributed.

Data type and length: Text(64)

Null permitted: No

Primary key: Yes

ExpirationTime

Defines the participant record expiration date and time. For example, a project may dictate that participant records can only be called or contacted within a specific date range.

Expired records are not available for dialing (except for retrieving appointments).

Data type and length: DateTime

Null permitted: Yes

Primary key: No

Default value: 2099–12–31 23:59:000

The sample record ID that uniquely identifies each sample record.

Data type and length: Text(64)

Null permitted: No

Primary key: Yes

InterviewMode

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

PreviousInterviewerID

The name of the interviewer who last contacted the participant. This allows appointments to be returned to the previous interviewer if the current time is before the AppointmentMarginAfter interval has passed. Appointments that are not kept within this period may be passed to any interviewer.

When interviews are reviewed, this field is updated with the name of the reviewer.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

PreviousQueue

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Queue

Names the queue in which the record is currently held.

When replicate identifiers are defined in the queue field for specific records, those records can then be used to create sample/participant record subsets.

See the topic Queues for more information.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: FRESH

ReturnTime

Data type and length: Date

Null permitted: Yes

Primary key: No

Screener

This field can be set using the following IOM script in routing (it is the data source for the Incidence report).

Passed Screener:
IOM.SampleRecord.Item
["Screener"].Value =
"Passed"
Failed Screener:
IOM.SampleRecord.Item
["Screener"].Value =
"Failed"
IOM.Terminate(Signals.
sigFailedScreener, True)

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: Null

Serial

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

SortId

A random value that can be used for sorting records prior to selection. (Appointments and recalls are not affected by this property as they are always sorted in ascending date/time order.) This value can prove useful in personal interviewing when planning the contacts for the day as SortIds can be manually edited and then used for sorting in the Interviewer window to show the visit order. The Participants activity can initialize this field with a random value when uploading records. If records are uploaded in batches, each record in the sample table receives a new random number, not just those being uploaded in the current batch. See “Naming the Database Server, Sample Database, and Sample Table” in the UNICOM Intelligence Interviewer - Server Admin User's Guide for details.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: Null

Test

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: Null

TimeZone

Data type and length: Long

Null permitted: Yes

Primary key: No

UserId

The UserId of latest record in the history table for a specific sample.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: NULL

Converting the data types

The data types shown above are those that the UNICOM Intelligence Data Model uses. When the table is created in the sample database, the Activate component converts these data types into the corresponding data types in the database application you are using. (For further details about the mapping process, open the UNICOM Intelligence Developer Documentation Library documentation, and then search for “Data Type Mapping for Columns in Sample Tables”.) You can check the column data types by opening the table in your database application and can change the data types if they are not exactly what you want. Refer to your database application's documentation for information on changing data types.

Your participants text file does not need to contain information for all the required columns, as many of the columns are used only internally by the sample management system and will only contain information once a participant has been called. As a minimum, you must supply a value in your text file for the Id column. For telephone interviewing projects, you should provide a value for the PhoneNumber column, and if you have participants in more than one time zone you might want to provide a value for the TimeZone column.

Once you have told Activate which text file to use, it scans the file and decides how it will load the data into the sample table. It does this by comparing the field names in the participants text file with the columns names that need to exist in the sample table. If there are additional fields in the text file, new columns will be created in the sample table to hold this data. The fields table displays a summary of what it will do and lets you change it if this is necessary. Typically, you might change the fields that are used for authenticating inbound callers.

Columns

Available

Fields to make available to the sample management script. Cancel any that are not used by the sample management script (you cannot cancel required fields).

Fields whose contents are used only by the interview script become available during interviews and need not be selected here.

Authentication

Fields to use for authenticating inbound callers taking Web interviews. Choose the fields you want to use and cancel any you do not.

If you need to be able to select specific participant records, you should select the Id field because this is a key to the database and is guaranteed to contain a unique value for each record.

If you authenticate on a field that may contain non-unique values, the sample management system will select the first record whose value in that column matches the values specified in the sample management script.

Field

Field names. You cannot modify these settings.

Default

Default values to be inserted into empty fields. Fields with no default values will have a null value. You may specify your own defaults as long as the values are consistent with the fields' data types and lengths.

Type

The type of data in the field. You cannot change the data type of required fields.

Length

The number of characters that can be held in a text field. You cannot change the length of required fields.

Uploading participant records

1 Select Upload (located under the Participants node).

2 Select Upload participants and click Browse.

This opens the Specify Participants dialog box.

3 In Delimiter, select the character that separates the fields in each record. The default is a comma. If you pick a different character, this becomes the default the next time you activate a project.

4 Click Browse and select the .txt or .csv file you want to upload.

5 The upload process automatically randomizes records as it loads them. Click Re-randomize all participant records during import if you want to cancel the randomization process.

6 Click OK to close the dialog box.

Activate checks the participants text file and displays the fields in the fields table.

7 Make whatever changes are appropriate in this table.

8 Use the Fields and Script settings to select the sample database and table you want to use, and the sample management script that will control access to the participant records.

For more information, see Participants - Fields and Participants - Script.