Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-1106

College Match - Merge Duplicate High School Records in Preapplication Data


    • Type: Task
    • Status: Done/Fixed
    • Priority: Major
    • Resolution: Fixed/Completed
    • Affects Version/s: None
    • Fix Version/s: Quest-CM
    • Component/s: None
    • Labels:


      During the Preapplication Cycle, we did not attempt to dedupe the High School records that students entered. Hence, the final DB from that cycle contains many duplicate High School (civicrm_contact/civicrm_organization) records. We now have a master reference DB of schools which we can use to dedupe and match school based on a CEEB (school) code. However, some schools are not in the CEEB reference database.

      Quest has updated the final CiviCRM DB from the Preapp cycle - populating the correct CEEB codes for each listed school. The task for this issue is to dedupe and merge any school records which have a CEEB code into a single contact organization, AND correct the school data - school name, address, school type (custom_2) to match the info in the CEEB database.. We will also migrate the school type (custom_2) data values for schools w/o CEEB codes to match the new mappings.

      This operation should be coded as a PHP script which can be run from the command line. It should be commited to the quest repository. http://svn.quest.civicrm.org/quest/trunk/bin

      1. Merge and correct data for schools with CEEB codes

      1.1 Source the updated Preapp DB from cividata/matchapp/quest_db.sql.bz2 (u need to source quest_data_cm.sql and civicrm_data.mysql before doing this)

      1.2 Get a list of all unique CEEB code values (select UNIQUE(char_data) from civicrm_custom_value WHERE custom_field_id=1)
      1.3 For each unique CEEB code value:

      • Find the civicrm_contact record with the lowest ID - this contact organization (school) will be preserved
      • Retrieve the corresponding quest_ceeb record (quest_ceeb.code MATCHES the contact's custom_1 value)
      • Update the following data values to match the CEEB data:
        -+ set civicrm_custom_value for custom_field 2 (school type) = quest_ceeb.school_type
        -+ set organization_name = quest_ceeb.school_name
        -+ set location 1 street_address, city, state_province to match corresponding quest_ceeb table values
      • For each of the other contact (organization) records which have this CEEB code
        -+ update their civicrm_relationship records - replacing their contact ID with the ID of the school instance we're keeping
        -+ use the delete contact API to delete this duplication school and it's child records

      2. Fix the school type field value (custom_2) for schools which don't have CEEB codes

      • Find all civicrm_organization records which do NOT have a CEEB code (e.g. no corresponding civicrm_custom_value record) AND which do have a school type (custom_2)
      • For these schools, update (migrate) the civicrm_custom_value.char_data field per these mappings (old value -> new value):
      1. 'Public' (310) maps to 'public' (A)
      2. 'Private' (311) maps to 'independent, not religious' (B)
      3. 'Parochial' (312) maps to 'other independent, religious' (D)




            • Assignee:
              kurund Kurund Jalmi
              dgg David Greenberg
            • Votes:
              0 Vote for this issue
              0 Start watching this issue


              • Created: