3

does this post in the old forum from Lobo still hold true?

  1. change the value in civicrm_custom_field
  2. change the column type in the value table
  3. truncate civicrm_cache

1 Answer 1

5

Sometimes its possible to change the field type from UI, but it totally depends upon from what field type you want to convert to.

An easy win would

  1. Single select to radio or vice versa(change the value in civicrm_custom_field)
  2. Multi select to checkbox or vice versa(change the value in civicrm_custom_field)

If the change would involve data type like text to int or float etc it would involve

  1. Change the value in civicrm_custom_field
  2. Convert the data for the the field from old data type to new data type
  3. change the column type in the value table

Additionally things to think about:

If you have trigger based logging enabled you need to ensure that the data types work such that inserts into the log_civicrm_value_group_name_group_id work properly you might end up having to drop and recreate the table.

If you are moving to a text/memo type field then you'll need to drop any indexes.

If doing a number of these you might find the following useful.

To generate the sql to drop the indexes:

mysql -BN db_name -e "SELECT DISTINCT concat('alter table civicrm_value_group_name__group_id drop index ', INDEX_NAME, ';') FROM INFORMATION_SCHEMA.STATISTICS where table_schema = 'db_name' and table_name = 'civicrm_value_group_name__group_id' and (INDEX_NAME like '%489' or INDEX_NAME like '%499' or INDEX_NAME like '%500')" 

To generate the sql to alter the table:

mysql -BN db_name -e " select column_name from  civicrm_custom_field where id in (489,499);" | awk '{printf("alter table civicrm_value_group_name_group_id modify %s new_data_type ADDITIONAL PARAMS HERE;\n", $1)}'

For Additional params check an existing (or new) field of the type you are aiming for.

And to update the fields themselves.

mysql db_name -e "update civicrm_custom_field set data_type = 'New Data Type', html_type='New Type'  where id in (489,499);"

Note again compare against an existing field of the desired type and match against it's values.

1
  • That's brilliant - thanks @pradeep.
    – Marsh
    Jan 25, 2022 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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