Author Topic: How to change custom field input from "text" to "select" (dropdown select) (Read 2106 times)

cfusch · July 10, 2014, 11:24:59 am

Sorry, Jon ... never used Pastebin.

http://pastebin.com/vmabUzPU

Carl

JonGold · July 10, 2014, 11:51:05 am

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 · July 10, 2014, 01:15:16 pm

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 · July 10, 2014, 02:31:52 pm

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 · July 10, 2014, 03:55:02 pm

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 · July 11, 2014, 08:44:57 am

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 · July 11, 2014, 09:43:50 am

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 · July 11, 2014, 10:20:05 am

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 · July 13, 2014, 10:27:40 am

Yes, on occasion I do have good ideas.

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

petednz · July 13, 2014, 03:06:11 pm

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

CiviCRM Community Forums (archive)

News:

Author Topic: How to change custom field input from "text" to "select" (dropdown select) (Read 2106 times)

cfusch

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

JonGold

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

cfusch

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

JonGold

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

cfusch

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

CiviTeacher.com

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

JonGold

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

cfusch

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

Hershel

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

petednz

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