How to retrieve data of deleted field from custom form?

Pre-requisites

The exact deleted field name and its type must be known.

Form name and form ID under which field is deleted must be known.

Steps to solve the problem: 

1. Get the form XML from the dyextn_containers table into a file using below SQL.

mysql -u<db_user> -p<password> -D <db_name> -e "select XML from dyextn_containers where identifier = <FORM_ID>;" > custom-form.xml

2. Open the custom-form.xml file and search for form/subform name into it you will find the database table name for the same form in <dbTableName> tag.

Example : 

<name>pDAC</name>\n 
<caption>PDAC</caption>\n 
<dbTableName>DE_E_11280</dbTableName>\n

PDAC is form name and DE_E_11280 is the database table name.

3. List down all present fields from UI of the form into a google sheet.

4. Now describe the table found in step#2. List down all database column names into a google sheet.

desc DE_E_11280;

5. Compare the columns listed in step #3 with those present in the database table (step #4 columns) for the form PDAC. The database will have more columns than those present in XML. The additional columns are the candidate columns that were storing the data for the deleted field of the form.

6. Review the additional columns' data and see which one of them stored the data for the deleted field. If the additional columns list has only one column then this step is not needed.

Here you need to check data is present into the additional columns or not. If there are 10 additional columns, you need to run the below query 10 times with each column name and note down the count of records.

select count (additional_column_name) from <table_name_from_step2>;

Example : select count (name) from DE_E_11280;

7. Check the values of the additional columns, check if relevant data is present or not.

select distinct (additional_column_name) from <table_name_from_step2>;

Once the relevant data is found that column is a missing field of the form.

8. Add the missing field through UI. Give it the same name and type as the deleted ones. This will create a new database column.

Note: If the deleted field is dropdown or checkbox type then you need to add values (got at step #7) to the newly created field from UI.

9. Copy the column data from the field identified in step #7  the newly created column.

update DE_E_11280 set DE_A_29 = DE_A_24.