How can I remove items from the allowed values list of a select field that has data for the values?

Question

I've created a content type that has a list / select option field, and I've entered the key|value pairs as necessary for the select list to function.

Data has been entred, and it's been decided that certain terms no longer apply and that they should be deleted.

However, when trying to remove said terms, I get the following error:

Allowed values list: some values are being removed while currently in use.

Obviously, in the life of a project, values are going to change. What's a practical way to remove items once nodes are associated with the listed terms?

This is about the closest thing that I could find:

https://drupal.org/node/1653012

It references a d6 plugin and some patch trickery I'd prefer not to have to resort to. If I did have to ultimately resort to using the patch to remove the validation check on that field, is there any harm in leaving those items orphaned on the nodes they were associated with?

Update, I have come accross this issue again with a government client who, for the last 7 years of having a Drupal site has had 50 states and territories in a select list. Now, policy has changed and the territories no longer needed to be included. Being able to remove items from the select lists is important, and thus I'm offering a bounty.

I'm looking for a safe solution to be able to remove items from a select list. What I don't know is if that solution should update any of the nodes as I'm not sure how the field values are stored in relation to a node's total content.

I'm happy with a pure SQL solution to run in MySQL; or, I'm looking for a module.

Answer 1

I did something like this recently with the following approach.

1. Add the new allowed values.
2. Add a setting to configure "active" values.
3. Filter out the "inactive" values from display on the form.

e.g:

/**
 * Admin settings form
 */
function MODULE_admin_settings(){

  $form = array();

  // Select active preferences for display
  $field = field_info_field('field_preferences');
  $preferences = list_allowed_values($field);
  $form['field_preferences_active'] = array(
    '#type' => 'checkboxes',
    '#title' => t('Active preferences'),
    '#options' => $preferences,
    '#description' => t('Select the preferences available for user selection.'),
    '#default_value' => variable_get('field_preferences_active', array()),
  );

  return system_settings_form($form);

}

/**
 * Implements hook_field_attach_form
 */
function MODULE_field_attach_form($entity_type, $entity, &$form, &$form_state, $langcode) {

  // Filter out inactive preferences
  if(isset($form['field_preferences'])){
    $preferences = variable_get('field_preferences_active', array());
    foreach($preferences as $key => $preference){
      // If this preference isn't checked, but is set in the field values, unset it.
      if(empty($preference) && isset($form['field_preferences'][LANGUAGE_NONE]['#options'][$key])){
        unset($form['field_preferences'][LANGUAGE_NONE]['#options'][$key]);
      }
    }
  }

}

This way the legacy data is preserved for reference, the form validates, and the data integrity is intact.

Answer 2

As I know, all fields data is stored in 2 tables: field_data_field_FIELDNAME and field_revision_field_FIELDNAME. And I found confirmation of my thought here: https://stackoverflow.com/a/7773117/1300562

So to remove unnecessary field values you need to delete these values from above-mentioned tables and then remove them from the list of allowed values.

Step 1.

$values_to_remove = array('value1', 'value2'); // an array of unnecessary values
$fieldname = 'FIELDNAME'; // name of your field. For example,
                          // 'territory' for field with machine name 'field_territory'
$entity_type = 'node'; // it's 'node' in your case, but it can be 'taxonomy_term' or something else

db_delete('field_data_field_' . $fieldname)
  ->condition('entity_type', $entity_type)
  ->condition('field_' . $fieldname . '_value', $values_to_remove)
  ->execute();

db_delete('field_revision_field_' . $fieldname)
  ->condition('entity_type', $entity_type)
  ->condition('field_' . $fieldname . '_value', $values_to_remove)
  ->execute();

Step 2.
Remove unnecessary pairs of key|value on the field settings page and submit the form to save changes.
Cache should be cleared automatically after that, but if you still can see removed field values on node pages, clear the cache manually.

P. S. Recently I faced with similar problem, and now I prefer to use fields of type "Term reference" or (even better) "Entity reference" instead of list of text values. When using reference field, you can create separate vocabulary for each field and simply create/edit/delete terms at any time.

Answer 3

I think you actually can done this by using Views Bulk Operations module.

1. add a new option on that field you would like to replace. for example: na|NA
2. create a View to list node contain with that field
3. add "Bulk operations: Content" fields on that View
4. check "Modify entity values" and "show avaiable tokens"(select All on display values)
5. Add the field you want to change on Filter Criteria and "expose" that filters
6. set url Path on that View
7. Go to that view page and change
8. Now, use the expose and operations features to change the field option
9. Done

Answer 4

First of all check if you have any allowed values specified in the field? If you do, then an other option will not be validated. So try to remove the values from Field Settings tab first.

Alternatively you have 2 options:

---

1.

Remove all values that you've put in the allowed values list that are in-use by user accounts. For example, you can run a SQL query to find these:

SELECT * FROM field_data_field_MYFIELDNAME WHERE entity_type = 'user' and value = 'MY VALUE'

or create a user view that shows you what user accounts have the value you want to remove from the allowed values list.

---

2.

If you don't want to remove values from the fields, this can be achieved by hack.

Warning, this is not suggested solution for production and you should know what you're doing!

1. Find and edit modules/field/field.module

2. Find field_has_data() function and add return TRUE; in the first line of function.

   function field_has_data($field) {
     return FALSE; // HACK !!!
     $query = new EntityFieldQuery();
   

3. Re-save the field with values you want.
4. Remove the hack as soon as you do that.

Answer 5

I used kenorb's 2nd suggestion and it worked to update the list of values in a Drupal 7.52, Profile2 7.x-1.3 field. So if you were getting the drupal warning: “Allowed values list: some values are being removed while currently in use.” the following allowed me to remove values from the (profile2) field, without removing or replacing them in the database.

In Drupal core's root directory, there is a folder called modules, and the file to edit is located at: modules/field/field.module. THIS IS A CORE FILE, you must absolutely revert your changes when done updating values. I took the site offline, temporarily replaced the following code block in (drupal root)/modules/field/field.module

function field_has_data($field) {
  $query = new EntityFieldQuery();
  $query = $query->fieldCondition($field)
    ->range(0, 1)
    ->count()
    // Neutralize the 'entity_field_access' query tag added by
    // field_sql_storage_field_storage_query(). The result cannot depend on the
    // access grants of the current user.
    ->addTag('DANGEROUS_ACCESS_CHECK_OPT_OUT');

  return (bool) $query
    ->execute() || (bool) $query
    ->age(FIELD_LOAD_REVISION)
    ->execute();
}

WITH EXACTLY

function field_has_data($field) { 
    return FALSE; // hack 
    $query = new EntityFieldQuery();
}

And drupal stopped complaining and I was able to alter the list. (In my case, it’s faculty in the list of values that have left the university, but are still associated with a students record, as their advisor, mentor, etc.)

Answer 6

Here comes an improvement for H-L answer which I think is the best:

To sum up, you need to assign new values to content which has "old" values assigned for your select field.

Apart from Views Bulk Operations, you will need to install and enable Administration Views module. With this module, you already have an out-of-the-box view with bulk operations enabled (just see admin/content once enabled). Then:

1) Go to admin/structure/views an edit "Administration: Node" view

2) Add a new page display for the view using button "Add -> Page" on top

3) Asssign a path to the new display: Example admin/content/custom

4) Add a new filter for your select field: Select operator "is one of" and then select all options you want to delete

5) Save the view

6) Go to admin/content/custom Now you see all content that you need to bulk edit (change value for your select field)

7) Select all rows by clicking first checkbox in the left of the table (if there is more than one page, also select a button which will say "Select all X rows in this view")

8) Select operation "change value" and press "Execute"

9) For your select field, select a new value to overwrite the ones you want to delete

10) Select the checkbox for that select field

11) Click Next and you are done

Answer 7

It sounds like your Drupal problem is based on a deeper data problem: What happens to the entities which are currently using the depreciated list values? This question is at the root of the error message that Drupal is sending you.

Lets take a closer look at your state/territory example. Your client has been using a system that treats states and territories in the same way for years and has build up a huge group of nodes which contain both states and territories. Then one day, the powers that be decide that territories need to be handled differently and that the drop-down for assigning region should no longer contain territories. Great. Simply create a view that uses standard filters to render a list of all the territory nodes and use Views Bulk Operations to change all of their region values to... what... some 51th state called other maybe? The fate of the territories is a very serious question. Your solution must include a method for preserving or relocating the territory status. You may need to create a new list field called 'Territory' which can be used to identify areas that are territories, or create an entirely new data type for them.

You will need to use Rules with View Bulk Operations in order to perform these changes. If you don't know much about rules, please take some time to learn about how they work. Rules gives you the ability to manipulate information based on triggers, conditions and actions. After learning about rules, you may find that the answers you are looking will intuitively present themselves. Basically, You will need to create a rule, which is triggered by a Bulk Operation, that will target all of the territories and remove, reassign, rename or otherwise separate them from the main body of information. The Rule must be able to store the territory state in some way while at the same time, set the state dropdown to 'other' or 'N/A' status. This may be all that is needed. Otherwise...

After the reassignment is done, it should be a simple operation to alter the original list field and remove the territory names. However, if the system still does not allow you to alter the list, you may need to create a new list field, then use Views Bulk Operation and Rules to review all of the current state values and reassign them to the new list. Rules can work with Views Bulk Operations to target all of the relevant nodes and act upon them based on the field values. Setting a the value of a new list field based on the value of an existing list field for a group of nodes is easy when you use Rules.

Also remember, if Drupal gives you trouble with an operation, always flush the cache before considering a difficult alternative.

Answer 8

I would assume your client wants legacy content to hold to it's original value, meaning changing the select list will effectively destroy any previous data. If that's not a concern, then any of the other answers would probably work. If it is however, you can't really change the select list without loosing that data history. I might go a much simpler route, to allow for historical data, while making the site a little more future-proof - I would suggest using field permissions:

*set up a new field for that select list using a taxonomy instead of static data

*set the field permission for the existing select list as VIEW, but not EDIT by anyone except admin

With this, the old field should remain viewable and searchable (add a new title reflecting it as legacy only) but not editable. This of course depends heavily on custom searches, views, etc that may need to be adjusted.

I suggest this (as messy as it may sound) because removing that data, removes history, and that may end up being devastating in the long run. You could even use css to hide the old field in the edit node, and a hook to hide it for new content (where it doesn't have a value set). In this way, it would only show for that legacy content.

Of course, you could then take it a step further with a custom one-off module to copy the data from the old select list to the new taxonomy.

Answer 9

A simple drush script to the rescue! We update the field data and field revision tables and replace the old values with new ones before manually changing the field settings.

If we have something like this in our current field settings:

&date=today|today
&date=last2days|last2days

and want to replace it with the following:

date=today|today
date=last2days|last2days

We run the drush script first and then change the field settings in admin UI.

Note: This code is for a field with machine name field_foo_bar.

    $field_name = 'foo_bar';

    print "for {$field_name}...\n";

    replace_field("&date=today", "date=today", $field_name);

    replace_field("&date=last2days", "date=last2days", $field_name);

    function replace_field($old_value, $new_value, $field_name, $entity_type='node') {
      print "Replacing {$old_value} with {$new_value}...\n";
      $data_count = replace_options_data_field($field_name, $entity_type, $old_value, $new_value);
      $revision_count = replace_options_revision_field($field_name, $entity_type, $old_value, $new_value);
      print $data_count + $revision_count . " entries replaced.\n";
    }

    function replace_options_data_field($field_name, $entity_type, $old_value, $new_value) {
      $num_updated = db_update('field_data_field_' . $field_name)
        ->fields(array(
                   'field_' . $field_name . '_value' => $new_value,
                 ))
        ->condition('entity_type', $entity_type)
        ->condition('field_' . $field_name . '_value', $old_value)
        ->execute();
      return $num_updated;
    }

function replace_options_revision_field($field_name, $entity_type, $old_value, $new_value) {
  $num_updated = db_update('field_revision_field_' . $field_name)
    ->fields(array(
               'field_' . $field_name . '_value' => $new_value,
             ))
    ->condition('entity_type', $entity_type)
    ->condition('field_' . $field_name . '_value', $old_value)
    ->execute();
  return $num_updated;
}