Answers.Body: <p>It's good to be thinking about where the data is stored, but don't let your thinking ahead get in the way of getting started somewhere.  It's not unusual for organizations using CiviCRM heavily to decide they need to remap data somehow, and the fact that the data is in CiviCRM somewhere already makes it one step easier.  <strong>The short answer is that you should put your data in the place that makes the most sense right now, and if you have to move it later, it'll take a little bit of work, but nothing overwhelming.</strong></p>
  
  <p>Since you're worried about just some potential future place to store data, I'll have to be a bit abstract, but the principle should hold.</p>
  
  <p>There are two main tasks you'd face if you were to decide that your data in custom fields really belongs in different custom fields or an extension's ad-hoc tables.  The first is moving data, and the second is any transformation that may be necessary.</p>
  
  <p>Using SQL queries, moving data to and from custom fields is relatively easy.  You'll end up doing something like</p>
  
  <pre><code>INSERT INTO civicrm_value_newtable
  SELECT NULL as id, entity_id, oldfield AS newfield
  FROM civicrm_value_oldtable
  </code></pre>
  
  <p>to move the data from one custom field to another.  Of course, you could also export to CSV and then import from CSV, mapping the old field's column to the new field.  Since you have the contact ID, you know who a value belongs to.</p>
  
  <p>The more challenging aspect is if you need to transform the data.  Maybe you have three categories, and the new system takes four.  Maybe you have two fields where the new system has one, or vice-versa.</p>
  
  <p>Not knowing what situation you might encounter, the only thing you can do is to pick the most useful and accurate ways to store your data now.  The better your data now reflects reality, the more reliably you can transform it into something else.</p>
  
  <p>When it does come time to transform, you can do SQL functions; and export, alter within CSV, and import; or a series of SQL insert queries, such as</p>
  
  <pre><code>INSERT INTO civicrm_value_newtable
  SELECT NULL AS id, entity_id, "option1" AS newfield
  FROM civicrm_value_oldtable
  WHERE oldfield = "A";
  
  INSERT INTO civicrm_value_newtable
  SELECT NULL AS id, entity_id, "option2" AS newfield
  FROM civicrm_value_oldtable
  WHERE oldfield = "B"
    OR oldfield = "C";
  </code></pre>
  
  <p>and so on for each new possible value.</p>
  
  <p>In sum, don't let the perfect be the enemy of the good, and don't work so hard to prepare for vague possibilities that it's not functional now.  "<a href="https://youtu.be/nRLOf3bSlVs" rel="nofollow">If it's a life of possibilities that you wanna live....</a>"</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-09T03:01:16.813Z
Answers.LastActivityDate: 2015-08-09T03:01:16.813Z
Answers.UserDisplayName: Andie Hunt
Answers.Body: <p>It's often easiest for extensions to build on top of custom-data, so several CiviHR modules do that. In fact, CiviHR prompted development of a code-generator for custom-data. Take a look at <a href="http://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Module+Extension" rel="nofollow">Create a Module Extension</a> and especially the section "Add Custom Fields".</p>
  
  <p>It is possible for an extension to create custom SQL tables. This is discussed a little bit in the section "Add a database upgrader / installer / uninstaller" (which produces skeletal code and a bunch of comments). You can work with the new tables using <code>CRM_Core_DAO::executeQuery()</code>. (Also: In a pinch, <a href="https://github.com/civicrm/civicrm-core/blob/master/CRM/Utils/SQL/Select.php" rel="nofollow"><code>CRM_Utils_SQL_Select</code></a> and <a href="https://github.com/civicrm/civicrm-core/blob/master/CRM/Utils/SQL/Insert.php" rel="nofollow"><code>_Insert</code></a> may help.)</p>
  
  <p>Some extensions go a step further and create DAO classes (in the same style as core code), although the technique is generally experimental/unsupported. You'd have to carefully trace some example modules (like CiviVolunteer and CiviHR's job module) and talk with their authors about how they work.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-08-09T00:30:09.530Z
Answers.LastActivityDate: 2015-08-09T00:30:09.530Z
Answers.UserDisplayName: Tim Otten
Body: <p>I'm still figuring out how to organize our organization's data before importing it into CiviCRM. One question that came up is whether it would be possible (through the UI or through SQL) to move some custom field set data from a contact type to some CiviCRM component or extension, if such a need arises later on.</p>
  
  <p>To be more specific, I'm thinking of creating a custom field set for the Individual contact type. The field set would store data about an individual's qualifications. This is all well and good, but what if later on it starts making more sense to have the data about individuals' qualifications in some CiviCRM component or extension (e.g. CiviHR)? Then I imagine this data would have to be moved from the custom field set to that component/extension.</p>
  
  <p>I know that in CiviCRM all data across components and extensions is integrated. But who knows, it may turn out that it is much more convenient to have the data about people's qualifications in CiviHR (or other) rather than have it as a custom field set. I don't know how things might develop in the future. And that's the point. I would much rather have the ability to juggle around the data and organize it the way I want to. This kind of flexibility seems important to me, especially since I don't think I'd get the data organizing right the first time.</p>
  
  <p>Or if there are some fundamental flaws in my thinking and my question doesn't make sense, please let me know. Thanks in advance!</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-08-09T00:11:59.067Z
Comments.Text: Hi. Not sure I follow the real outcome here. What do you see is the problem with just retaining the Custom Fields for Qualifications on the Individual? Why might it make more sense to then move it to CiviHR? And what other sort of 'CiviCRM component' might you be thinking about? Qualifications sounds like a very good use case for Custom Field
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-08-09T10:23:06.930Z
Comments.Text: @petednz I'm not seeking any outcome per se, I was just concerned about a possible future need to move data from one place to another. It could be that if qualification data is stored in CiviHR or CiviVolunteer, going to those extensions would provide a better overview than if that data is not stored in them. Andrew Hunt's answer gave me the reassurance I needed that in the future I can change any choices that I'm making right now.
Comments.UserDisplayName: Borislav Zlatanov
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2015-08-10T03:48:37.860Z
Comments.Text: cool. i think the other question is 'where does civihr store its data'. would it be different to a civi custom field?
Comments.UserDisplayName: petednz - fuzion
Tags: <extension><custom-fields><import>
Title: Is it possible to move data from a contact type to a component/extension?
ThreadText: # Is it possible to move data from a contact type to a component/extension?
  
  (Asked by: username_0 on 2015-08-08)
  
  I'm still figuring out how to organize our organization's data before importing it into CiviCRM. One question that came up is whether it would be possible (through the UI or through SQL) to move some custom field set data from a contact type to some CiviCRM component or extension, if such a need arises later on.
  
  To be more specific, I'm thinking of creating a custom field set for the Individual contact type. The field set would store data about an individual's qualifications. This is all well and good, but what if later on it starts making more sense to have the data about individuals' qualifications in some CiviCRM component or extension (e.g. CiviHR)? Then I imagine this data would have to be moved from the custom field set to that component/extension.
  
  I know that in CiviCRM all data across components and extensions is integrated. But who knows, it may turn out that it is much more convenient to have the data about people's qualifications in CiviHR (or other) rather than have it as a custom field set. I don't know how things might develop in the future. And that's the point. I would much rather have the ability to juggle around the data and organize it the way I want to. This kind of flexibility seems important to me, especially since I don't think I'd get the data organizing right the first time.
  
  Or if there are some fundamental flaws in my thinking and my question doesn't make sense, please let me know. Thanks in advance!
  
  **Tags:** extension, custom-fields, import
  
  > **Comments:**
  > **username_1**: Hi. Not sure I follow the real outcome here. What do you see is the problem with just retaining the Custom Fields for Qualifications on the Individual? Why might it make more sense to then move it to CiviHR? And what other sort of 'CiviCRM component' might you be thinking about? Qualifications sounds like a very good use case for Custom Field  
  > **username_0**: @petednz I'm not seeking any outcome per se, I was just concerned about a possible future need to move data from one place to another. It could be that if qualification data is stored in CiviHR or CiviVolunteer, going to those extensions would provide a better overview than if that data is not stored in them. Andrew Hunt's answer gave me the reassurance I needed that in the future I can change any choices that I'm making right now.  
  > **username_1**: cool. i think the other question is 'where does civihr store its data'. would it be different to a civi custom field?  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_2 on 2015-08-09. Score: 3
  
  It's good to be thinking about where the data is stored, but don't let your thinking ahead get in the way of getting started somewhere.  It's not unusual for organizations using CiviCRM heavily to decide they need to remap data somehow, and the fact that the data is in CiviCRM somewhere already makes it one step easier.  The short answer is that you should put your data in the place that makes the most sense right now, and if you have to move it later, it'll take a little bit of work, but nothing overwhelming.
  Since you're worried about just some potential future place to store data, I'll have to be a bit abstract, but the principle should hold.
  There are two main tasks you'd face if you were to decide that your data in custom fields really belongs in different custom fields or an extension's ad-hoc tables.  The first is moving data, and the second is any transformation that may be necessary.
  Using SQL queries, moving data to and from custom fields is relatively easy.  You'll end up doing something like
  INSERT INTO civicrm_value_newtable
  SELECT NULL as id, entity_id, oldfield AS newfield
  FROM civicrm_value_oldtable
  
  to move the data from one custom field to another.  Of course, you could also export to CSV and then import from CSV, mapping the old field's column to the new field.  Since you have the contact ID, you know who a value belongs to.
  The more challenging aspect is if you need to transform the data.  Maybe you have three categories, and the new system takes four.  Maybe you have two fields where the new system has one, or vice-versa.
  Not knowing what situation you might encounter, the only thing you can do is to pick the most useful and accurate ways to store your data now.  The better your data now reflects reality, the more reliably you can transform it into something else.
  When it does come time to transform, you can do SQL functions; and export, alter within CSV, and import; or a series of SQL insert queries, such as
  INSERT INTO civicrm_value_newtable
  SELECT NULL AS id, entity_id, "option1" AS newfield
  FROM civicrm_value_oldtable
  WHERE oldfield = "A";
  
  INSERT INTO civicrm_value_newtable
  SELECT NULL AS id, entity_id, "option2" AS newfield
  FROM civicrm_value_oldtable
  WHERE oldfield = "B"
    OR oldfield = "C";
  
  and so on for each new possible value.
  In sum, don't let the perfect be the enemy of the good, and don't work so hard to prepare for vague possibilities that it's not functional now.  "If it's a life of possibilities that you wanna live...."
  
  
  ---
  
  ### Answer by username_3 on 2015-08-09. Score: 1
  
  It's often easiest for extensions to build on top of custom-data, so several CiviHR modules do that. In fact, CiviHR prompted development of a code-generator for custom-data. Take a look at [Create a Module Extension](http://wiki.civicrm.org/confluence/display/CRMDOC/Create+a+Module+Extension) and especially the section "Add Custom Fields".
  
  It is possible for an extension to create custom SQL tables. This is discussed a little bit in the section "Add a database upgrader / installer / uninstaller" (which produces skeletal code and a bunch of comments). You can work with the new tables using `CRM_Core_DAO::executeQuery()`. (Also: In a pinch, [`CRM_Utils_SQL_Select`](https://github.com/civicrm/civicrm-core/blob/master/CRM/Utils/SQL/Select.php) and [`_Insert`](https://github.com/civicrm/civicrm-core/blob/master/CRM/Utils/SQL/Insert.php) may help.)
  
  Some extensions go a step further and create DAO classes (in the same style as core code), although the technique is generally experimental/unsupported. You'd have to carefully trace some example modules (like CiviVolunteer and CiviHR's job module) and talk with their authors about how they work.
  
  
  ---
  
  