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

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.

In this case <dbTableName>DE_E_11076</dbTableName>

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.

In the below example, you can see field 'visitReason' is mapped to 'DE_A_2' column.

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.

Query template: Query records of a form

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

Example: Query records of a form

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

Query template: Query records of a form attached at custom-field level

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

Example #1: Query records of a specimen custom-field level form

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

Example #2: Query records of a registration custom-field level form

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	CollectionProtocolExtension

Getting a Form DE table and querying its records