5

Sometimes one needs to rejig which fieldset contains one or more fields after data has been entered in them. For example, you create a new contact subtype and you want to move some but not all of the custom fields in an existing custom fieldset to a custom fieldset for the new contact subtype. You might need to do this when consolidating fieldsets, or just so the fields appear under a more appropriate title when you have several fieldsets for the same objects.

What's the best way to move a custom contact field and its data to a different fieldset?

7

Mostly you will want to move fields between customs fields sets used for the same entity (ie from one custom field set used for participants to another custom field set used for participants.)

This can be done easily through the UI as follows:

  1. Go to Administer > Customize Data and Screens > Custom Fields
  2. If it doesn't already exist create the new field set.
  3. Return to Administer > Customize Data and Screens > Custom Fields and select View and Edit Custom Fields for the field set containing the field you want to move.
  4. On the far right of the row containing the field you want to move click on More > Move.
  5. Select the the new field set.

However, if you want to move a field from a field set used for Individuals to a field set used for Contacts or vice versa you will get an error message "The destination group extends a different entity type."

In that situation or similar involving a single branch of contact subtypes:

  1. Back up your database.
  2. Using phpMyAdmin or otherwise, in civicrm_custom_group find the field set with the most restricted contact type and edit the Extends field to match the value in the field set with the least restricted contact type. (eg change "Individual" to "Contact".)
  3. In the UI move the field as described above.
  4. Using phpMyAdmin or otherwise change the Extends value you altered in 2. back to it's original value.

NOTE: 1. I haven't tried moving fields between a field set used for Organizations and one used for Individuals.

2

Before doing either of the following approaches, make sure to backup your database.

Both methods assume you have created the new custom fieldset group in the browser before beginning.

Export and Import

The first method is done through the browser, and the instructions are a high-level summary of what needs to be done.

  1. Create a new second field in the other custom fieldset. You'll need to use a different name when creating the second field that was used when creating the first field. (After you have created the second field, you can change its name back to what was used for the first field.) If the field has option values, make sure to use the same set of options.
  2. Export data from the first field including the contact id,
  3. Import data into the second field.
  4. Review the data to make sure everything looks right.
  5. Delete the first field.

Change the field location in the schema (Expert SQL skills required)

This method can be used to avoid long exports and imports, as well as inadvertent errors in configuring fields the same through the browser.

Make sure to take your site offline while modifying the schema as below.

In this approach, we create new field(s) in the table holding the data for the new fieldset, copy the data over, move the fields from the old to the new group, review the data, then delete the old field(s). As an example, we're going to move fields from an Applicant custom fieldset to a new Student fieldset.

  1. Find the custom field ID and the custom fieldset group ID in one of two ways.

    1. In a browser, navigate to Administer > Custom Data and Screens > Custom Fields, then beside the relevant fieldset click View and Edit Custom Fields, then beside the relevant field, hover over Edit field and observe the url your browser displays (or open a new tab for that url). In the url, you'll see gid=xx and id=yy, where xx is the fieldset group id and yy is the field id.
    2. In MySQL either on a command line, via phpMyAdmin, or some similar tool, list the custom fieldset groups with:

      mysql> SELECT id, name, title, extends FROM civicrm_custom_group;

      Note the fieldset group id numbers of the old and new groups you want, say gg1 and gg2. Then run:

      mysql> SELECT id, name, label FROM civicrm_custom_field WHERE custom_group_id=gg1;

      Note the field id number you want, say ff.

  2. There is a table for each custom fieldset named civicrm_value_GGG_gg, where gg is the group id, and GGG is its civicrm_custom_group.name. I find it is easiest to get the table name by copying it into my clipboard from the list of tables in the database. From the mysql command line:

    mysql> SHOW TABLES;

  3. Display the definition of the table so we can copy one or more field definitions into the new table, for example:

    mysql> SHOW CREATE TABLE civicrm_value_applicants_11;

    | civicrm_value_applicants_11 | CREATE TABLE civicrm_value_applicants_11 (

    id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Default MySQL primary key',

    entity_id int(10) unsigned NOT NULL COMMENT 'Table that this extends',

    student_number_18 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

    application_stage_19 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

    academic_program_20 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

    start_term_21 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

    residency_22 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

    application_type_23 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

    academic_level_24 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

    rtw__25 tinyint(4) DEFAULT NULL,

    applicant_type_52 double DEFAULT NULL,

    PRIMARY KEY (id),

    UNIQUE KEY unique_entity_id (entity_id),

    KEY INDEX_application_stage_19 (application_stage_19),

    CONSTRAINT FK_civicrm_value_applicants_11_entity_id FOREIGN KEY (entity_id) REFERENCES civicrm_contact (id) ON DELETE CASCADE

    ) ENGINE=InnoDB AUTO_INCREMENT=3337 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

    1. Here's the table we are copying fields into:

    mysql> show create table civicrm_value_students_37;

    | civicrm_value_students_37 | CREATE TABLE civicrm_value_students_37 (

    id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Default MySQL primary key',

    entity_id int(10) unsigned NOT NULL COMMENT 'Table that this extends',

    PRIMARY KEY (id),

    UNIQUE KEY unique_entity_id (entity_id),

    CONSTRAINT FK_civicrm_value_students_37_entity_id FOREIGN KEY (entity_id) REFERENCES civicrm_contact (id) ON DELETE CASCADE

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

    1. Using relevant field definitions from the first table, run the following query:

    mysql> ALTER TABLE civicrm_value_students_37

    ADD COLUMN (

    student_number_18 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

    academic_program_20 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

    start_term_21 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

    residency_22 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

    academic_level_24 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

    rtw__25 tinyint(4) DEFAULT NULL

    );

    1. Copy the data over. If the fieldset receiving the data exists for some or all of the relevant records, then you will need to run an update query along the following lines

    mysql> UPDATE civicrm_value_students_37 s

    INNER JOIN civicrm_value_applicants_11 a

    ON s.entity_id=a.entity_id

    SET

    s.student_number_18=a.student_number_18,

    s.academic_program_20=a.academic_program_20,

    s.start_term_21=a.start_term_21,

    s.residency_22=a.residency_22,

    s.academic_level_24=a.academic_level_24,

    s.rtw__25=a.rtw__25;

    1. Insert the data if appropriate. If some or all of the records in the old fieldset do not exist in the new fieldset, you'll need to run an insert query along the following lines:

    mysql> INSERT INTO civicrm_value_students_37 (entity_id, student_number_18, academic_program_20, start_term_21, residency_22, academic_level_24, rtw__25)

    SELECT a.entity_id, a.student_number_18, a.academic_program_20, a.start_term_21, a.residency_22, a.academic_level_24, a.rtw__25

    FROM civicrm_value_applicants_11 a

    LEFT JOIN civicrm_value_students_37 s

    ON a.entity_id=s.entity_id

    WHERE s.entity_id IS NULL;

    NB: FYI the LEFT JOIN ... WHERE ... IS NULL selects records that exist in the old table but not the new one.

    1. This step is the first action that changes what appears through the browser. Update the fieldset group that these fields are in:

    mysql> UPDATE civicrm_custom_field SET custom_group_id=11 WHERE id IN (18, 20, 21, 22, 24, 25);

    1. After checking the data in the browser, remove cruft from the first database table:

    mysql> ALTER TABLE civicrm_value_applicants_11

    DROP COLUMN student_number_18,

    DROP COLUMN academic_program_20,

    DROP COLUMN start_term_21,

    DROP COLUMN residency_22,

    DROP COLUMN academic_level_24,

    DROP COLUMN rtw__25;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.