Page Comparison

...

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-form extensions.

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

...

Versions Compared

Old Version 5

New Version 6

Key