I work for raSANTIAGO, a consultancy in Portland, Oregon; we do a lot of work with CiviCRM. We've been working with the good folks at Portland Community Media to convert their membership data from Facil to CiviCRM.
I've put together an annotated set of MySQL queries that I'm using to import PCM's data; we're also releasing it "into the wild" for anyone to adapt and use for their own similar conversion efforts. We've put all of our code into a public Git repository on GitHub at http://github.com/rasantiago/Portland-Community-Media/ -- feel free to clone it for your own use, browse it for clues, or whatever.
This is just a brief post to point you at a possible resource; we'll follow up with more detail (especially regarding the steps that need to be performed by hand) soon.
Cheers,
-Sam
[New to this site; please correct me if I step on any netiquette landmines!]
Comments
You guys ROCK, but one
You guys ROCK, but one question... did you import the Access tables into MySQL and then run these queries? I don't know why, but I thought you were planning on connecting to the MySQL through access.
Access to MySQL
For ease we used a tool to copy data from Access to MySQL. It just makes it much easier to work in one environment. Specifically, we used an inexpensive tool from DBConvert. There are other ways of bringing in the data but given that we charge hourly for services it made more sense to buy a $50 tool and get it done with less than an hour of effort rather than do it by hand. On that note, if anyone does know a good open source method to go from Access to MySQL please let us know. Would prefer that. Thanks
Did you use this DBConvert tool?
Did you use this DBConvert tool?
DBConvert for Access & MySQL
Thanks,
- Stefan
Yes
Yes
Thank you
Great work on this. Many PEG centers are tied to Facil. This sort of solution may be a tipping point for some Access stations looking towards adopting pieces of the Open Media system.
Stragegies for Converting Facil Persons to Drupal Users
The scripts raSANTIAGO created for PCM handled the CiviCRM side of moving from Facil to a Drupal/CiviCRM solution. MERCI has its own import, but Users and Projects still need to be migrated into Drupal.
One challenge to getting data into Drupal is the records in Facil's tlbPerson table do not include true usernames, passwords, or (often) email addresses. We've handled this a few different ways. At channelAustin, the usernames were set to FirstnameLastname and the email set to uid@dominizer.org for users without an email. At BNN we generated account names with the first letter of the first name and the last name. Both options resulted in several duplicate usernames that will eventually need to be manually reconciled at both Austin and Boston. Some of these are users with similar names, but others are actually the same user. Facil doesn't force person records to have anything unique other than the id. In the Facil databases we've worked with so far, we've seen the same Person entered multiple times as well as organizations entered as people.
For users with an email address in Facil, distributing a password Drupal is easy. Simply set a password to anything and allow the user to reset it using Drupal's "forgot my password" functionality. For users without emails this is more difficult. Our plan has been to catch attempts to reset a password by users with an @dominizer.org email and display a message to call the station instead of trying to send an email, but that code has yet to be written.
We realize that many users in tblPersons table at stations that have been using Facil for years will never log into Drupal, but we still need those records to display the owner of any imported Projects and Shows. In both Austin and Boston we were able to import thousands of existing Projects.
New Drupal CiviCRM Account Creation Module
Good Point!
We will be releasing a new module which handles the problem of contact records that do not have email addresses. The new module will allow users to come to drupal, setup an account and based on their street address identify an existing CiviCRM contact record that matches them. Specifically, the workflow would work in the following way:
User clicks on Create Account, enters details
Module checks for an existing first name / last name pair
If it doesn't find one, it creates a new Drupal user and a new
Civi contact and links the two
If it finds a match, it presents a list of first/last names and
street addresses and asks the user if he or she is any of the listed
people
If the user selects a contact, the module writes the email address
into the existing contact record, creates a Drupal account with that
email address, and joins the two. (If the contact was already
associated with an existing Drupal account, a new account and a new
contact are created, rather than linking the two.)
If the user selects 'none of the above,' registration creates a
new Civi contact with the user's details, a new Drupal account, and
joins the two
It is not perfect but we think it should handle 90% of the cases. We are hoping to release this week.
When released?
Do you know when you'll be releasing this module?
I just received this module
I just received this module today and am testing it out. It looks good, only a couple minor things I have noticed that need to be changed. I'd bet this will be released next week sometime.
I know this is an old thread,
I know this is an old thread, but I'm interested in the module to migrate the data to Drupal. Is this available anywhere?
There has never been a
There has never been a module. The process rasantiago used is manual database migration. Stephan Wray posted a screencast of how channelAustin migrated the Facil data into MERCI, but beyond that you are going to need to work through the database migration or use CiviCRM's import to move from Facil to CiviCRM.
I have the scripts for
I have the scripts for CiviCRM, but my client is not using CiviCRM... just Drupal with MERCI. In any case, the screencast seems to be exactly what I wanted, thanks!