Data Migration for Question Groups

Owned by Kay Chau
Last updated: Mar 27, 2011 by UdaiU (Unlicensed)Version comment

*MUST READ* if your MFI currently uses Additional Fields (Custom Fields) AND/OR Surveys in Mifos 1.6.x and earlier.

During the upgrade to Mifos 2.0, Additional Fields and Surveys are automatically migrated to the Question Group functionality.

Survey Migration

  • Surveys and responses for all workflows are migrated.
  • Validation messages during the migration process are logged in the tomcat log automatically.
  • A new question group is created for each migrated survey for the same flow the survey belonged to.
  • Responses saved are all migrated to the new Question Group tables
  • The survey title becomes the question group title
  • If the survey was set to All, the new QG is automatically applied to the Create Savings workflow.  Issue mifos-4293.
    • Mandatory questions in surveys set to All are created as Non-Mandatory in Question Groups - Issue mifos-4329.

Additional Fields Migration

  • Additional Fields for all entities are migrated.
  • Validation messages during the migration process are logged in the tomcat log automatically.
  • A new question group is created for each migrated QG for the Create flow that it belonged to.  IE, additional fields for Clients are now set to Create Client workflow.
  • Responses saved are all migrated to the new Question Group tables
  • The question group title becomes "Additional Fields Create xxx" where xxx is the entity the addtional fields belonged to.
  • By default, question groups will be set as non-editable.  To make them editable, you will need to go to the Question Group admin page and set them to be.
  • Additional fields attached to loans are not automatically added to the Loan Creation process.  You will need to go to the Question Group admin page and set the question group to be applicable to every loan or go to individual loan products and turn on the Question Group in question.

Post Migration

  • The migrated questions are added under the 'Misc' section of the question group
  • Question Groups are automatically active
  • When the migrating survey and an existing question group have the same title and flow, then post migration, a duplicate QG is created with the same title as the existing QG.
  • The order of the questions within the survey remains the same post migration.
  • Question within a survey retains its mandatory/non-mandatory status after migration in the corresponding flow
  • ‘Date of survey, surveyed by, entered into the system by’ fields and their corresponding responses are not visible on the UI after migration
  • IF the server is restarted during upgrade, all the responses get migrated all over again if the earlier migration failed.
  • When the string length of a question title on the UI is greater than 500 characters, the question is not migrated.
  • Survey responses are not editable post migration.

How might Migration not be successful?

  • If you somehow restart the migration, then any existing surveys or additional fields that were already migrated will not be re-migrated.  This might cause issues if migration was stopped in the middle of the process of copying data from surveys and additional fields to question groups.

If successful

  • NO additional fields should be listed under "View Additional Fields" in Admin
  • No more data exist in old Additional Fields tables in the DB
  • In the workflows where these additional fields existed, these additional fields shouldn't be displayed as Additional Fields - should now be under Question Groups
  • There should be no validation for mandatory additional fields - the validation should only be for QG's

If NOT successful

  • The additional fields that did not migrate should be listed under "View Additional Fields" in Admin
  • The additional fields that did not migrate and all data saved with them should still be in the DB
  • In the workflows where these additional fields existed, these additional fields shouldn't be displayed as Additional Fields - the MFI must re-create them manually under Question Groups
  • There should be no validation for mandatory additional fields - the validation should only be for QG's

Known Issues

MIFOS-4892    : Survey Migration not mapping single select or choice responses correctly

The data which is affected by this issue requires manual verification at database level using this query.

SELECT distinct(qgr.response)
   FROM question_group qg, question_group_instance qgi, question_group_response qgr, sections_questions s, questions q
   WHERE qg.id=qgi.question_group_id AND qgi.id=qgr.question_group_instance_id AND qgr.sections_questions_id=s.id
   AND s.question_id=q.question_id AND (q.answer_type=6 OR q.answer_type=4) AND is_ppi=0;

If you have zero rows as result then your database is NOT affected by this issue. Make sure you have applied query in correctly.

If you are affected.
The result of this query will be id(numbers) instead of response text.

STEP 1: make backup of question_group_response table.

mysqldump -u mifos -pmifos mifos question_group_response > qgr.sql

STEP 2: Run the update query to fix the data (NOTE: This query should be run once only, running twice or more will corrupt responses column, then you would have to restore the question_group_table and try again)
(This query only affects question_group_response table, so if something goes wrong you can restore the question_group_response table)

UPDATE question_group_response qgr
    JOIN  ( SELECT qgr.id, qgr.response, q.question_id
            FROM question_group qg, question_group_instance qgi, question_group_response qgr, sections_questions s, questions q
            WHERE qg.id=qgi.question_group_id AND qgi.id=qgr.question_group_instance_id AND qgr.sections_questions_id=s.id
            AND s.question_id=q.question_id AND (q.answer_type=6 OR q.answer_type=4) AND is_ppi=0
           ) r     
SET qgr.response=(SELECT choice_text FROM question_choices WHERE choice_id=qgr.response) WHERE qgr.id=r.id;

STEP 3: check for mysql warnings/errors (there should NOT be any warning or error after running the query).

mysql> SHOW WARNINGS