1

I'm wondering if there is a simple way to change an attribute from dropdown to text field? We had a development shop set up our Magento website but they made the fields dropdown and we have entered considerable data and would like to change it to text field now that dropdown is proving to be a considerable hassle... even if the data isn't imported during the change... I'm sure we can reimport it. But I'd like to avoid having to delete the attributes.

I've seen Magento Code for Text to Dropdown and Dropdown to Multiselect... but I haven't found any for Dropdown to Textfield.

2

If you're OK doing this with a query, I have a couple of update statements that should change the data-type from int to varchar (should be the case for all non-numeric text fields), changes input-type from select to text, and migrates all of the existing data into the correct tables. You should be able to run these in either order, then just reindex and you're done!

Insert values from the old table into the new table:

insert into catalog_product_entity_varchar
(entity_type_id, attribute_id, store_id, entity_id, value)
(
    select 
        cpei.entity_type_id, 
        cpei.attribute_id, 
        cpei.store_id, 
        cpei.entity_id, 
        eaov.value
    from eav_attribute_option eao
    join eav_attribute_option_value eaov
    on eao.option_id = eaov.option_id
    join catalog_product_entity_int cpei
    on eao.option_id = cpei.value
    where cpei.attribute_id = [one or more attribute_ids of your choosing]
)

Update attribute settings:

UPDATE eav_attribute
SET 
    backend_type = 'varchar',
    frontend_input = 'text'
WHERE attribute_id = [the same attribute_ids from above];

Notes:

If you use a third-party extension for your layered navigation, you'll have to investigate those tables to see if anything needs updating, and if you generally use these as filters in the layered nav, there may be some undesired results (I have not tested the script). If that is the case, I would recommend opening and saving the attributes from admin after running the script.

For multistore use a store_id indicator

insert into catalog_product_entity_varchar
(entity_type_id, attribute_id, store_id, entity_id, value)
(
    select 
        cpei.entity_type_id, 
        cpei.attribute_id, 
        eaov.store_id, 
        cpei.entity_id, 
        eaov.value
    from eav_attribute_option eao
    join eav_attribute_option_value eaov
    on eao.option_id = eaov.option_id
    join catalog_product_entity_int cpei
    on eao.option_id = cpei.value
    where cpei.attribute_id = 131
    and eaov.store_id = 0
)
3
  • How does the first query work if there are multiple store_ids we are getting a unique key index error. – snh_nl Feb 16 at 10:51
  • Done both scripts (thanks!) however when we edit a product in the backend we still see it retrieves the old INT value from the select option. The code UPDATE eav_attribute SET backend_type = 'varchar', frontend_input = 'text' WHERE attribute_id = 131; was run. We confirmed in backend that the new attribute is indeed op type 'Text' ... after checked table catalog_product_entity_varchar and compared the new entry for id 131 with an existing text attribute. Looks the same. How come the admin product page is still retrieving the old "int id" from select option – snh_nl Feb 16 at 11:05
  • @snh_nl - Any data with a non-zero store_id will override the value for store_id = 0, so when you edited this for multistore use, it specifically needs to NOT have and eaov.store_id = 0 as a condition. Please let me know if removing this from your query fixes the problem as expected, and I will rollback the answer. – mtr.web Feb 16 at 19:26

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.