Below mentioned tables will be required while the update
Table Name | |
---|---|
dyextn_containers | Table where the custom forms are stored |
catissue_form_context | Contains form attachment levels |
catissue_form_record_entry | Contains 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
...
Code Block | ||||
---|---|---|---|---|
| ||||
select xml IDENTIFIER, NAME, DELETED_ON 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'
Code Block | ||||
---|---|---|---|---|
| ||||
<container id="1">
<id>86</id>
<name>chemotherapyForm</name>
<caption>Chemotherapy form</caption>
<dbTableName>DE_E_11006</dbTableName>....... |
To restore the form,
SQL> select IDENTIFIER, NAME, DELETED_ON from dyextn_containers where caption = 'Chemotherapy form';
...
IDENTIFIER
of this command would be
IDENTIFIER | NAME | DELETED_ON |
---|---|---|
86 | chemotherapyForm | 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:
...
Code Block | ||||
---|---|---|---|---|
| ||||
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.SQL> select * from
Code Block | ||||
---|---|---|---|---|
| ||||
select * from catissue_form_context where container_id=86; |
The output of the command would be like below:
IDENTIFIER | CONTAINER_ID | ENTITY_TYPE | CP_ID | SORT_ORDER | IS_MULTIRECORD | IS_SYS_FORM | deleted_on |
---|---|---|---|---|---|---|---|
29 | 86 | Participant | 5 | NULL17 | /05/18 11:082018-05-24 15:05:04 |
There is a deleted_on date added, which needs to be blanked outSQL> update
Code Block | ||||
---|---|---|---|---|
| ||||
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, 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 attachment.
To find the database table name in which the records of the form are saved, run the below SQL:
Code Block | ||||
---|---|---|---|---|
| ||||
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'
Code Block | ||||
---|---|---|---|---|
| ||||
<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
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT count(*) from DE_E_11006; |
The number or records is
count(*) |
---|
25 |
Check the latest form context identifier of the form.
Code Block | ||||
---|---|---|---|---|
| ||||
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 form was reattached) is 30.
IDENTIFIER | CONTAINER_ID | ENTITY_TYPE | CP_ID | SORT_ORDER | IS_MULTIRECORD | IS_SYS_FORM | deleted_on |
---|---|---|---|---|---|---|---|
29 | 86 | Participant | 5 | NULL | 2018-05-24 15:05:04 | ||
30 | 86 | Participant | 5 | NULL | NULL |
Check the number of records present for both the IDs
Code Block | ||||
---|---|---|---|---|
| ||||
select * from catissue_form_record_entry where form_ctxt_id = 29; |
There are 10 records from the older entry
count(*) |
---|
10 |
For ID 30,
Code Block | ||||
---|---|---|---|---|
| ||||
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 new ID, the sum should be same as that in the database DE table.
Code Block | ||||
---|---|---|---|---|
| ||||
select count(*) from catissue_form_record_entry where form_ctxt_id = 29 and activity_status='Active' limit 5; |
The output of the above command.
IDENTIFIER | FORM_CTXT_ID | OBJECT_ID | RECORD_ID | UPDATED_BY | UPDATE_TIME | ACTIVITY_STATUS |
---|---|---|---|---|---|---|
143381 | 29 | 115 | 142552 | 4 | 23/05/18 14:35 | ACTIVE |
143404 | 29 | 413 | 142575 | 4 | 23/05/18 16:31 | ACTIVE |
143358 | 29 | 1506 | 142529 | 4 | 23/05/18 14:09 | ACTIVE |
143363 | 29 | 1507 | 142534 | 4 | 23/05/18 14:22 | ACTIVE |
143379 | 29 | 1513 | 142550 | 4 | 23/05/18 14:31 | ACTIVE |
Need to move all the records
Code Block | ||||
---|---|---|---|---|
| ||||
update catissue_form_record_entry set form_ctxt_id = 30 where form_ctxt_id = 29; |
Get the
Code Block | ||||
---|---|---|---|---|
| ||||
select count(*) from catissue_form_record_entry where form_ctxt_id = 30 and activity_status='Active'; |
The count should be 25