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