Is it possible to move data from a contact type to a component/extension?

Question

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!

Answer 1

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 2

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 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 and _Insert 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.