Author Topic: Custom Data, Changing Types: Text Input -> Select (Read 1426 times)

kwixson · October 23, 2009, 07:40:24 am

We have a custom data field for contributions that is set up as a text input field, and now the primary user wants it set up as a dropdown (select) field instead. How do I transform the text input field into a select field without losing the data in the database for past entries?

Michał Mach · October 26, 2009, 03:36:35 am

The easiest way (coding-wise) that comes to my mind is exporting the contents of the field, preparing the dropdown custom field based on unique values from the exported data and importing them back, but to the new field this time. Otherwise, you might need to write a script "talking" directly to the database to do the work for you. If you get to the script writing stage, please consider sharing it with the community as an example on how to approach this.

Thx,
m

kwixson · October 27, 2009, 01:19:56 pm

There are over 83 thousand records for which I would need to export the values and then reimport. From what I've been told, you have to divide the exported file into smaller files of limited number of lines (I was told 250, but I've found that 1000 line files works okay) and that I have to import all of these files once for each kind of record (individual, household and organization.) With 1000 line files that translates into 168 individual imports/uploads (there are only individuals and organizations) which amounts to hours of work, just to change the input type on the form from text to select. Are you sure there's no other way?

Now I'm getting feedback that this is not the only field they might want changed from text to select.

Donald Lobo · October 27, 2009, 01:26:57 pm

i would attempt to do the following:

1. Run a distinct query across your "text" column to get your select options. Most likely you will need to clean up and standardize these values to make the list a bit more manageable

2. Create a new field of type select using the distinct list from above as your select options

3. Run another update query that populates the select value in the custom value table based on the text column

4. delete the text column

this procedure assumes you know some amount of sql

lobo

kwixson · October 27, 2009, 01:55:17 pm

Yeah, not really sure how to reliably generate an update query for 83 thousand key => value pairs, unless you think I can use PHPmyadmin to export just those fields (id and custom data field) and then simply change the name of the field before importing the previously exported SQL file.

Donald Lobo · October 27, 2009, 02:02:20 pm

something like:

Code: [Select]

update civicrm_value_extended_care_signout_3 set select_column = '3' where text_column = 'option for three';

repeat the above for all select options

to get the list of distinct text columns

Code: [Select]

select distinct(text_column) from civicrm_value_extended_care_signout_3

if the above sounds like a foreign language, u might want to consider hiring someone from http://civicrm.org/professional/

lobo

CiviCRM Community Forums (archive)

News:

Author Topic: Custom Data, Changing Types: Text Input -> Select (Read 1426 times)

kwixson

Custom Data, Changing Types: Text Input -> Select

Michał Mach

Re: Custom Data, Changing Types: Text Input -> Select

kwixson

Re: Custom Data, Changing Types: Text Input -> Select

Donald Lobo

Re: Custom Data, Changing Types: Text Input -> Select

kwixson

Re: Custom Data, Changing Types: Text Input -> Select

Donald Lobo

Re: Custom Data, Changing Types: Text Input -> Select