Data inaccuracies in CiviMember export

[ctarascio]

Hi,
   We are running 2.1 alpha 3 and Drupal 6.4. We found a few issues with data being in fields where it shouldn't be, but the biggest issue for us is the fact that, for contacts that have multiple phone numbers (for example), multiple lines appear in the export file. We were expecting one line per contact, but I suspect that "misunderstanding" is because of our ignorance about the internal structure of CiviCRM. I've emailed Dave G. privately about the multiple line issue, so I'll just report the data inaccuracy issues in this post.

one thing though: having potentially many lines for each contact makes it difficult to test the validity of the export

The export spec included some Membership information, name, address and the following:
   Individual   Phone   Home    Phone
   Individual   Phone   Work    Phone
   Individual   Phone   Main     Mobile
   Individual   Phone   Main     Fax

Example #1
Constituent "A" had the following data:
     Primary location=Main
            Main Phone-Phone 111-111-1111
            Main Email xxxxx@xxx.xxx
      Adt’l location=Work
            Work Phone-Phone 222-222-2222
      Adt’l location=Home
            Home Phone-Phone 111-111-1111

     Data exported correctly except that "111-111-1111" appeared in the "Main Phone Mobile" and "Main Phone Fax" fields. This
     person had nothing in those fields so they should have been blank.

Example #2
Constituent "B" had the following data:
     Primary location=Main
            Main Phone-Mobile 333-333-3333
      Adt’l location=Work
            Work Phone-Phone 444-444-4444

   Data exported correctly except that "333-333-3333" appeared in the "Main Phone Fax" field in addition to appearing in the "Main
   Phone Mobile" field.

Example #3
Constituent "C" had the following data:
     Primary location=Home
            Home Phone-Phone 555-555-5555
            Home Phone-Mobile 666-666-6666
            Home Phone-Fax    777-777-7777

     Correctly, the Home Phone-phone number was exported to the Home-Phone Phone field, but the Phone-Mobile and Phone-Fax numbers appeared in that field as well, on separate lines: There were 9 lines for this contact: 3 contained the Home phone-phone in that field, 3 contained the home phone-mobile in that field and 3 contained the phone home fax in that field.

   I'm also wondering if some of these issues are due to our lack of knowledge about how the export is supposed to work. Perhaps if we setup the export specs differently the data would come out in the "right" fields.

   I'll do some more testing but thought I'd at least post what we've found so far.

Thank you 
Cynthia

[Kurund Jalmi]

this looks like a bug, filed http://issues.civicrm.org/jira/browse/CRM-3488.

thanx,

Kurund

[Kurund Jalmi]

Cynthia,

This fix should be part of next release. Let me know if attached patch works for you.

Kurund

[ctarascio]

Dear Kurund,
   We upgraded to 2.1 beta 3 and applied the patch you created. I have not had time to test as extensively as I need to, but I have been able to test the multiple lines per contact issue and that is still a problem. This is a BIG issue for us because if we can't get one line per contact, the export file is not really useful. Is this something that can be fixed?

Thanks,
Cynthia

I'll be able to test the data accuracay issue more carefully next week

[Kurund Jalmi]

Cynthia,

Can you try to replicate it on your http://sandbox.civicrm.org ?

Kurund

[ctarascio]

Kurund,
   We are able to replicate this on our internal sandbox site and we were able to replicate it on the demo site, but now when I try to replicate on the demo site I get this error message:

backTrace


/var/www/drupal-6.civicrm.org/public/sites/drupal.demo.civicrm.org/modules/civicrm/CRM/Core/Error.php, backtrace, 260
/var/www/drupal-6.civicrm.org/public/sites/drupal.demo.civicrm.org/modules/civicrm/CRM/Core/DAO.php, fatal, 708
/var/www/drupal-6.civicrm.org/public/sites/drupal.demo.civicrm.org/modules/civicrm/CRM/Core/BAO/Mapping.php, getFieldValue, 395
/var/www/drupal-6.civicrm.org/public/sites/drupal.demo.civicrm.org/modules/civicrm/CRM/Export/Form/Map.php, buildMappingForm, 91
/var/www/drupal-6.civicrm.org/public/sites/drupal.demo.civicrm.org/modules/civicrm/CRM/Core/Form.php, buildQuickForm, 320
/var/www/drupal-6.civicrm.org/public/sites/drupal.demo.civicrm.org/modules/civicrm/CRM/Core/QuickForm/Action/Display.php, buildForm, 98
/var/www/drupal-6.civicrm.org/public/sites/drupal.demo.civicrm.org/modules/civicrm/packages/HTML/QuickForm/Controller.php, perform, 203
/var/www/drupal-6.civicrm.org/public/sites/drupal.demo.civicrm.org/modules/civicrm/packages/HTML/QuickForm/Page.php, handle, 103
/var/www/drupal-6.civicrm.org/public/sites/drupal.demo.civicrm.org/modules/civicrm/CRM/Core/Controller.php, handle, 232
/var/www/drupal-6.civicrm.org/public/sites/drupal.demo.civicrm.org/modules/civicrm/CRM/Core/Invoke.php, run, 197
/var/www/drupal-6.civicrm.org/public/sites/drupal.demo.civicrm.org/modules/civicrm/drupal/civicrm.module, invoke, 315
, civicrm_invoke,
/var/www/drupal-6.civicrm.org/public/includes/menu.inc, call_user_func_array, 346
/var/www/drupal-6.civicrm.org/public/index.php, menu_execute_active_handler, 18

On the demo site, we were using the following record to test: Sarah Reiter.

I am going to ask Sarah to follow-up on this.

Thanks,
Cynthia

[Kurund Jalmi]

Cynthia,

Filed an issue http://issues.civicrm.org/jira/browse/CRM-3505 for this error.  Demo seems to be in some weird state, I am not getting any error on my local machine or http://sandbox.civicrm.org/civicrm/

Kurund

(will take some time to investigate CRM-3505, meanwhile if you want you can try it on sandbox)

[Sarah]

Hi, I tried this on the sandbox. The phone numbers seem to be working fine and does not duplicate records.
What is duplicating multiple records is the three listed emails for each location type.
I tried exporting with an email in each of the three email fields listed for location type main, and it generated a row and record for each email entry.
When I tried to move additional emails to another location type and export I receive this error message.

backTrace

/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/Error.php, backtrace, 138
, handle,
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/PEAR.php, call_user_func, 912
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB.php, PEAR_Error, 966
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/PEAR.php, DB_Error, 574
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB/common.php, raiseError, 1903
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB/mysql.php, raiseError, 898
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB/DataObject.php, query, 2394
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB/DataObject.php, _query, 1587
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/DAO.php, query, 144
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/DAO.php, query, 862
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Export/BAO/Export.php, executeQuery, 226
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Export/Form/Map.php, exportComponents, 211
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/Form.php, postProcess, 246
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/StateMachine.php, mainProcess, 166
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/QuickForm/Action/Next.php, perform, 63
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/HTML/QuickForm/Controller.php, perform, 203
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/HTML/QuickForm/Page.php, handle, 103
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/Controller.php, handle, 232
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/Invoke.php, run, 197
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/drupal/civicrm.module, invoke, 315
, civicrm_invoke,
/var/www/drupal-6.civicrm.org/public/includes/menu.inc, call_user_func_array, 346
/var/www/drupal-6.civicrm.org/public/index.php, menu_execute_active_handler, 18

backTrace

/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/Error.php, backtrace, 150
, handle,
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/PEAR.php, call_user_func, 912
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB.php, PEAR_Error, 966
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/PEAR.php, DB_Error, 574
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB/common.php, raiseError, 1903
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB/mysql.php, raiseError, 898
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB/DataObject.php, query, 2394
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/DB/DataObject.php, _query, 1587
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/DAO.php, query, 144
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/DAO.php, query, 862
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Export/BAO/Export.php, executeQuery, 226
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Export/Form/Map.php, exportComponents, 211
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/Form.php, postProcess, 246
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/StateMachine.php, mainProcess, 166
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/QuickForm/Action/Next.php, perform, 63
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/HTML/QuickForm/Controller.php, perform, 203
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/packages/HTML/QuickForm/Page.php, handle, 103
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/Controller.php, handle, 232
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/CRM/Core/Invoke.php, run, 197
/var/www/drupal-6.civicrm.org/public/sites/sandbox.civicrm.org/modules/civicrm/drupal/civicrm.module, invoke, 315
, civicrm_invoke,
/var/www/drupal-6.civicrm.org/public/includes/menu.inc, call_user_func_array, 346
/var/www/drupal-6.civicrm.org/public/index.php, menu_execute_active_handler, 18

[Kurund Jalmi]

Sarah:

I have reopened http://issues.civicrm.org/jira/browse/CRM-3488. Will get here once we fix the issue.

thanx,

Kurund

[ctarascio]

Hello Kurund,
   I just got the news flash about the release of beta 4. Can you verify whether or not the export issues have been fixed in that release? If not, we'll have to get them from the svn tree when you are finished. Please let me know.

Thanks for all your hard work,
Cynthia

[Donald Lobo]

cynthia:

most of the changes made it, except for one issue: http://issues.civicrm.org/jira/browse/CRM-3488

i have a patch for it and when kurund comes back we'll test and ensure it works  and let you know

lobo

[Kurund Jalmi]

Cynthia:

We have fixed CRM-3488. Can you apply following patch:

http://fisheye.civicrm.org/changelog/CiviCRM-2-1/?cs=17004
http://fisheye.civicrm.org/changelog/CiviCRM-2-1/?cs=17005

HTH

Kurund