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

...

The query forms are overwritten during upgrade. Therefore a copy of the customised form XML should be retained for merging after the upgrade is done. This will be enhanced in the future versions to make it upgrade proof.
The query form XML files for Participant registration and Specimen are cpr.xml and specimen.xml respectively.
Above example metadata should take care of 80% use cases. For syntax of using other field types, refer existing query form XMLs located in $TOMCAT_HOME/webapps/openspecimen/WEB-INF/classes/query-forms directory.
Going forward, in future releases, we plan to allow users define their custom tables and related query metadata in plugin, which the query interface can pick automagically automatically and make them available for querying purpose.

Versions Compared

Old Version 4

New Version 5

Key