How to correct altered key|label pairs in "select list" fields (and corresponding data) after migrating to Drupal 7?

Question

I am most of the way through migrating a Drupal 5 site through Drupal 6 to Drupal 7. The CCK Content Migrate did a great job with one exception: all fields in "select list" format have one of several issues with the key|label pairs in their "allowed values" list.

If the original "allowed valued" list did not use key|label pairs, like this:

Alabama
Alaska
Arizona 

The allowed values were automatically updated to key|label format when they were migrated to Drupal 7, like this:

1|Alabama
2|Alaska
3|Arizona

This wouldn't be so bad, except that the actual data in the database did not change accordingly, so you would see "Alabama" instead of "1" for existing data. Presumably, any new data would store "1" instead "Alabama". Also, when editing a node, the value "-None-" is pre-selected in the select list despite the fact that data exists for that field.

In some cases the "allowed valued" list did use key|label pairs (with a string as the key), like this:

movie|Movie
television|Television
product_or_service|Product or Service

In these cases, the key is stored in the database. As in the previous case, the value "-None-" is pre-selected when editing a node (instead of showing the existing data). The key|label pairs were not changed when migrating to Drupal 7, although I noticed that spaces were removed, for example:

product_or_service | Product or Service

became:

product_or_service|Product or Service

But the issue here is that the key is displayed in the node (like product_or_service) instead of the label (like Product or Service).

In all cases, exposed filters created from these select lists in Views return no results. I tried creating exposed filters with other fields that were not select lists (like text fields) and those worked just fine (once "use AJAX" was selected in that View's Advanced Options).

So the question is: what's the best way to move forward with these fields? I'd like to make consistent use of key|label data for both existing and new nodes. And I would love to avoid backtracking to Drupal 6 to start the migration process over again if at all possible!

I have only found two references to this issue online. One person seems to have updated the data manually after encountering the same problem (see the single comment on this post). I am not opposed to a solution like this since we don't have too many nodes, but I would need some pretty specific directions on how to do it (I access my database via phpMy Admin, if you know what I mean).

Another person seems to have fixed it in Drupal 6 - but I am hoping to avoid backtracking to 6 since we're already in Drupal 7 and are almost done (except for this)! I'd probably need specific directions in this case as well.

Has anyone been through this? Or know a smart/simple solution? Any thoughts or suggestions would be very much appreciated. Thank you!

Answer 1

1) Replace numeric values in DB with correct string. I would use php to do this, something like:

$countries = db_query('SELECT nid, field_country_value FROM field_data_field_countries')->fetchAll();

$search = array(1,2,3);
$replace = array('Alabama', 'Alaska', 'Arizona');

while($country = db_fetch_array($countries)) {
    $country_name = str_replace($search, $replace, $country['field_country_value']);
    db_query('UPDATE field_data_field_countries SET field_country_value=:field_country_value WHERE nid=:nid', 
        array(':field_country_value'=>$country_name, ':nid'=>$country['nid']));
}

This can be run from any custom module. (I apologies, some of that is in Drupal 7 not Drupal 6 code but you get the idea)

2) Make sure the keys match the data in the DB - once they are the same then when you edit the node it should have the correct value selected. If you are having problems then do comparisons between the key's value and the value stored in the DB - this should highlight any minor differences such as extra spaces...