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

[cfusch]

Sorry, Jon ... never used Pastebin. 

http://pastebin.com/vmabUzPU

Carl

[JonGold]

No worries!  So you know, you don't need to use a Pastebin account for public pastes.  And now that we have that error message, I'm pretty sure I see the problem!

If you look at the pastebin, you'll see the SQL being executed that is throwing the error:
SELECT count(*)
FROM   civicrm_custom_field
WHERE  option_group_id =

So it seems that you need to find the entry in civicrm_custom_field that corresponds to the field you modified, and enter a value for option_group_id.  That value should correspond to the entry in civicrm_option_group that stores your multiple-choice values for the field.

[cfusch]

Jon,

Okay, I'm getting closer, but looks like I'm still missing something.

The entry in civicrm_custom_field that corresponds to the field I modified is: "NSA_Qualification" (the unique custom field ID is 16, and the custom group ID is 3.)

Looking at custom group ID 3 in CiviCRM_custom_group shows a table, civicrm_value_sailing_qualifications_3, where the values for the SQL Select should be stored according to your last message.

Then going to table civicrm_value_sailing_qualifications_3, I see a column named, "nsa_qualification_16" for which there are many rows with orginal values that came from the imported DB that contains some dirty data (hence wanting to use drop-downs going forward).  The 2 selection options that I tried to create from civicrm Admin are nowhere to be found. So I'm lost at this point at a dead-end road. ;-)   

??Where?? am I to enter the option group id so that it will be found by the SQL statement?  And what do I put there? "3"? 

Sorry, I'm lost at this point, but I think I got close ... what now?

Carl

[JonGold]

Carl,

It looks like you were looking at civicrm_custom_group, but I was referring to civicrm_option_group.  Could you look again, this time at civicrm_option_group?

Please pastebin the following:
select id, name, label, option_group_id FROM civicrm_custom_field WHERE id = 16;

My understanding is that in that SQL, option_group_id will be NULL, but it has to have a value corresponding to your multiple choice options in civicrm_option_group.

[cfusch]

Jon,

Looking at the option_group, and not the civicrm_custom_group (duh)  the option_group_id is NULL.

It would make sense that the field is NULL because creation of the options was never successful when saving, but rather resulted in the error, right?

Here's the query:
http://pastebin.com/mpJGvwy5

Carl

[CiviTeacher.com]

Hershel's idea is how I would suggestion it be done - in this case.

1. export the custom text data
2. clean up the text data (normalize it)  (i.e. orange, oranges, ornge all become 'orange')
3. create new select field
4. import into that field

[JonGold]

Civiteacher is on to something!

I have an idea that entering the next available civicrm_option_group id would do the trick, but I'm not in a position to spend time figuring out if that would do damage.  I think the import option is probably safest at this point.

[cfusch]

 Yeah, if I'd done what Hershel suggested, I'd be done by now even with the remaining 20 or so columns like it!     I'll move on with his suggested method.

I'll also try adding the next in line ID just for grins, and will do it in my own backyard, then report it back to you guys at a later date.

{ Sorry, Hershel ... Next time I'll move swiftly forward with your suggestions. }

Carl

[Hershel]

Yes, on occasion I do have good ideas.

Anyhow this thread may be useful for others in the future.

[petednz]

so another option to consider (maybe for others in the future) would be to see if Views Bulk Operations and the civicrm entities module can let you eg search for all contacts with eg "oran" and then set the new field for them to be "orange" if that is the scenario

can't quite recall if civicrm entities has been taken as far as updating custom fields but