7

I have a content type with an integer content field. The field needs to contain float data. What's the easiest way to change the field type from integer to decimal? (There are thousands of existing nodes I will have to convert).


I've found a solution in this comment and this comment. Is it the best?

  • No. That deals with Drupal 6 and CCK, this question is about Drupal 7. Fields in D7 are very similar to CCK in D6, but not identical. The general approach may be fine, but you will need to make tweaks to get it to work on D7. – Eelke Blok May 21 '15 at 7:29
8

Just to merge all the previous suggestions into one convenient chunk:

// Change this to your field name, obvs.
$field = 'field_myfieldname';

// Update the storage tables
$tables = array('field_data', 'field_revision');
foreach ($tables as $table) {
  $tablename = $table .'_'. $field;
  $fieldname = $field .'_value';
  db_change_field($tablename, $fieldname, $fieldname, array(
    'type' => 'numeric',
    'precision' => 10,
    'scale' => 2,
    'not null' => FALSE,
  ));
}

// Fetch the current field configuration
$field_config = db_query("SELECT data FROM {field_config} WHERE field_name = :field_name", array(
    ':field_name' => $field,
  ))
  ->fetchObject();
$data = unserialize($field_config->data);

// Update the settings entry
$data['settings'] = array(
  'precision' => 10,
  'scale' => 2,
  'decimal_separator' => '.',
);

// Store the new field config, update the field type at the same time
db_update('field_config')
  ->fields(array(
    'data' => serialize($data),
    'type' => 'number_decimal',
  ))
  ->condition('field_name', $field)
  ->execute();    

// If you are confident about what bundles have instances of this field you can
// go straight to the db_query with a hardcoded entity_type / bundle.
$instances = field_info_field_map();
foreach ($instances[$field]['bundles'] as $entity_type => $bundles) {
  foreach ($bundles as $bundle) {

    // Fetch the field instance data
    $field_config_instance = db_query("SELECT data FROM {field_config_instance}
                                       WHERE field_name = :field_name
                                       AND entity_type = :entity_type
                                       AND bundle = :bundle", array(
        ':field_name' => $field,
        ':entity_type' => $entity_type,
        ':bundle' => $bundle,
      ))
      ->fetchObject();
    $data = unserialize($field_config_instance->data);

    // Update it with the new display type
    $data['display']['default']['type'] = 'number_decimal';

    // Store it back to the database
    db_update('field_config_instance')
      ->fields(array('data' => serialize($data)))
      ->condition('field_name', $field)
      ->condition('entity_type', $entity_type)
      ->condition('bundle', $bundle)
      ->execute();

  }
}
|improve this answer|||||
  • One more addition; when you have a non-standard configuration with different display modes, the line marked "Update it with the new display type" might wreak havoc. Instead, you should loop over all displays, first check if the field isn't hidden in that display, and update it if it isn't. Edit: Apparently, I can't post code in a comment. – Eelke Blok May 21 '15 at 8:20
  • 1
    Excellent solution. I've copied it to function accessible by hook_update_n, and am off to the races! Note: And views, panels, etc that display the field will need updating as well. The deployment of this in a hook_update_n is likely to cause a hiccup and require a little shuffle between field change and exported feature revert if that's your bag. – texas-bronius Jun 11 '15 at 19:39
5

A better approach might be to use Drupal's built-in capability to alter the field type, db_change_field().

db_change_field('field_data_field_number', 'field_number_value', 'field_number_value', array(
  'type' => 'numeric',
  'precision' => 10,
  'scale' => 2,
  'not null' => FALSE,
));
|improve this answer|||||
  • It's important to note this is not a complete solution, just a possible improvement over other solutions given; this only changes the type of the database column (field), it doesn't do the entire Drupal field conversion. Basically, it replaces the raw ALTER query given elsewhere. – Eelke Blok May 21 '15 at 7:26
4

I just wrote some code that should handle this for you in my Helper module: http://cgit.drupalcode.org/helper/tree/lib/FieldChangeHelper.php?h=7.x-1.x

FieldChangeHelper::changeType('my_int_field_name', 'number_decimal');

It should work fine for most simple cases and fields, but should be tested thoroughly. It also currently doesn't update any field formatters, so you'll want to review the display configuration for the field after running this.

|improve this answer|||||
  • This looks very nice! Could you please give some pointers how to use your modules for the newbies out here :) – petergus Nov 5 '14 at 15:07
  • Would something like this work for converting a text to link type? – Sonicthoughts Nov 15 '14 at 23:24
  • This does work quite well even with the latest drupal version (currently 7.34). – todinov Dec 16 '14 at 9:42
  • This doesn't allow to set precision and scale. By default, it set precision to 10 and scale to 2. If you need to set different values, take a look to KingAndy answer. – Camilo Feb 8 '16 at 2:48
4

Dave Reids drupal.org/project/helper module above is a good simple solution.

Can't add comment above so posting steps here.

1) Download and enable Helper module

2) Create a new file: changeIntField.php

<?php
FieldChangeHelper::changeType('my_int_field_name', 'number_decimal');
?>

3) Save file in root folder where drupal is installed

4) drush scr changeIntField.php

5) Delete file changeIntFiled.php as you no longer need it.

6) Edit display format for node/entity where field is being used.

7) Edit Views - where the field is used and fix display format settings here as well.

|improve this answer|||||
  • This worked great! If you have the Devel module installed, you can also run this code directly on the Excute PHP Code page. – ovi Dec 2 '15 at 16:55
  • P.S. you have a small typo in your drush command. it should be drush scr – ovi Dec 2 '15 at 16:56
  • Access the php execute section devel here: /devel/php – Sam Thompson Jul 22 '16 at 18:09
  • Why not just drush php-eval "FieldChangeHelper::changeType('my_int_field_name', 'number_decimal');"? – milkovsky Mar 9 '17 at 14:23
2

Changing the type of a Drupal 7 content field from integer to decimal is quite simple. There is no need to create a new field and then copy definitions. I've used the following three steps:

  1. Find the name of the field's table and the name of the field in that table where the field's value is stored, using MySQL.

  2. Use the MySQL alter statment to alter the field's datatype:
    ALTER TABLE field_data_field_myfield MODIFY field_myfield_value decimal(10,2) ;

  3. Change the field definitions in Drupal:
    UPDATE field_config SET type = 'number_decimal' WHERE field_name = 'field_myfield';

Steps 1-3 are enough. Although, in certain cases it might be beneficial to compare the properties of an integer and a decimal fields on the field_config table to see if there is anything else which needs to be changed: SELECT id, field_name, type, CONVERT(data USING utf8) FROM field_config WHERE type like 'number_%'.

|improve this answer|||||
2

Note that there is another table for each field which is called field_revision_field_{field_name} and this table mysql column type should also change

Also decimal field has different settings which should be set, use the following script to unserialize field settings modify it and then serialize it again. There are two table field_config and field_config_instance. the following is for field_config

$s = unserialize('field_config table serialize string');

$s['settings'] = array (
    'precision' => '20',
    'scale' => '0',
    'decimal_separator' => '.',
);

print serialize($s);

here is for field config instance :

$s = unserialize('field_config_instance table serialize string');

$s['display']['default']['type'] = 'number_decimal';

print serialize($s);

NOTE : fields may may more than one instance, and the next code should be used for all instances.

Now clear Drupal's cache and head to field settings , edit the field and save it. If you receive no error you're probably good to go :)

|improve this answer|||||
1

I had to change a field called "field_margin_percentage" to a decimal

Step 1 - Updated the database fields

db_change_field('field_data_field_margin_percentage', 'field_margin_percentage_value', 'field_margin_percentage_value', array(
  'type' => 'numeric',
  'precision' => 10,
  'scale' => 2,
  'not null' => FALSE,
));

Step 2 - Update the field_config so the field knows the correct handler

db_query("UPDATE field_config SET type = 'number_decimal' WHERE field_name = 'field_margin_percentage'");

Step 3 - Get the existing 'data' column from field_config and add the decimal settings

$current_field_config = 'a:7:{s:12:"translatable";s:1:"0";s:12:"entity_types";a:0:{}s:8:"settings";a:0:{}s:7:"storage";a:5:{s:4:"type";s:17:"field_sql_storage";s:8:"settings";a:0:{}s:6:"module";s:17:"field_sql_storage";s:6:"active";s:1:"1";s:7:"details";a:1:{s:3:"sql";a:2:{s:18:"FIELD_LOAD_CURRENT";a:1:{s:34:"field_data_field_margin_percentage";a:1:{s:5:"value";s:29:"field_margin_percentage_value";}}s:19:"FIELD_LOAD_REVISION";a:1:{s:38:"field_revision_field_margin_percentage";a:1:{s:5:"value";s:29:"field_margin_percentage_value";}}}}}s:12:"foreign keys";a:0:{}s:7:"indexes";a:0:{}s:2:"id";s:3:"161";}';

$s = unserialize($current_field_config);

$s['settings'] = array (
    'precision' => '10',
    'scale' => '2',
    'decimal_separator' => '.',
);

print serialize($s); // This will give you the new value for the data column (see below)

Step 4 - Get the existing 'data' column from field_config_instance and add the decimal settings

$s = unserialize('a:7:{s:5:"label";s:17:"Margin Percentage";s:6:"widget";a:5:{s:6:"weight";s:2:"13";s:4:"type";s:6:"number";s:6:"module";s:6:"number";s:6:"active";i:0;s:8:"settings";a:0:{}}s:8:"settings";a:7:{s:3:"min";s:1:"0";s:3:"max";s:3:"100";s:6:"prefix";s:0:"";s:6:"suffix";s:0:"";s:26:"commerce_user_profile_pane";i:0;s:35:"commerce_user_profile_pane_required";i:0;s:18:"user_register_form";b:0;}s:7:"display";a:1:{s:7:"default";a:5:{s:5:"label";s:5:"above";s:4:"type";s:14:"number_integer";s:6:"weight";s:2:"12";s:8:"settings";a:4:{s:18:"thousand_separator";s:1:" ";s:17:"decimal_separator";s:1:".";s:5:"scale";i:0;s:13:"prefix_suffix";b:1;}s:6:"module";s:6:"number";}}s:8:"required";i:0;s:11:"description";s:115:"The Paddy\'s Seals % of this sale. You can enter this OR the Margin Value. The other one will be calculated for you.";s:13:"default_value";a:1:{i:0;a:1:{s:5:"value";s:1:"0";}}}');

$s['display']['default']['type'] = 'number_decimal';

print serialize($s);

Step 5 - Use the above serialized strings to update your 'data' columns in field_config and field_config_instance

db_query('update field_config set data=\'a:7:{s:12:"translatable";s:1:"0";s:12:"entity_types";a:0:{}s:8:"settings";a:3:{s:9:"precision";s:2:"10";s:5:"scale";s:1:"2";s:17:"decimal_separator";s:1:".";}s:7:"storage";a:5:{s:4:"type";s:17:"field_sql_storage";s:8:"settings";a:0:{}s:6:"module";s:17:"field_sql_storage";s:6:"active";s:1:"1";s:7:"details";a:1:{s:3:"sql";a:2:{s:18:"FIELD_LOAD_CURRENT";a:1:{s:34:"field_data_field_margin_percentage";a:1:{s:5:"value";s:29:"field_margin_percentage_value";}}s:19:"FIELD_LOAD_REVISION";a:1:{s:38:"field_revision_field_margin_percentage";a:1:{s:5:"value";s:29:"field_margin_percentage_value";}}}}}s:12:"foreign keys";a:0:{}s:7:"indexes";a:0:{}s:2:"id";s:3:"161";}\' where field_name=\'field_margin_percentage\'');

db_query('update field_config_instance set data=\'a:7:{s:5:"label";s:17:"Margin Percentage";s:6:"widget";a:5:{s:6:"weight";s:2:"13";s:4:"type";s:6:"number";s:6:"module";s:6:"number";s:6:"active";i:0;s:8:"settings";a:0:{}}s:8:"settings";a:7:{s:3:"min";s:1:"0";s:3:"max";s:3:"100";s:6:"prefix";s:0:"";s:6:"suffix";s:0:"";s:26:"commerce_user_profile_pane";i:0;s:35:"commerce_user_profile_pane_required";i:0;s:18:"user_register_form";b:0;}s:7:"display";a:1:{s:7:"default";a:5:{s:5:"label";s:5:"above";s:4:"type";s:14:"number_decimal";s:6:"weight";s:2:"12";s:8:"settings";a:4:{s:18:"thousand_separator";s:1:" ";s:17:"decimal_separator";s:1:".";s:5:"scale";i:0;s:13:"prefix_suffix";b:1;}s:6:"module";s:6:"number";}}s:8:"required";i:0;s:11:"description";s:115:"The Paddy-s Seals % of this sale. You can enter this OR the Margin Value. The other one will be calculated for you.";s:13:"default_value";a:1:{i:0;a:1:{s:5:"value";s:1:"0";}}}\' where field_name=\'field_margin_percentage\'');
|improve this answer|||||
0

Here's how I did it Drupal 7 in an update_hook_N().

The plan was to change one field called "field_ui_current_salary" from DECIMAL(10) to DECIMAL(10,2) which would allow 2 decimal points for the values instead of no decimals at all!

/**
 * Changing Database Currenty Salary field to allow decimals.
 * Replace MYMODULE with your module's name and number N with your next incremented value.
 */
function MYMODULE_update_7001() {
  // field_ui_current_salary scale = 2 in field_config table.
  // Use your 'data' value for $new_data from 'field_config' table for your field and just change where 'scale' is from 0 to 2 (or your desired number of decimals)
  $new_data = 'a:8:{s:12:"entity_types";a:0:{}s:17:"field_permissions";a:1:{s:4:"type";i:0;}s:7:"indexes";a:0:{}s:8:"settings";a:5:{s:3:"max";d:99999999.989999995;s:3:"min";d:-99999999.989999995;s:9:"precision";i:10;s:16:"profile2_private";b:0;s:5:**"scale";i:2;**}s:12:"translatable";i:0;s:7:"storage";a:5:{s:4:"type";s:17:"field_sql_storage";s:8:"settings";a:0:{}s:6:"module";s:17:"field_sql_storage";s:6:"active";s:1:"1";s:7:"details";a:1:{s:3:"sql";a:2:{s:18:"FIELD_LOAD_CURRENT";a:1:{s:34:"field_data_field_ui_current_salary";a:2:{s:6:"amount";s:30:"field_ui_current_salary_amount";s:8:"currency";s:32:"field_ui_current_salary_currency";}}s:19:"FIELD_LOAD_REVISION";a:1:{s:38:"field_revision_field_ui_current_salary";a:2:{s:6:"amount";s:30:"field_ui_current_salary_amount";s:8:"currency";s:32:"field_ui_current_salary_currency";}}}}}s:12:"foreign keys";a:0:{}s:2:"id";s:3:"372";}';
  db_query('UPDATE {field_config} SET data = :new_data WHERE field_name = :salary;', array(':new_data' => $new_data, ':salary' => 'field_ui_current_salary'));

  // field_data_field_ui_current_salary and its revision copy -> DECIMAL(10,2)
  db_query("ALTER TABLE {field_data_field_ui_current_salary} MODIFY field_ui_current_salary_amount DECIMAL(10,2);");
  db_query("ALTER TABLE {field_revision_field_ui_current_salary} MODIFY field_ui_current_salary_amount DECIMAL(10,2);");

  cache_clear_all();
}
|improve this answer|||||
0

The original question was regarding Drupal 7, but I suspect a lot of people are wondering how to do this in Drupal 8. Unfortunately, there isn't any good method for this at the moment. The least bad option is to simply delete the field (and any data it contains) and let Drupal recreate it from scratch.

I'm trying to find a better solution and will post here if and when it's available: https://www.drupal.org/node/2843108

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.