Sample table

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

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

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

ActivityStartTime

Data type and length: DateTime

Null permitted: Yes

Primary key: No

Default value: Null

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.

CallOutcome

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: None

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

Data type and length: Text(64)

Null permitted: No

Primary key: Yes

Default value: None

The sample record ID which uniquely identifies each sample record.

InterviewMode

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: None

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.

Queue

Data type and length: Text(255)

Null permitted: Yes

Primary key: No

Default value: FRESH

Names the queue in which the record is currently held.

ReturnTime

Data type and length: Date

Null permitted: Yes

Primary key: No

Default value: None

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.

Screener

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: Null

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.

Serial

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

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.

Test

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: Null

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.

UserId

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: NULL

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

Project uses telephone interviewing

Column

Data type and length

Null permitted

Primary key

Default value

Notes

Active

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

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

ActivityStartTime

Data type and length: DateTime

Null permitted: Yes

Primary key: No

Default value: Null

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.

AppointmentTime

Data type and length: Date

Null permitted: Yes

Primary key: No

Default value: None

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

AppointmentTryCount

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

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.

Audit

Data type and length: Text(2000)

Null permitted: Yes

Primary key: No

Default value:

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.

CallOutcome

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value:

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

CallRecordingsCount

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0 for telephone projects, otherwise Null

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.

Comments

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

Null permitted: Yes

Primary key: No

Default value: None

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.

ConnectCount

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

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

DayPart

Data type and length: Text(128)

Null permitted: Yes

Primary key: No

Default value: Null

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.

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 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: Text(64)

Null permitted: No

Primary key: Yes

Default value: None

The sample record ID that uniquely identifies each sample record.

InternalDialerFlags

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: NULL

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.

InterviewMode

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value:

NoAnswerCount

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

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

PhoneNumber

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value:

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.

PreviousInterviewerID

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value:

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.

PreviousQueue

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value:

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.

Queue

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: FRESH

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.

RecallTime

Data type and length: Date

Null permitted: Yes

Primary key: No

Default value:

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

RequiresManualDial

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value:

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

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

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.sigFailedScreener, True)

SortId

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: Null

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.

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

Default value:

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.

TrunkGroup

Data type and length: Long or Text(64)

Null permitted: Yes

Primary key: No

Default value: NULL

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.

TryCount

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

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.

UserId

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: NULL

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

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

Notes

Active

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

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

ActivityStartTime

Data type and length: DateTime

Null permitted: Yes

Primary key: No

Default value: Null

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.

AppointmentTime

Data type and length: Date

Null permitted: Yes

Primary key: No

Default value:

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.

AssignedInterviewerId

Data type and length: Text(64)

Null permitted: No

Primary key: Yes

Default value:

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

Audit

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

Null permitted: Yes

Primary key: No

Default value:

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

CallOutcome

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value:

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

Comments

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

Null permitted: Yes

Primary key: No

Default value:

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.

DistributedTo

Data type and length: Text(64)

Null permitted: No

Primary key: Yes

Default value:

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

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 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: Text(64)

Null permitted: No

Primary key: Yes

Default value:

The sample record ID that uniquely identifies each sample record.

InterviewMode

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value:

PreviousInterviewerID

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value:

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.

PreviousQueue

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value:

Queue

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: FRESH

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.

ReturnTime

Data type and length: Date

Null permitted: Yes

Primary key: No

Default value:

Screener

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.

sigFailedScreener, True)

Serial

Data type and length: Long

Null permitted: Yes

Primary key: No

Default value: 0

SortId

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: Null

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.

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

Default value:

UserId

Data type and length: Text(64)

Null permitted: Yes

Primary key: No

Default value: NULL

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

The data types shown above are those that the UNICOM Intelligence Data Model. 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 (see Data type mapping for columns in sample tables for further details about the mapping process). 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.

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 may 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. See Using an existing database as a sample database for more information.