Export Custom Fields with Multiple Records?

[aaronchow]

Dear All,
I'm assigned to help out a sister organization which is currently using CiviCRM.  This sister organization hires a web designer to re-design their website using Drupal + CiviCRM.  The Drupal was set up quite well, but there is some implementation problem (or he didn't plan it carefully enough) with CiviCRM, and the admins feel kind of screwed because they have entered all of their data into CiviCRM.

One of the more pressing issue relates to a custom fieldsets.  The sister organization wants to display a list of treatment programs that other organizations provide, so the web designer created a custom fieldset that allows multiple records (stored under Organization as a tab).  The problem with his approach is that this custom fieldset cannot be used in Profile, and they cannot import or export the data (he totally ignore the warning message when he created it!).  Anyway, I assume he knew about it because he used Views to create this Treatment Program listing and he used Profile to create the other directory.

But, now, the staffs need to export all of the Treatment Program data out for whatever reason, but they couldn't find those fields in the export screen, so they are trying to ask for my help when the original web designer ignored their calls.  I explained why this is happening (along with the other smaller issues), but I can't find a way to fix this problem. 

This Treatment Program fieldset has 26 custom fields (mostly textfields, couple multiple choices, and one of them is a contact reference field).  I saw that they have entered around 500 organizations, and each organization hosts an average of 5 treatment programs.  I tried to export the information they need using Views, but there are some formatting issues, so it is very time-consuming to individually fix and proof-read all of the records.  So I wonder if there is another cleaner way to pull out all of the records directly from CiviCRM???

After exporting the data, they plan to allow each organization to update their treatment program information, so I have to re-do their structure one way or another, but, now, I must help them with the exporting issue.  Anyway, what will you do if you are in my shoe?  Any advice is appreciated!

[Coleman Watts]

If I were in your shoes I would hire a knowledgeable Civi consultant who can help you out of your jam.
If set up properly, Civi can be a great asset to your organization. If not, then it will be a great pain.
One of the first questions they will no doubt ask will be why do the staff need to export Civi data to excel? In my experience, this is a sign of  a staff who do not understand how to use their CRM.

[aaronchow]

If I were in your shoes I would hire a knowledgeable Civi consultant who can help you out of your jam.
If set up properly, Civi can be a great asset to your organization. If not, then it will be a great pain.
One of the first questions they will no doubt ask will be why do the staff need to export Civi data to excel? In my experience, this is a sign of  a staff who do not understand how to use their CRM.

Hi Coleman,
Thanks for the suggestions.  Please let me re-introduce my situation as it was very brief earlier.

Background
I set up CiviCRM for the organization that I work at.  My organization likes it so much that the CEO has recommended it to others (Thanks CiviCRM Community!), and one of the "sister" organizations heard and decided to adopt it.  They hired a freelancer for this job because he claimed he has extensive knowledge with both Drupal and CiviCRM, and one of his profolio showed a site which has CiviCRM integration.  As a result, the sister organization has listed out all of their needs in the contract, and the guy has "delivered" all of them, so all of the payments were paid.

However, I received some phone calls asking for help yesterday.  They explained that their organization offers a referral service, so they have an online Treatment Program list, but they also has a "printed" version of the same list that they distribute yearly.  They said that they entered all of the information into the database, and the data were showing up correctly at the online listing.  In addition, the Treatment Program information shows up when they perform a search, but when they tried to "export" them, they can't find any fields that are related to them.  When they tried to contact the web designer, the web designer asked for additional funds because this was not part of the contract, as the contract clearly states that he is only responsible for performing occasional updates.

Where I come in
So my CEO has asked me to "take a look."  After I briefly checked their website, I find the web designer has done a great job using Drupal to write the frontend, but he clearly showed a lack of knowledge with CiviCRM.  For example, the website has three directories at the frontend: Two simple membership directories and the Treatment Programs directory.  He used CiviCRM's Profile to pull two of them, and he used Drupal's View to pull the last one.  Before I investigate further, I saw that he modified the look and feel of the two membership directories, but I noticed that he directly modify the system template files instead of working on the copies.  Then when I saw the way how the Treatment Programs custom fields were created, I know this "take a look" thingy will be more than a simple fix.

The sister organization were in a pissed mood when I explained my findings to them, but my CEO is even worst when I explained to him.  He told me that he has personally recommend CiviCRM to them, and they worked really hard on the budget to make this happen, so my CEO has assigned me to "fix" the mess.  However, since I'm not paid to do this "project," I only plan to help deliver their "immediate need" for as much as possible.

One "immediate need" is to export the Treatment Programs data into an Excel (or .CSV) format.  They have a freelance desktop publisher who will work on the layout of their printed directory, but the desktop publisher wants all of the information stored in a file so that she may import them easily.

Problems I'm trying to solve now
I created a Drupal View to pull out all of the Treatment Programs information, and I used Drupal Export to export them into an Excel format.  I had some formatting problem earlier was able to find some quick workarounds, but I just ran into another problem.  Each Treatment Program has a "contact person."  The web designer created a "contact reference field" for the staff to enter this information.  However, I'm unable to use this "contact reference field" as a relationship in Views, so I'm having trouble pulling out the contact information from this contact id.  Any suggestions?


- Aaron

[Coleman Watts]

So I'm unclear about one thing: you have an online directory that is displaying correctly (as a profile?). You want to print that directory, but you want to use Views for that task instead of a profile? If the profile works fine, why switch to views?

[aaronchow]

So I'm unclear about one thing: you have an online directory that is displaying correctly (as a profile?). You want to print that directory, but you want to use Views for that task instead of a profile? If the profile works fine, why switch to views?

Hi Coleman,

The problem is that I cannot use CiviCRM's Profile to pull the information out.  When the original web designer created the field-set, he set it to "accept multiple records."  With this option selected, all of the fields in this field-set cannot be referenced by Profile, and we cannot import or export them as well.  And since all of the information is entered, I cannot go back and undo it.

I'm actually done with this bullshit "project," and I find out more about the web designer while I was working on it.  In addition, I've suggested the sister organization to post the job request in this forum if they decide to hire a CiviCRM consultant, and I'm writing down my findings if anyone is interested to read it.

How it looks
A Treatment Programs directory is displayed online, and it is grouped by the organization providers.   When one clicks on a specific record, it takes them to a "Drupal" profile page with more information.  This Drupal profile page lists its main content in the Content area, and it also has the Contact Person's contact information displays as a block in the sidebar.

How it works behind the scene
1. He set up the Treatment Program as a subtype of an Organization
2. He set up some custom fields (which accepts multiple values) to stored the info, and it is displayed as a "Tab" underneath this subtype.
3. He asked the staffs to enter all of the information.
4. He created this "directory" using Views.
    (I have no idea how he was able to link the treatment program result into their associated drupal profiles, but my friend said something about the hooks_view_alter)
5. He duplicated the Treatment Programs fields and re-created them under Drupal Profile, and copy all of the content there
6. He added an additional "CID" field under Drupal profile
7. He created another view to pull out the CID value from the contact reference field.
    (again, no idea how he was able to do it)
8. He stored the CID value into the Drupal's CID Profile field
6. He created the Contact Person block based on the value of this CID entity, but he is pulling the contact information directly from CiviCRM

Why did I take so long to do it?
When I export the value from the Contact Reference field, it only gives me the "display name."   But I need the cid value because I want to set it as a contextual filter, so I may pull out its related information.  Eventually I ended up exporting all of the individual data and have Excel sort it out for me.

My biggest pissed point is that the guy created two sets of Treatment Programs information.  He recommended the staffs to update the Treatment Programs information via the Drupal Profile, but he also said that the information will be synchronized into their CiviCRM records, so some of the staffs did not follow this direction.  Additionally he used Long Text fields to store everything, so I ended up spending hours trying to split up the data into their proper columns.

[Dave Greenberg]

For reference of folks reading this thread, as of 4.3 CiviCRM Profiles have support for editing custom fields in 'multiple record' custom data sets. Might help folks w/ similar use cases in the future.

[petednz]

dgg - did you mean 'exporting'?

"support for editing custom fields in 'multiple record' custom data sets"

[Dave Greenberg]

No - sorry. Support was added for editing w/in profiles, not for exporting.

[aaronchow]

No - sorry. Support was added for editing w/in profiles, not for exporting.

Thanks for adding this support. We can always use other means to export the data out, but allowing us to edit the data with profile is a great help. Thanks again.