Sample table

Desktop User Guides > Professional > Interview scripting > Sample management > Sample management facilities > Sample table

Sample table

The sample table is a database table in a SQL Server database that stores sample records. Each sample record holds details about a prospective respondent such as ID, password, age, gender, and so on. These individual items of information are known as sample record fields.

Before you can use sample management, you must set up the sample table in a SQL Server database. The simplest way to create a sample table is to use the Participants activity in UNICOM Intelligence Interviewer - Server Admin to load some sample records. If you choose to load the records into a new table, the load process will create the table for you.

If possible, create a separate table for each project. You can create a sample table that is shared by a number of projects, but you then run the risk of overwriting a project's data due to the interview serial number field being populated on one project and then being used in the second project. For example, if a record is used on project A and generates an interview with serial number 100, the number 100 is written back into the Serial field. If the record is then used on Project B, the interviewing program will read the serial number for the new interview from the sample record and will create an interview with that serial number. If there is already an interview with that serial number, its data will be overwritten. To avoid this, you should always prepopulate the Serial field in shared sample tables.

When you activate a project, you specify whether it uses sample management. For projects that use sample management, you must specify the server, database, and database table that correspond to the sample table and the columns within the database table that correspond to the sample record fields. This maps the columns in the database table to the sample record fields that you can access with the sample management script. You must make sure that you map all of the fields that you want to update with new information or that you want to access in the script.

You can map columns of the nchar, ntext, nvarchar, bit, smallint, int, and datetime SQL Server database data types. (You can also map columns of the varchar SQL Server database data type, however the Unicode-compatible nvarchar is preferable, especially for new databases or if you need to support extended character sets, or might need to in the future.) You cannot map columns of any other SQL Server database data type.

It is strongly recommended that you initialize all of the fields in the sample table with a default value. If you do not initialize a field, it will be transferred to the sample management script with a zero value if it is a numeric field or an empty string if it is an alphanumeric field.

Required fields

The sample table must contain the columns shown in the following table. The column names must appear exactly as shown (beginning with a capital letter followed by lowercase letters) and might not be shown in all uppercase or lowercase letters (Id, not ID or id). The Id field should be an alphanumeric data type and must not contain single quotation marks (').

Project does not use telephone interviewing

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

Default value: None

The sample record ID which uniquely identifies each sample record.

Data type and length: Text(64)

Null permitted: No

Primary key: Yes

Default value: None

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

Default value: None

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

Default value: None

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.

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: Null

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.

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 user ID of the 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

Project uses telephone interviewing

Column

Data type and length

Null permitted

Primary key

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

Default value: None

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 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.

Data type and length: Text(2000)

Null permitted: Yes

Primary key: No

Default value:

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

Default value:

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 might 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

Default value: None

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 might 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

Default value: None

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 returns a new value for Internal Dialer Flag for the number. After dialing, this field is updated with the new value, and this value is 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

Default value:

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

Default value:

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 can 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

Default value:

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 might 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

Default value:

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 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

Default value:

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

Default value:

ReturnTime

Data type and length: Date

Null permitted: Yes

Primary key: No

Default value:

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 Time zone management for more information.

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value:

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 user ID 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

Project uses personal interviewing

Column

Data type and length: Data type and length

Null permitted: Null permitted

Primary key: Primary key

Default value: Default value

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 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

Default value:

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

Default value:

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

Default value:

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 can 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

Data type and length: DateTime

Null permitted: Yes

Primary key: No

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

Defines the participant record expiration date and time. For example, a project might 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).

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 can 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

Default value:

PreviousQueue

Displaying the value of PreviousQueue can be useful to interviewers as it might 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

Default value:

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 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

Default value:

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)

To 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

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

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 Time zone management for more information.

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value:

UserId

The user ID of the 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

Data types

The data types shown in this section are those that the UNICOM Intelligence Data Model uses. When the table is created in the sample database, the Activate component converts the data types into the corresponding data types in the database application (see Data type mapping for columns in sample tables). You can check the column data types by opening the table in the database application and can change the data types if they are not what you want.

Optional fields

The sample table can contain additional fields for storing other data, such as the respondent's name, email address, gender, and age. You can use these fields to set quotas for the number of respondents to be interviewed with specific sets of characteristics.

When sample records are used in telephone or personal interviewing projects, you can use the Comment field to store comments or other information to be displayed when the record is presented to the interviewer for calling. The interviewer can add to the comments if appropriate. The Comments field must be big enough to store a useful amount of text, and must be defined as being of SQL type nvarchar.

Authentication fields

If the project allows inbound calling, where participants complete the questionnaires themselves, you define which of the sample record fields are to be used for validation during authentication when you activate the project. Generally, you use just one or two fields for authentication (for example, Id or Id and Password).

Who can access the sample data?

By default, all users can access all data in all sample tables and sample databases on all servers. You can restrict access to the sample data by specifying your requirements in the SampleMgtGUI.Config.xml file. You might want to do this if you have different sample databases for different customers, and you want to prevent users who work on one customer's projects from having access to the sample data for other customers. For more information, see Controlling access to project files.

Another privacy option to consider is read-only fields; that is, data that sample management can read but not update. This type of access control is generally used when you are reading sample data from an existing database that is not part of your UNICOM Intelligence system. For more information, see Using an existing database as a sample database.