How to change custom field input from "text" to "select" (dropdown select)

[cfusch]

I have a custom field that is currently "Alphanumeric > Text" and need to change that field to "Alphanumeric > Select", i.e. HTML input type is a dropdown selection. 

I don't mind going to the database to do this, but need a process.  OR is it easier to create a new empty field with an Alphanumeric>Selection drop down and move the data there?

Help!

Carl

[Coleman Watts]

You can do this easily enough by modifying the civicrm_custom_field table in mysql. Change the html_type column.

[cfusch]

Coleman, thanks for the swift response!

Do I just change it to "Select" ? (spelled with a capital?)   And how do I deal with existing (non-valid) values already in the field that might not be included in my new select list?  Will that be a problem?  Should I truncate the civicrm_cache table after the change?  Here's my process ... see if I'm going to run in the ditch, please. ;-)


1. Backup - (duh)
2. edit the custom field table, HTML column from "Text" to "Select"
3. Truncate the civicrm_cache table (I saw this recommended elsewhere, but not sure its really necessary)
4. Login as admin, edit the custom field to include the new select list items.
5. Modify any incorrect values to maintain data continuity in the field.
6. Celebrate!

Carl

[Hershel]

Another idea is to create a new field as you want it, and then copy whatever existing data you want from the old field to the new one, and then drop the old one.

[cfusch]

Hi! Hershel!

Yeah, I'd started to do that, but was looking for a simpler way, and now I know two ways to do this!  Creating a new table and moving, dropping, etc. seemed bigger than the other method, but at least you and Coleman have solved my problem!

As always, thank you for your help!

Carl

[cfusch]

Guys!

I got around to trying the simple route for changing a custom field from HTML text to a 'Select' (dropdown), but have run in to trouble. 

@Colman had a solution that looked straight forward and I like that because I have a number of fields to convert to drop-downs (Select HTML input type).

You can do this easily enough by modifying the civicrm_custom_field table in mysql. Change the html_type column.

Soooooo..... using PHPmyadmin, I went to the civicrm_custom table, located my custom field, "nsa_qualification" and changed the HTML column from 'Text' to 'Select'  (the data type remained as 'string').  Then logged in as admin, went to the custom field area and created two drop-down selections (options) for the revised field, saved.  Then went to a contact page to see if the revised field was actually working, i.e. giving a drop-down selection, but when I clicked on the 'Edit' button, editing mode would not open and the server timed out.

What have I overlooked??

Carl

[JonGold]

That sounds like a PHP error, which you should be able to see in your Apache error logs.  If you're running Debian or a derivative (e.g. Ubuntu), by default it's in /var/log/apache2/error.log.

[cfusch]

Hi, Jon!

Thanks!  Could you take another look, please?

Yes, I have debian on a virtualbox and the host is Ubuntu. However, I have tried this also on my staging server, a VPS at hostgator with the same results. Also tried Ubuntu.

After changing the HTML column in the civicrm_custom_field table from 'text' to 'Select' I added two test fields in the modified custom field in admin (see the attached) and get the following result when trying to save (also attached).

I *did* check the apache2 log, and yes, there is a PHP error complaining about memory allocation (on all platforms) ... PHP_Memory is 256M.

What else could be the problem?  Is is possible that I needed to do something other than make that one change to the civicrm_custom_field HTML column???  BTW: also tried truncating the civicrm_cache table ... still no cigar.

Carl

[JonGold]

Ah - well you've got a DB Syntax error.  So there's a problem with the SQL being generated.  If you turn on Debugging (Administer menu > System Settings > Debugging), preferably with backtrace, that last screenshot of yours will become a lot more helpful!

My wild guess is that there's something about how the data is being stored that isn't the same as if CiviCRM set up the field "properly".  Like maybe you need to escape those parentheses or something.  Seeing the debugging output should help.  Also, try creating this field "from scratch" and see if the SQL stored in the table looks different.

Also - phpMyAdmin doesn't show certain non-printable characters which might be in play here.  If you have access to command-line SQL, you may want to compare the "from scratch" field with this field in a terminal so you can see non-printable characters.

[cfusch]

Hi, Jon!  Thanks!

I'll go to work on your suggestion and see if I get a different result. I *have* created one from scratch and it worked okay, BUT I didn't look at it from command line SQL which I will do this time. 

I'll be back.

Carl

[JonGold]

Please post the backtrace too!  Just turn on debugging and backtrace and get to the same error screen, there will be a lot more information available.

[cfusch]

Jon,

Three screenshots are attached: 1) is the admin page for creating select options, and after trying to save I get 2) the debugging and backtrace, 3) MySQL tables.  I am not yet up to speed with civicrm internals, so can't make much sense of this ... perhaps you can.

With regards to mysql command line, note there is no ~new~ data produced by the modificationg because the saving the options blows-up.  I did however look at the old data and found a few with some control characters which I can go back and investigate, but you'd think that wouldn't make any difference to the HTML column change ... would it?

Are there any specific tables / fields that I should be looking at?

Carl

[JonGold]

The backtrace screenshot is getting cut off - could you make a pastebin of that text?

[cfusch]

sorry for the delay ... opened a pastebin account:

it is listed as civicrm-cfusch

Carl

[JonGold]

That doesn't work for me, could you please post the URL of that particular pastebin?