How to move forms from all CPs to the CP group?

Introduction

This page explains how to delink forms attachment from ‘All current and future collection protocols’ and link to specific protocols or collection protocol group(CPG). If data is already entered and if form attachment is changed via UI, data is lost. Follow below steps to do it without losing data.

Steps

Scenario: The form is attached at the ‘Visit Forms’ level to 'all CPs’ and needs to be moved under ‘Common workflow’ CPG.

1. Log in to the database and run the below SQL to get the context id of the form which is attached to all CPs.

Select fc.identifier from catissue_form_context FC 
join dyextn_containers dc on dc.identifier=fc.CONTAINER_ID 
where dc.name ='<Form Name>';

You can get the form name from the UI. Form list view → click on the form name → Copy form name.

The user will get the form context id(identifier) of the form as shown in the below image.

2. Delete the form attachment from all CPs.

Form list view → click on the setting icon → click on delete icon → Done.

3. Attach the form at the CPG.

Go to the CPG → Form Tab → Visit → Forms +Add → select the form(attached to all CPs).

4. Run the below SQL for CP ids in the CPG to get the new context ID of the form which is attached at the CPG.

Select fc.cp_id,fc.identifier from catissue_form_context FC 
join dyextn_containers dc on dc.identifier=fc.CONTAINER_ID 
where dc.name = <Form name> and fc.deleted_on is null and dc.deleted_on is null;

You will get the new context ids and CP ids as shown in the below image.

You can see the three entries of IDENTIFIER and CP_ID which means the CPG contains these three CPs to which the form is attached.

5. Run the SQL for the count of records under each CPs. This is the count of record (data entry) in each CP. Once you run the update SQL from #6 you will get a count of affected rows. The count from #6 step should match with the count of this step.

select re.identifier from catissue_form_record_entry re 
inner join catissue_form_context fc on fc.identifier = re.form_ctxt_id
inner join catissue_specimen_coll_group cpg on cpg.identifier =re.object_id 
inner join catissue_coll_prot_reg cpr on cpr.identifier = cpg.Collection_protocol_reg_id 
where fc.identifier = <Old Context ID> and cpr.collection_protocol_id = <CP ID>;

6. Run the SQL for updating the context id with a comparison of old context id.

update catissue_form_record_entry re set re.form_ctxt_id = <New Context ID> 
where re.identifier in (select re.identifier from catissue_form_record_entry re 
inner join catissue_form_context fc on fc.identifier = re.form_ctxt_id
inner join catissue_specimen_coll_group cpg on cpg.identifier =re.object_id 
inner join catissue_coll_prot_reg cpr on cpr.identifier = cpg.Collection_protocol_reg_id 
where fc.identifier = <Old Context ID> and cpr.collection_protocol_id = <CP ID>);

You will get the new context id, CP id from #4 step, and old context id from #1 steps. You can check the rows affected after running the SQL and cross verify the count from #5 step.

Go back to the UI, You can see the form is attached to only specific CPs which are part of CPG.

Also, you can verify the data in each CP to which the form is attached.

For more details about the DB table refer to http://dbschema.openspecimen.org/