CiviCRM Community Forums (archive)

I have Org records that for membership sign up reasons [see:http://issues.civicrm.org/jira/browse/CRM-9586] need their subtype reverting to standard Organizational record.

I can find my way around the SQL for the most part but is it possible or is the custom data somehow specific to the subtype name/id? I can't see yet that this is the case but am asking here before I butcher the db

Were you able to batch update the org sub-type from the general org to a more specific one without loosing custom data or did they always have the subtype?  Reason I ask is cause I am trying to do exact the opposite of what you are doing with batch update and a profile with one field where that field is the sub-type so I can move general orgs down into more specific ones.  I just don't see the field value to be able to set this. Sub-Type doesn't display anything for me in the drop-down list. I'm also using 4.1.1  -thanks

Actually have had success doing this with two SQL queries. I am checking results now. I think it will be more complicated round the other way

Hiya,
Here is my SQL.
We had subtype of schools - i wanted to keep the custom fields mapped to schools subtype. So far I have found that below seemed to be the key. Your scenario is the reverse though as I understand it.

UPDATE `xxx`.`civicrm_contact` SET `contact_sub_type` = NULL WHERE `civicrm_contact`.`contact_sub_type` = "School";

UPDATE `xxx`.`civicrm_custom_group` SET `extends_entity_column_value` = NULL WHERE `extends_entity_column_value` = "School";

So off top of my head you would need something like:

UPDATE `xxx`.`civicrm_contact` SET `contact_sub_type` = "MYSUBTYPE" WHERE `civicrm_contact`.`contact_sub_type` = NULL;

UPDATE `xxx`.`civicrm_custom_group` SET `extends_entity_column_value` = "MYSUBTYPE" WHERE `extends_entity_column_value` = NULL;

No idea if this woul dwork and damage is huge so backup everything 3 times before changing anything