CDISC ODM

OpenClinica Data Schema (table.column_name)

OpenClinica CRF Spreadsheet

Example Values (Comments)

Added to 3.1?

ODM

 

 

 

 

FileOID

dataset.name-D-creationDateTime

 

Intervention_DatasetD20100924143323+0300

 

CreationDateTime

 

 

2010-09-24T14:33:23+03:00

 

FileType

 

 

Snapshot (generated automatically)

 

ODMVersion

 

 

1.3

 

xsi:schemaLocation

 

 

http://www.cdisc.org/ns/odm/v1.3 OpenClinica-ODM1-3-0-OC2-0.xsd

 

Study

 

 

 

 

OID

study.oc_oid

 

S_DEMO123

 

StudyName

study.name

 

Demonstration Study

 

StudyDescription

study.summary

 

This study is used for demonstrational purposes

 

ProtocolName

study.unique_identifier

 

Demo123

 

MeasurementUnit

 

 

 

 

OID

measurement_unit.oc_oid

 

MU_HG

 

Name

measurement_unit.name

UNITS

Hg

 

Symbol

 

 

 

 

TranslatedText

item.units

UNITS

Hg

 

MetaDataVersion

 

 

 

 

OID

dataset.odm_metadataversion_oid

 

v1.0.0 (defined at dataset creation time)

 

Name

dataset.odm_metadataversion_name

 

MetaDataVersion_v1.0.0

 

StudyEventRef

 

 

 

 

StudyEventOID

study_event_definition.oc_oid

 

Systematically generated when a study event definition is created.  Refer to section 2.4.1

 

OrderNumber

study_event.sample_ordinal

 

The order the event definitions appear in the system.  These values can be rearranged by certain users.

 

Mandatory

event_definition_crf.required_crf

 

If at least one CRF in the event definition is set to Required, the event itself becomes required.

 

StudyEventDef

 

 

 

 

OID

study_event_definition.oc_oid

 

Systematically generated when a study event definition is created.  Refer to section 2.4.1

 

Name

study_event_definition.name

 

Initial Visit

 

Repeating

study_event_definition.repeating

 

Yes, No

 

Type

study_event_definition.type

 

Scheduled, Unscheduled, Common.  The values conform to CDISC terms.

 

FormRef

 

 

 

 

FormOID

crf_version.oc_oid

 

F_DEMOGRAPHICS_1

 

Mandatory

event_defintion_crf.required_crf

 

Yes, No

 

FormDef

 

 

 

 

OID

crf_version.oc_oid

 

F_DEMOGRAPHICS_1

 

Name

crf.name + crf_version.name

CRF_NAME + VERSION

Demographics-Dynamics – 1

 

Repeating

 

 

No (generated automatically)

 

ItemGroupRef

 

 

 

 

ItemGroupOID

item_group.oc_oid

 

IG_DEMOG_UNGROUPED

 

Mandatory

Same as ItemRef Mandatory

 

Yes, No

 

OpenClinica:FormDetails

 

 

 

 

FormOID

crf_version.oc_oid

 

F_DEMOG_1

 

ParentFormOID

crf.name

CRF_NAME

DEMOG

Y

VersionDescription

crf_version.description

VERSION_DESCRIPTION

This CRF version contains 2 additional items

Y

RevisionNotes

crf_version.revision_notes

REVISION_NOTES

I revised this CRF and created this new version.

Y

OpenClinica:SectionDetails

    

OpenClinica:Section

    

SectionLabel

section.label

SECTION_LABEL

  

SectionTitle

section.title

SECTION_TITLE

  

SectionSubtitle

section.subtitle

SUBTITLE

  

SectionInstructions

section.instructions

INSTRUCTIONS

  

SectionPageNumber

section.page_number_label

PAGE_NUMBER

  

OpenClinica:PresentInEventDefinition

 

 

 

Y

StudyEventOID

study_event_definition.oc_oid

 

Systematically generated when a study event definition is created.  Refer to section 2.4.1

Y

IsDefaultVersion

event_definition_crf.default_version_id

 

True, False

Y

PasswordRequired

event_definition_crf.electronic_signature

 

True, False

Y

DoubleDataEntry

event_definition_crf.double_entry

 

True, False

Y

HideCRF

event_definition_crf.hide_crf

 

True, False

Y

SourceDataVerification

event_definition_crf.source_data_verification

 

100% Required, Partial Required, Not Required, Not Applicable

Y

ItemGroupDef

 

 

 

 

OID

item_group.oc_oid

 

IG_DEMOG_UNGROUPED

 

Name

item_group.name

GROUP_LABEL

Ungrouped (if the items are ungrouped)

 

Repeating

item_group_metadata.repeating_group

GROUP_LAYOUT

Yes, No

 

Comment

item_group_metadata.header

GROUP_HEADER

 

 

SASDatasetName

 

 

UNGRO044 (generated automatically; take the item_group.name, switch to all caps, and truncate to eight characters, adding integers and alpha characters to the end to make unique)

Y

ItemRef

 

 

 

 

ItemOID

item.oc_oid

 

I_THAP_BLOODPRODTYP

 

OrderNumber

item_form_metadata.ordinal

 

1, 2, 3, etc.

 

Mandatory

item_form_metadata.required

REQUIRED

Yes, No

 

OpenClinicaItemGroupDetails

 

 

 

 

ItemGroupOID

item_group.oc_oid

 

IG_DEMOG_UNGROUPED

 

OpenClinica:PresentInForm

 

 

 

 

FormOID

crf_version.oc_oid

 

 

 

ShowGroup

item_group_metadata.show_group

GROUP_DISPLAY_STATUS

HIDE, SHOW

Y

OpenClinica:ItemGroupRepeat

 

 

 

Y

RepeatNumber

item_group_metadata.repeat_number

GROUP_REPEAT_NUMBER

 

Y

RepeatMax

item_group_metadata.repeat_max

GROUP_REPEAT_MAX

 

Y

ItemDef

 

 

 

 

OID

item.oc_oid

 

I_THAP_BLOODPRODTYP

 

Name

item.name

ITEM_NAME

BloodProdTyp

 

DataType

item_data_type.name

DATA_TYPES

Text, float, integer, etc.

 

Length

item_form_metadata.width_decimal

WIDTH_DECIMAL*

(For ODM float data types, the length is based on the width parameter specified in the WIDTH_DECIMAL field in the spreadsheet (if it is provided).

 

For other data types, length is calculated based on the maximum length of all the values for the item.)

 

SignificantDigits

item_form_metadata.width_decimal

WIDTH_DECIMAL*

(For ODM float data types, the SignificantDigits is based on the decimal parameter specified in the WIDTH_DECIMAL field in the spreadsheet.

 

For other data types, SignificantDigits is not used.)

 

 

 

SASFieldName

 

 

Blood301 (again, generated from the item.name, but truncated to eight characters with an integer at the end to enforce uniqueness)

 

Comment

item.description

DESCRIPTION_LABEL

 

 

OpenClinica:FormOIDs

crf_version.oc_oid

 

F_THAP_V10 (comma-separated list of CRF version OIDs where the item is found)

 

Question

 

 

 

 

OpenClinica:QuestionNumber

item_form_metadata.question_number

QUESTION_NUMBER

 

Y

TranslatedText

item_form_metadata.header + item_form_metadata.left_item_text + item_form_metadata.right_item_text

LEFT_ITEM_TEXT

RIGHT_ITEM_TEXT

HEADER

SUB_HEADER

Amount of blood product (ml)

 

MeasurementUnitRef

 

 

 

 

MeasurementUnitOID

measurement_unit.oc_oid

 

Systematically generated when a CRF Version is uploaded containing an item with a value in the UNITS cell.

 

CodeListRef

 

 

 

 

CodeListOID

response_set.response_set_id

 

CL_233 (OID generated automatically by adding the prefix CL_ to the primary key)

 

RangeCheck

 

 

 

 

Comparator

item_form_metadata.regexp (if “func:”)

VALIDATION

LE, GE, EQ, etc.

 

SoftHard

 

 

Soft, Hard (based on study parameter values governing discrepancy note generation)

 

CheckValue

Item_form_metadata the value in the function.  For example, 95 if the check is for greater than 95,

VALIDATION

100

 

ErrorMessage

 

 

 

 

TranslatedText

item_form_metadata.regexp_error_msg (if “func:”)

VALIDATION_ERROR_MESSAGE

Please specify a value between 0 and 100

 

OpenClinica:ItemDetails

 

 

 

 

ItemOID

item.oc_oid

 

Systematically generated when a CRF Version is uploaded to OpenClinica

 

OpenClinica:PresentInForm

 

 

 

 

FormOID

crf_version.oc_oid

 

 

 

ParentItemOID

item.oc_oid

 

Items can contain links to other items that are their parents.  The value would reflect an OID for an item present in the same section of the same form.

Y

ColumnNumber

item_form_metadata.column_number

COLUMN_NUMBER

1, 2, 3 etc.

Y

PageNumber

item_form_metadata.page_number

PAGE_NUMBER

1, 2, 3 etc.

Y

PHI

item.phi_status

PHI

1, 0

Y

ShowItem

item_form_metadata.show_item

ITEM_DISPLAY_STATUS

SHOW, HIDE

Y

OrderInForm

item_form_metadata.ordinal

 

The items order shown in UI from view crf page.

 

OpenClinica:LeftItemText

item_form_metadata.left_item_text

LEFT_ITEM_TEXT

Height

Y

OpenClinica:RightItemText

item_form_metadata.right_item_text

RIGHT_ITEM_TEXT

Height

Y

OpenClinica:ItemHeader

item_form_metadata.header

HEADER

This field captures Height

Y

OpenClinica:ItemSubHeader

item_form_metadata.subheader

SUB_HEADER

Please provide the Height in inches.

Y

OpenClinica:SectionLabel

section.label

SECTION_LABEL

 

Y

OpenClinica:ItemResponse

response_type.name

RESPONSE_TYPE

 

Y

CodeList

 

 

 

 

OID

response_set.response_set_id

 

CL_233 (OID generated automatically by adding the prefix CL_ to the primary key)

 

Name

response_set.label

RESPONSE_LABEL

yn

 

DataType

item_data_type.name

DATA_TYPES

Text, float, integer, etc.

 

SASFormatName

 

 

$YN54X (generated, with Name in all caps with a $ and an extra suffix to be unique)

 

CodeListItem

 

 

 

 

CodedValue

response_set.options_values

REPONSE_VALUES_OR_CALCULATIONS

1, 2, 3, etc.

 

Decode

 

 

 

 

TranslatedText

response_set.options_text

RESPONSE_OPTIONS_TEXT

Yes, No, etc.

 

OpenClinica:MultiSelectList

 

 

 

 

OpenClinica:ID

response_set.response_set_id

 

MSL_54 (generate an OID on the fly by adding the prefix MSL_ to the iprimary key)

 

OpenClinica:Name

response_set.label

RESPONSE_LABEL

OptionsList

 

OpenClinica:DataType

response_type.name

 

text, (the DataType value is always text)

 

OpenClinica:ActualDataType

response_type.name

DATA_TYPE

text, float, integer

 

OpenClinica:MultiSelectListItem

 

 

 

 

OpenClinica:CodedOptionValue

response_set.options_values

RESPONSE_VALUES_OR_CALCULATIONS

1, 2, 3, etc.

 

OpenClinica:StudyGroupClassList

 

 

 

 

OpenClinica:ID

study_group_class.study_group_class_id

 

SGC_1 (SGC_ prefix automatically appended)

 

OpenClinica:Name

study_group_class.name

 

Age Classification

 

OpenClinica:Status

status.name

 

Available

 

OpenClinica:Type

group_class_types.name

 

Arm, Demographic, etc.

 

OpenClinica:SubjectAssignment

study_group_class.subject_assignment

 

Optional

 

OpenClinica:StudyGroupItem

 

 

 

 

OpenClinica:Name

study_group.name

 

Young Adult

 

OpenClinica:Description

study_group.description

 

12-18

 

OpenClinica:StudyDetails

 

 

 

 

StudyOID

study.oc_oid

 

 

 

SiteName

study.name

 

Present only when extracting site level ODM.

Y

ParentStudyName

study.name

 

Gives the name of study which this site is part of. Tag is present only when the ODM is retrieved at site level.

Y

OpenClinica:StudyDescriptionStatus

 

 

 

Y

OfficialTitle

study.official_title

 

 

Y

SecondaryIDs

study.secondary_identifier

 

 

Y

DateCreated

study.date_created

 

2011-01-01

Y

StartDate

study.date_planned_start

 

2011-01-01

Y

StudyCompletionDate

study.date_planned_end

 

2011-12-31

Y

OpenClinica:StudySystemStatus

study.status_id

 

Available, Pending, Locked, Frozen

Y

OpenClinica:PrincipalInvestigator

study.principal_investigator

 

John Smith

Y

OpenClinica:DetailedDescription

study.protocol_description

 

This is a demonstration study.

Y

OpenClinica:Sponsor

study.sponsor

 

Drug Company A

Y

OpenClinica:Collaborators

study.collaborators

 

This can be a list of different organizations

Y

OpenClinica:StudyPhase

study.phase

 

N/A, Phase I, Phase I/Phase II, Phase II, Phase II/Phase III, Phase III, Phase III/Phase IV, Phase IV

Y

OpenClinica:ProtocolType

study.protocol_type

 

Interventional, Observational

Y

OpenClinica:ProtocolVerificationDate

study.protocol_date_verification

 

2011-01-01

Y

OpenClinica:Purpose

study.purpose

 

Treatment, Prevention, Diagnosis, Supportive Care, Screening, Health Services Research, Basic Science, Other

Y

OpenClinica:Allocation

study.allocation

 

Randomized Clinical Trial, Non-Randomized Clinical Trial, N/A

Y

OpenClinica:Masking

study.masking

 

Open, Single Blind, Double Blind

Y

OpenClinica:Control

study.control

 

 

Y

OpenClinica:InverventionModel

study.interventions

 

 

Y

OpenClinica:ConditionsAndEligibility

study.eligibility

 

 

Y

OpenClinica:Conditions

study.conditions

 

 

Y

OpenClinica:Keywords

study.keywords

 

 

Y

OpenClinica:EligibilityCriteria

 

 

 

Y

OpenClinica:Sex

study.gender

 

 

Y

OpenClinica:Age

 

 

 

Y

MinimumAge

study.age_min

 

 

Y

MaximumAge

study.age_max

 

 

Y

OpenClinica:HealthyVolunteersAccepted

study.healthy_volunteers

 

 

Y

OpenClinica:ExpectedTotalEnrollment

study.expected_total_enrollment

 

 

Y

OpenClinica:FaciltyInformation

 

 

 

Y

OpenClinica:FacilityName

study.facility_name

 

 

Y

OpenClinica:FacilityCity

study.facility_city

 

 

Y

OpenClinica:FacilityState

study.facility_state

 

 

Y

OpenClinica:PostalCode

study.facility_zip

 

 

Y

OpenClinica:FacilityCountry

study.facility_country

 

 

Y

OpenClinica:FacilityContactName

study.facility_contact_name

 

 

Y

OpenClinica:FacilityContactPhone

study.facility_contact_phone

 

 

Y

OpenClinica:FacilityContactEmail

study.facility_contact_email

 

 

Y

OpenClinica:RelatedInformation

 

 

 

Y

OpenClinica:MEDLINEIdentifier

study.medline_identifier

 

 

Y

OpenClinica:ResultsReference

study.results_reference

 

 

Y

OpenClinica:URLReference

study.url

 

 

Y

OpenClinica:URLDescription

study.url_description

 

 

Y

OpenClinica:StudyParameterConfiguration

 

 

 

Y

OpenClinica:StudyParameterListRef

 

 

 

Y

StudyParameterListID

study_parameter.name

 

 

Y

Value

study_parameter_value.value

 

 

Y

OpenClincia:StudyParameterListItem

 

 

 

Y

CodedParameterValue

study_parameter.name

 

 

Y

RuleImport

 

 

Rules are a separate vendor extensions from the OpenClinica: namespace.

Y

RuleAssignment

 

 

 

Y

RuleRef

 

 

 

Y

OID

rule.oc_oid

 

 

Y

ShowAction

rule_action.action_type

 

 

Y

ifExpressionEvaluates

rule_action.expression_evaluates_to

 

 

Y

HideAction

rule_action.action_type

 

 

Y

ifExpressionEvaluates

rule_action.expression_evaluates_to

 

TRUE or FALSE

Y

EmailAction

rule_action.action_type

 

 

Y

ifExpressionEvaluates

rule_action.expression_evaluates_to

 

TRUE or FALSE

Y

DiscrepancyNoteAction

rule_action.action_type

 

 

Y

ifExpressionEvaluates

rule_action.expression_evaluates_to

 

TRUE or FALSE

Y

InsertAction

rule_action.action_type

 

 

Y

ifExpressionEvaluates

rule_action.expression_evaluates_to

 

TRUE or FALSE

Y

Run

 

 

 

Y

AdministrativeEditing

rule_action.administrative_data_entry

 

TRUE or FALSE

Y

InitialDataEntry

rule_action.initial_data_entry

 

TRUE or FALSE

Y

DoubleDataEntry

rule_action.double_data_entry

 

TRUE or FALSE

Y

Batch

rule_action.batch

 

TRUE or FALSE

Y

Message

rule_action.message

 

The values do not match, please verify the data provided.

Y

DestinationProperty

 

 

 

Y

OID

rule_action_property.oc_oid

 

This will be an OID.  If the item is part of the same group, then the user should

Y

RuleDef

 

 

 

Y

OID

rule.oc_oid

 

All capital letters or integers.  This is user defined

Y

Name

rule.name

 

 

Y

Description

rule.description

 

Describe the rule to put it into a context you will recognize.

Y

Expression

rule_expression.value

 

The expression is used to validate data values to then decide if an action should be taken.

Y

AdminData

 

 

 

Y

StudyOID

study.oc_oid

 

 

Y

User

 

 

 

Y

OID

 

 

Generated automatically by taking the value in user_account.user_id and appending USR_ to the front

Y

FullName

user.user_name

 

jsmith

Y

FirstName

user.first_name

 

John

Y

LastName

user.last_name

 

Smith

Y

Organization

user.institutional_affiliation

 

Enterprise Organization

Y

ClinicalData

 

 

 

Y

StudyOID

study.oc_oid

 

S_DEMO123

 

MetaDataVersionOID

dataset.odm_metadataversion_oid

 

v1.0.0

 

SubjectData

 

 

 

 

SubjectKey

study_subject.oc_oid

 

SS_101

 

OpenClinica:StudySubjectId

study_subject.label

 

101

 

OpenClinica:UniqueIdentifier

subject.unique_identifier

 

 

 

OpenClinica:Status

status.name

 

available

 

OpenClinica:SecondaryId

study_subject.secondary_label

 

 

 

OpenClinica:YearOfBirth

subject.date_of_birth

 

2001 (depends on the study parameters; if study does not require date of birth or records full date of birth, this is not included)

 

OpenClinica:DateOfBirth

subject.date_of_birth

 

2001-01-01 (depends on the study parameters; if subject is year only or if date of dirth is not recorded, this is not included)

 

OpenClinica:Sex

subject.gender

 

m, f

 

StudyEventData

 

 

 

 

StudyEventOID

study_event_definition.oc_oid

 

SE_OBSERVAT

 

StudyEventRepeatKey

 

 

1, 2, 3, etc. (optional, not included if not repeating)

 

OpenClinica:StudyEventLocation

study_event.location

 

Boston, MA

 

OpenClinica:StartDate

study_event.date_start

 

2010-10-10

 

OpenClinica:EndDate

study_event.date_end

 

2010-12-31 (optional, not included if left blank)

 

OpenClinica:Status

status.name

 

signed, available, etc.

 

OpenClinica:SubjectAgeAtEvent

 

 

13 (calculated automatically)

 

 

 

 

 

 

FormData

 

 

 

 

FormOID

crf_version.oc_oid

 

F_GROU_V22

 

OpenClinica:Version

crf_version.name

 

 

 

OpenClinica:InterviewerName

event_crf.interviewer_name

 

 

 

OpenClinica:InterviewDate

event_crf.date_interviewed

 

2010-10-10

 

OpenClinica:Status

status.name

 

 

 

ItemGroupData

 

 

 

 

ItemGroupOID

item_group.oc_oid

 

IG_GROU_GROUP_1

 

TransactionType

 

 

Insert (generated by default)

 

ItemGroupRepeatKey

item_data.ordinal if repeating

 

1, 2, 3, etc. (optional)

 

ItemData

 

 

 

 

ItemOID

item.oc_oid

 

I_GROU_TC_ADV_PRIMARY_03

 

Value

item_data.value

 

 

 

IsNull

 

 

Yes (only generated if the item is null)

 

OpenClinica:ReasonForNull

item_data.value

 

(only generated if the item is null)

 

OpenClinica:AuditLogs

 

 

 

Y

EntityID

 

 

Form OID, Item OID, Event OID, Study Subject OID

Y

OpenClinica:AuditLog

 

 

 

Y

ID

 

 

System generated ID taken from the audit_log_event.audit_id and appending AL_ to the beginning

Y

UserID

 

 

References the value for User OID

Y

DateTimeStamp

audit_log_event.audit_date

 

1000-11-16T10:15:37

Y

AuditType

audit_log_event_type.name

 

The audit log type

Y

OldValue

audit_log_event.old_value

 

 

Y

NewValue

audit_log_event.new value

 

 

Y

OpenClinica:DiscrepancyNotes

 

 

 

Y

EntityID

 

 

Item OID, Event OID, Study Subject OID

Y

OpenClinica:DiscrepancyNote

 

 

 

Y

ID

 

 

System generated by appending DN_ to the value from discrepancy_note.discrepancy_note_id

Y

Status

resolution_status.name

 

New, Updated, Resolution Proposed, Closed, Not Applicable

Y

NoteType

discrepancy_note_type.name

 

Query, Failed Validation Check, Reason for Change, Annotation

Y

DateUpdated

discrepancy_note.date_created for a child note

 

 

Y

NumberOfChildren

 

 

Adds up the number of child notes that exist in the thread.

Y

OpenClinica:ChildNote

 

 

 

Y

Status

resolution_status.name

 

New, Updated, Resolution Proposed, Closed, Not Applicable

Y

DateCreated

discrepancy_note.date_created

 

 

Y

OpenClinica:Description

discrepancy_note.description

 

Value outside of range

Y

OpenClinica:DetailedNote

discrepancy_note.detailed_note

 

This value was outside of the range that is accepted.  Please follow up with this subject and see if they should be enrolled or not.

Y

UserRef

 

 

 

Y

UserOID

 

 

 

Y

OpenClinica:SubjectGroupData

 

 

 

 

OpenClinica:StudyGroupClassID

study_group_class.study_group_class_id

 

SGC_1

 

OpenClinica:StudyGroupClassName

study_group_class.name

 

Age Classification

 

OpenClinica:StudyGroupName

study_group.name

 

Young Adult