You can add additional data from other sources like tumor registry, pathology databases, or other clinical databases into OpenSpecimen. This data will be available in the OpenSpecimen query interface and catalogs.
There are two ways of doing this:
We want to capture vital statistics and radiology diagnoses, if any, for every participant visit. Also, we would like to make this captured data available to researchers via the OpenSpecimen query interface.
Create a table with required fields and foreign key to OpenSpecimen's Visit table as illustrated in below example:
create table CUSTOM_VISIT_STATS_DIAGNOSIS( IDENTIFIER BIGINT NOT NULL AUTO_INCREMENT, VISIT_ID BIGINT NOT NULL, DIASTOLIC_BP SMALLINT, SYSTOLIC_BP SMALLINT, RADIOLOGY_DIAGNOSIS VARCHAR(255), PRINCIPAL_DIAGNOSIS VARCHAR(255), FOREIGN KEY (VISIT_ID) REFERENCES CATISSUE_SPECIMEN_COLL_GROUP(IDENTIFIER) ); |
Load data into custom visit stats and diagnosis tables with relevant participant visit IDs and field values. Assuming we've all our data in a CSV file visit_diagnosis.csv,
we'll use following MySQL command to import data into custom table.
LOAD DATA INFILE '<my-sql-files>/visit_diagnosis.csv'INTO TABLE CUSTOM_VISIT_STAT_DIAGNOSIS FIELDS TERMINATED BY ',' IGNORE 1 LINES; |
Add the following query metadata in the file $TOMCAT_HOME/webapps/openspecimen/WEB-INF/classes/query-forms/scg.xml
just below the </row>
corresponding to the visit sub-form extensions
.
<row> <subForm> <name>customVisitDiagnosis</name> <!-- unique name within form --> <udn>customVisitDiagnosis</udn> <caption>Visit Stats and Diagnosis</caption> <table>CUSTOM_VISIT_STAT_DIAGNOSIS</table> <!-- custom table name --> <primaryKey>IDENTIFIER</primaryKey> <!-- this is optional and need not be specified if PK column name is IDENTIFIER --> <foreignKey>VISIT_ID</foreignKey> <!-- visit foreign key in custom table --> <parentKey>IDENTIFIER</parentKey> <row> <numberField> <name>id</name> <udn>id</udn> <caption>Diagnosis ID</caption> <column>IDENTIFIER</column> </numberField> <numberField> <name>diastolicBp</name> <udn>diastolicBp</udn> <caption>Diastolic BP</caption> <column>DIASTOLIC_DP</column> </numberField> <numberField> <name>systolicBp</name> <udn>systolicBp</udn> <caption>Systolic BP</caption> <column>SYSTOLIC_BP</column> </numberField> <dropDown> <name>radiologyDiagnosis</name> <udn>radiologyDiagnosis</udn> <caption>Radiology Diagnosis</caption> <column>RADIOLOGY_DIAGNOSIS</column> <options> <!-- dropdown values are picked from values available in custom table --> <sql>select distinct radiology_diagnosis from CUSTOM_VISIT_STAT_DIAGNOSIS where radiology_diagnosis is not null</sql> </options> </dropDown> <dropDown> <name>principalDiagnosis</name> <udn>principalDiagnosis</udn> <caption>Principal Diagnosis</caption> <column>PRINCIPAL_DIAGNOSIS</column> <options> <!-- dropdown values are picked from values available in custom table --> <sql>select distinct principal_diagnosis from CUSTOM_VISIT_STAT_DIAGNOSIS where principal_diagnosis is not null</sql> </options> </dropDown> </row> </subForm> </row> |
On the successful restart of OpenSpecimen, you should be able to see your custom visit stats and diagnosis fields appearing in the Visit form of query interface. You can use these fields like any normal OpenSpecimen query fields.
cpr.xml
and specimen.xml
respectively.$TOMCAT_HOME/webapps/openspecimen/WEB-INF/classes/query-forms
directory.