Getting the DE table for a Form
Inspect the form’s dyextn_container XML
Go to ‘Forms’ → Click on the form → Note the ‘Form Name’
...
2. Select the XML from ‘dyextn_containers’ table
| Code Block |
|---|
|
SELECT XML FROM dyextn_containers WHERE name='<form_name>'; |
3. The result would have a tag ‘<dbTableName>’ where the table name would be found. This table would contain the values for the form attribute.
| Expand |
|---|
| title | In this case <dbTableName>DE_E_11076</dbTableName> |
|---|
|
 Image Removed |
4. The DE table (DE_E_11076, in this case) will have columns for each attribute. The mapping of an attribute name to the column name can be found in the resultant XML from step#2. Each attribute is stored under <entry> tag, and each entry tag has <dbColumnName> tag.
| Expand |
|---|
| title | In the below example, you can see field 'visitReason' is mapped to 'DE_A_2' column. |
|---|
|
 Image Removed |
Getting the records of the Extension Form
Note: This section illustrates how to fetch records of a dynamic-form attached at a “Form Level” and not a “Custom field”.
Entity Records Table
Extension form records for different entities can be found in different tables, see below mapping.
...
Entity Type
...
Entity Record Table
...
Collection Protocol Registrations
...
os_cpr_extn_recs
...
Participants
...
os_participant_extn_recs
...
Specimens
...
os_spmn_extn_recs
...
Visits
...
os_visit_extn_recs
SQL Template to fetch form-records
To get the form records of an entity form you use the following SQL template.
| Expand |
|---|
| title | Query template: Query records of a form |
|---|
|
| Code Block |
|---|
| SELECT <select_appropriate_columns>
FROM <entity_records_table> ert
LEFT JOIN <de_table> de ON de.identifier = ert.record_id
LEFT JOIN catissue_specimen_coll_group visit ON visit.identifier = ert.visit_id; # If entity is visit
LEFT JOIN catissue_specimen spmn ON spmn.identifier = ert.specimen_id; # if entity is specimens
LEFT JOIN catissue_coll_prot_reg cpr ON cpr.identifier = ert.cpr_id; # if entity is CPR
LEFT JOIN os_participant_extn_recs part ON part.identifier = ert.participant_id; # if entity is participant |
|
Examples
Visit Forms Records: Getting the records from a form on the visit-level
| Expand |
|---|
| title | Example: Query records of a form |
|---|
|
| Code Block |
|---|
| SELECT
visit.name as 'Visit Name',
cpr.PROTOCOL_PARTICIPANT_ID as 'PPID',
cp.short_title as 'Short Title',
de.DE_A_2 as 'Reason'
FROM os_visit_extn_recs vre
LEFT JOIN DE_E_11076 de ON de.identifier = vre.record_id
LEFT JOIN catissue_specimen_coll_group visit ON visit.identifier = vre.visit_id
LEFT JOIN catissue_coll_prot_reg cpr ON cpr.identifier = visit.COLLECTION_PROTOCOL_REG_ID
LEFT JOIN catissue_coll_prot_event event ON event.identifier = visit.COLLECTION_PROTOCOL_EVENT_ID
LEFT JOIN catissue_collection_protocol cp ON cp.identifier = event.COLLECTION_PROTOCOL_ID; |
|
Getting the records of the Extension Form (Custom Fields)
SQL Template to fetch custom-field records
| Expand |
|---|
| title | Query template: Query records of a form attached at custom-field level |
|---|
|
| Code Block |
|---|
| SELECT <select_appropriate_columns>
FROM catissue_form_record_entry fre
LEFT JOIN catissue_form_context fc ON fc.identifier = fre.form_ctxt_id
LEFT JOIN catissue_specimen spmn ON spmn.identifier = fre.object_id # if entity_type is SpecimenExtension
LEFT JOIN catissue_participant part ON part.identifier = fre.object_id # if entity_type is ParticipantExtension
LEFT JOIN catissue_specimen_coll_group visit ON visit.identifier = fre.object_id # if entity_type is VisitExtension
LEFT JOIN catissue_collection_protocol cp ON cp.identifier = fre.object_id # if entity_type is CollectionProtocolExtension
LEFT JOIN <de_table> de ON de.identifier = fre.record_id
WHERE
fc.ENTITY_TYPE = <entity_type>; |
|
Example
| Expand |
|---|
| title | Example #1: Query records of a specimen custom-field level form |
|---|
|
| Code Block |
|---|
| SELECT spmn.label, de.*
FROM catissue_form_record_entry fre
LEFT JOIN catissue_form_context fc ON fc.identifier = fre.form_ctxt_id
LEFT JOIN catissue_specimen spmn ON spmn.identifier = fre.object_id
LEFT JOIN DE_E_11101 de ON de.identifier = fre.record_id
WHERE
fc.ENTITY_TYPE = 'SpecimenExtension'; |
|
| Expand |
|---|
| title | Example #2: Query records of a registration custom-field level form |
|---|
|
| Code Block |
|---|
| SELECT
cpr.PROTOCOL_PARTICIPANT_ID as 'PPID',
de.*
FROM catissue_form_record_entry fre
LEFT JOIN catissue_form_context fc ON fc.identifier = fre.form_ctxt_id
LEFT JOIN catissue_participant part ON part.identifier = fre.object_id
JOIN catissue_coll_prot_reg cpr ON cpr.PARTICIPANT_ID = part.identifier
LEFT JOIN DE_E_11051 de ON de.identifier = fre.record_id
WHERE
fc.entity_type='ParticipantExtension'; |
|
Note: See below table for different ‘entity_type’
...
Entity Type
...
Entity Type Name
...
Specimen
...
SpecimenExtension
...
Participant (Collection Protocol Registration)
...
ParticipantExtension
...
Visit
...
VisitExtension
...
Collection Protocol
...
| Note |
|---|
Accessing custom fields/forms records directly at the DB level is not recommended, as the DB schemas can change without prior notice. This might cause any queries, or reports built around the same, to fail. |
Introduction
Custom forms and field values are stored in dynamically created tables, so it can difficult to find and access them directly from the DB. However, you can follow the below steps to get the query accessing the custom fields/forms records.
Step 1: Build the OpenSpecimen query
Build a query in OpenSpecimen which contains all the required fields including custom (form) fields. (Refer to the https://openspecimen.atlassian.net/wiki/x/B4AhAQ page for more info)
| Expand |
|---|
| title | Screenshot: Example Query |
|---|
|
 Image Added |
Step 2: Save and run the query
Save the query
Go to ‘Actions’ → ‘Save’ → Enter ‘Title’ → Click ‘Save’
Run the query
Go the ‘Queries’ page → Click on the saved query to run it. This will add the query to the query audit logs.
| Expand |
|---|
|
 Image Added |
Step 3: Retrieve the backend SQL
Retrieve the SQL used by OpenSpecimen to display the results by following this wiki page: https://openspecimen.atlassian.net/wiki/x/GwDiGw
Step 4: Use the SQL directly on DB
Run the query directly on the DB.
| Expand |
|---|
|
 Image Added |