Answers.Body: <p>Sometimes its possible to change the field type from UI, but it totally depends upon from what field type you want to convert to.</p>
  <p>An easy win would</p>
  <ol>
  <li>Single select to radio or vice versa(change the value in civicrm_custom_field)</li>
  <li>Multi select to checkbox or vice versa(change the value in civicrm_custom_field)</li>
  </ol>
  <p>If the change would involve data type like text to int or float etc it would involve</p>
  <ol>
  <li>Change the value in civicrm_custom_field</li>
  <li>Convert the data for the the field from old data type to new data type</li>
  <li>change the column type in the value table</li>
  </ol>
  <p>Additionally things to think about:</p>
  <p>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.</p>
  <p>If you are moving to a text/memo type field then you'll need to drop any indexes.</p>
  <p>If doing a number of these you might find the following useful.</p>
  <p>To generate the sql to drop the indexes:</p>
  <pre><code>mysql -BN db_name -e &quot;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')&quot; 
  </code></pre>
  <p>To generate the sql to alter the table:</p>
  <pre><code>mysql -BN db_name -e &quot; select column_name from  civicrm_custom_field where id in (489,499);&quot; | awk '{printf(&quot;alter table civicrm_value_group_name_group_id modify %s new_data_type ADDITIONAL PARAMS HERE;\n&quot;, $1)}'
  </code></pre>
  <p>For Additional params check an existing (or new) field of the type you are aiming for.</p>
  <p>And to update the fields themselves.</p>
  <pre><code>mysql db_name -e &quot;update civicrm_custom_field set data_type = 'New Data Type', html_type='New Type'  where id in (489,499);&quot;
  </code></pre>
  <p>Note again compare against an existing field of the desired type and match against it's values.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-01-25T16:10:57.230Z
Answers.Text: That's brilliant - thanks @pradeep.
Answers.UserDisplayName: Marsh
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-01-25T15:42:05.683Z
Answers.LastActivityDate: 2022-06-09T03:31:31.187Z
Answers.LastEditDate: 2022-06-09T03:31:31.187Z
Answers.UserDisplayName: Pradeep Nayak
Body: <p>does <a href="https://forum.civicrm.org/index.php?topic=13151.0.html" rel="nofollow noreferrer">this post in the old forum from Lobo</a> still hold true?</p>
  <ol>
  <li>change the value in civicrm_custom_field</li>
  <li>change the column type in the value table</li>
  <li>truncate civicrm_cache</li>
  </ol>
  
Tags: <custom-fields>
Title: Is it the case that to change a field type in CiviCRM there are only 3 steps?
ThreadText: # Is it the case that to change a field type in CiviCRM there are only 3 steps?
  
  (Asked by: username_0 on 2022-01-25)
  
  does [this post in the old forum from Lobo](https://forum.civicrm.org/index.php?topic=13151.0.html) still hold true?
  
  1. change the value in civicrm\_custom\_field
  2. change the column type in the value table
  3. truncate civicrm\_cache
  
  **Tags:** custom-fields
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2022-01-25. Score: 3 | Accepted answer
  
  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.
  
  > **Comments:**
  > **username_0**: That's brilliant - thanks @username_1.  
  
  ---
  
  