How to recover data for deleted form?

Below mentioned tables will be required while the update

Table Name
dyextn_containers
A table where the custom forms are stored
catissue_form_contextContains form attachment levels
catissue_form_record_entryContains the actual records entered in the form

The steps mentioned below will help the user to recover the data for the form deleted or detached from the level.

When form is deleted

For example, The user has accidentally deleted the Chemotherapy form. Run the below SQLs:

get_form_details
select IDENTIFIER, NAME, DELETED_ON from dyextn_containers where caption = 'Chemotherapy form';

The output of this command would be:

IDENTIFIER

NAMEDELETED_ON
86chemotherapy for

2018-05-24 15:05:04

To reactivate the form, the deleted_on date needs to be blanked out. Run the below SQL to do so:

remove_deleted_on_date
update dyextn_containers set deleted_on = null where identifier = 86;

After this, the form will reappear in the form list. Follow the steps in the section below to retrieve the data for the records.

When form is detached

CASE 1: Below steps will be performed when the form is not reattached.

Check the identifier (attachment ID) of the form in the form context table.

get_form_context_id
select * from catissue_form_context where container_id=86;

The output of the command would be like below:

IDENTIFIERCONTAINER_IDENTITY_TYPECP_IDSORT_ORDERIS_MULTIRECORDIS_SYS_FORMdeleted_on
2986Participant5NULL

2018-05-24 15:05:04

There is a deleted_on date added, which needs to be blanked out.

remove_deleted_on_date
update catissue_form_context set deleted_on = null where identifier = 29;

This will restore the form attachment level and the records as well.

CASE 2: The form was detached and reattached again, the user performed data entry after reattaching the form. There will some records pointing to the older ID and some to the new ID. We need to update the older records to point to the new form of attachment.

To find the database table name in which the records of the form are saved, run the below SQL:

SQL_to_get_xml
select xml from dyextn_containers where caption = 'Chemotherapy form';

The output would be the form XML file along with the form ID and database table name in which the form records are stored. Eg: Form ID is 86 and DB table name is 'DE_E_11006'

form_xml_output
<container id="1">
  <id>86</id>
  <name>chemotherapyForm</name>
  <caption>Chemotherapy form</caption>
  <dbTableName>DE_E_11006</dbTableName>.......

Check the total number of records present in the DE_E_11006 table.

check_DE_count
SELECT count(*) from DE_E_11006;

The number of records is 

count(*)
25

Check the latest from the context identifier of the form.

get_form_context_id
select * from catissue_form_context where container_id=86;

The output of the command would be like below: The older form ID was 29 and the new one (when the form was reattached) is 30.

IDENTIFIERCONTAINER_IDENTITY_TYPECP_IDSORT_ORDERIS_MULTIRECORDIS_SYS_FORMdeleted_on
2986Participant5NULL

2018-05-24 15:05:04
3086Participant5NULL

NULL

Check the number of records present for both the IDs

get_record_count
select * from catissue_form_record_entry where form_ctxt_id = 29;

There are 10 records from the older entry

count(*)
10

For ID 30,

get_record_count
select * from catissue_form_record_entry where form_ctxt_id = 30;

There are 15 records from the new entry

count(*)
15

After moving the records from old to the new ID, the sum should be the same as that in the database DE table.

get_records
select count(*) from catissue_form_record_entry where form_ctxt_id = 29 and activity_status='Active' limit 5;

The output of the above command.

IDENTIFIERFORM_CTXT_IDOBJECT_IDRECORD_IDUPDATED_BYUPDATE_TIMEACTIVITY_STATUS
14338129115142552423/05/18 14:35ACTIVE
14340429413142575423/05/18 16:31ACTIVE
143358291506142529423/05/18 14:09ACTIVE
143363291507142534423/05/18 14:22ACTIVE
143379291513142550423/05/18 14:31ACTIVE

Need to move all the records 

update_records
update catissue_form_record_entry set form_ctxt_id = 30 where form_ctxt_id = 29;

Get the 

get_records_count
select count(*) from catissue_form_record_entry where form_ctxt_id = 30 and activity_status='Active';

The count should be 25