...
Create a table with required fields and foreign key to OpenSpecimen's Visit table as illustrated in below example:
Code Block language sql 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 table 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.Code Block language sql LOAD DATA INFILE '<my-sql-files>/visit_diagnosis.csv'INTO TABLE CUSTOM_VISIT_STAT_DIAGNOSIS FIELDS TERMINATED BY ',' IGNORE 1 LINES;
Add following query metadata in file
$TOMCAT_HOME/webapps/openspecimen/WEB-INF/classes/query-forms/scg.xml
just below the</row>
corresponding to the visit sub-formextensions
.Code Block language xml <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>
- Save the file and restart OpenSpecimen
...