Civicrm not exporting all contribution records

[jgregory]

I'm trying to export contributions. When I run a contribution search, I can see 117 records on the screen (for this particular export). When I select all records for export and choose "export primary fields", I only get 93 records. Many of my contacts have made multiple contributions. But only one contribution per contact is actually exported. To rephrase and clarify... All contributions are shown on screen, only one contribution per contact is actually exported. Any ideas out there?

Cheers, John Gregory

[Dave Greenberg]

This is a bug that has been fixed in the current stable release (3.4.6 / 4.0.6).

[jgregory]

Thanks Dave.

[pkeogan]

Dave,

We have applied this patch to a 3.4.5 install, but now when exporting the contribution records we are getting more in the export than on the screen. What looks like is happening is when a person has multiple address entries, it is exporting one record for each thus duplicating the contribution.

I can not export from the sandbox, so I can not reproduce -- this is not true in Firefox, will retest.

My guess is that is not just all contacts with duplicate addresses, because it is happening very infrequently.  If I delete an address the duplicate export record goes away.  Have you seen this behavior?

Also -- this is only true if primary address field is part of the export

Thanks, Paul

[dwhitig]

Looks like this is NOT an issue on the demo site so it must be something in the 3.4.5 version.  Can you tell me if this was a known issue at some point and if it was fixed in a specific version.  Looks like it occurs only when the City field is added to the find contribution search export.

Thanks,
Donna

[Donald Lobo]

can you check your DB and make sure u dont have multiple primary addresses for the same contact (the UI tries to enforce this, but is not enforced at the DB layer)

lobo

[pkeogan]

We checked that, for the cases we checked there is only one primary address.

[daven]

We experienced the same thing while on 4.0.5. I upgraded to 4.0.8 and it worked when I export Primary fields and other tests but not with a specific field mapping that the client needs. I test on a search result set of 6 contributions and the export has only 1. I played around with the fields in the export for a while and it seems that this only happens when I try to export billing addresses. When I export primary address fields then I get all 6 no problem.

Any ideas why this is happening? I'm not familiar with the code for exports but i don't mind digging if someone can point me in the right direction.

Thanks,
Dave

[Donald Lobo]

can u enable mysql query logging and see what the query difference is between the primary and the export with billing address

that might give us a clue

lobo

[daven]

I've compared the queries from the two export variations. This difference seems the most significant to me:

Successful export: "SELECT contact_a.id as contact_id ..."
Unsuccessful export: "SELECT DISTINCT(contact_a.id) as contact_id ..."

Here's the full queries, successful version first:

Code: [Select] [nofollow]

SELECT contact_a.id as contact_id, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`, individual_prefix.value as individual_prefix_id, individual_prefix.label as individual_prefix, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.city as `city`, civicrm_address.postal_code as `postal_code`, civicrm_state_province.id as state_province_id, civicrm_state_province.abbreviation as `state_province`, civicrm_state_province.name as state_province_name, civicrm_country.id as country_id, civicrm_country.name as `country`, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_contribution.id as contribution_id, civicrm_contribution.receive_date as `receive_date`, civicrm_contribution.net_amount as `net_amount`, civicrm_contribution.contribution_recur_id as `contribution_recur_id`, civicrm_value_type_of_gift_1.id as civicrm_value_type_of_gift_1_id, civicrm_value_type_of_gift_1.would_you_like_your_donation_to__2 as custom_2, civicrm_value_type_of_gift_1.in_memory__in_honor_of_3 as custom_3, civicrm_value_type_of_gift_1.special_instructions_6 as custom_6, civicrm_value_type_of_gift_1.in_memory_or_honor_7 as custom_7  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id 
LEFT JOIN civicrm_value_type_of_gift_1 ON civicrm_value_type_of_gift_1.entity_id = civicrm_contribution.id  LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.name = 'individual_prefix') LEFT JOIN civicrm_option_value individual_prefix ON (contact_a.prefix_id = individual_prefix.value AND option_group_prefix.id = individual_prefix.option_group_id )  WHERE  civicrm_contribution.id IN ( 272,274,275,347,349,351 )   LIMIT 10000, 10000ELECT contact_a.id as contact_id, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`, individual_prefix.value as individual_prefix_id, individual_prefix.label as individual_prefix, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.city as `city`, civicrm_address.postal_code as `postal_code`, civicrm_state_province.id as state_province_id, civicrm_state_province.abbreviation as `state_province`, civicrm_state_province.name as state_province_name, civicrm_country.id as country_id, civicrm_country.name as `country`, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_contribution.id as contribution_id, civicrm_contribution.receive_date as `receive_date`, civicrm_contribution.net_amount as `net_amount`, civicrm_contribution.contribution_recur_id as `contribution_recur_id`, civicrm_value_type_of_gift_1.id as civicrm_value_type_of_gift_1_id, civicrm_value_type_of_gift_1.would_you_like_your_donation_to__2 as custom_2, civicrm_value_type_of_gift_1.in_memory__in_honor_of_3 as custom_3, civicrm_value_type_of_gift_1.special_instructions_6 as custom_6, civicrm_value_type_of_gift_1.in_memory_or_honor_7 as custom_7  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id 
LEFT JOIN civicrm_value_type_of_gift_1 ON civicrm_value_type_of_gift_1.entity_id = civicrm_contribution.id  LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.name = 'individual_prefix') LEFT JOIN civicrm_option_value individual_prefix ON (contact_a.prefix_id = individual_prefix.value AND option_group_prefix.id = individual_prefix.option_group_id )  WHERE  civicrm_contribution.id IN ( 272,274,275,347,349,351 )   LIMIT 10000, 10000

Code: [Select] [nofollow]

SELECT DISTINCT(contact_a.id) as contact_id, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`, individual_prefix.value as individual_prefix_id, individual_prefix.label as individual_prefix, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.city as `city`, civicrm_address.postal_code as `postal_code`, civicrm_state_province.id as state_province_id, civicrm_state_province.abbreviation as `state_province`, civicrm_state_province.name as state_province_name, civicrm_country.id as country_id, civicrm_country.name as `country`, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_contribution.id as contribution_id, civicrm_contribution.receive_date as `receive_date`, civicrm_contribution.net_amount as `net_amount`, civicrm_contribution.contribution_recur_id as `contribution_recur_id`, `Billing-location_type`.id as `Billing-location_type_id`, `Billing-location_type`.name as `Billing-location_type`, `Billing-address`.id as `Billing-address_id`, `Billing-address`.street_address as `Billing-street_address`, `Billing-address`.supplemental_address_1 as `Billing-supplemental_address_1`, `Billing-address`.supplemental_address_2 as `Billing-supplemental_address_2`, `Billing-address`.city as `Billing-city`, `Billing-state_province`.id as `Billing-state_province_id`, `Billing-state_province`.abbreviation as `Billing-state_province`, `Billing-address`.postal_code as `Billing-postal_code`, civicrm_value_type_of_gift_1.id as civicrm_value_type_of_gift_1_id, civicrm_value_type_of_gift_1.would_you_like_your_donation_to__2 as custom_2, civicrm_value_type_of_gift_1.in_memory__in_honor_of_3 as custom_3, civicrm_value_type_of_gift_1.special_instructions_6 as custom_6, civicrm_value_type_of_gift_1.in_memory_or_honor_7 as custom_7  FROM civicrm_contact contact_a
LEFT JOIN civicrm_address `Billing-address` ON (`Billing-address`.contact_id = contact_a.id AND `Billing-address`.location_type_id = 5)  LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )
LEFT JOIN civicrm_state_province `Billing-state_province` ON `Billing-state_province`.id = `Billing-address`.state_province_id  LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1) 
LEFT JOIN civicrm_location_type `Billing-location_type` ON (  ( `Billing-address`.location_type_id = `Billing-location_type`.id )  )  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id 
LEFT JOIN civicrm_value_type_of_gift_1 ON civicrm_value_type_of_gift_1.entity_id = civicrm_contribution.id  LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.name = 'individual_prefix') LEFT JOIN civicrm_option_value individual_prefix ON (contact_a.prefix_id = individual_prefix.value AND option_group_prefix.id = individual_prefix.option_group_id )  WHERE  civicrm_contribution.id IN ( 272,274,275,347,349,351 )   GROUP BY contact_a.id LIMIT 10000, 10000

I'm looking in the code for where this distinct is being called. Any help on this would be appreciated.

[Donald Lobo]

The code is in:

CRM/Contact/BAO/Query.php

we've been moving away from using DISTINCT and switching to group by (for performance reasons). i suspect we did not cover the below case.

Can you file an issue for this. We should fix this for 4.1 (backporting to 4.0 will probably be quite easy). If you can take a look that wuld be great. ping us on irc if u need help

lobo

[denverdataman]

Did an issue get filed with this? A client of mine can only export 20 contacts at a time from the export menu.

this is for  Joomla 1.5.25 and CiviCRM 3.4.8.

I am only loosly involved with this so just looking to give them direction.

Thanks,
Steve