CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Search listing results with duplicated records
Pages: [1]

Author Topic: Search listing results with duplicated records  (Read 2180 times)

Gabrielh

  • Guest
Search listing results with duplicated records
September 05, 2007, 12:11:39 pm
Hi,
For some reason I couldn't identify so far, in almost every seach profile I created the listing result comes with many lines duplicated, but these duplicated lines in the list are linked to the same contact record in crm database, therefore there's no duplicate contact records at all. How could I avoid the search results showing these duplicates lines?
I'm using CiviCRM 1.8 with Drupal 5.2, PHP 4.4.7, MySQL 4.1.22

Any idea?

Thank for help.

Kurund Jalmi

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4169
  • Karma: 128
    • CiviCRM
  • CiviCRM version: 4.x, future
  • CMS version: Drupal 7, Joomla 3.x
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Search listing results with duplicated records
September 05, 2007, 01:19:25 pm
Can you give more information how to replicate this. Also can you try to replicate this on http://demo.civicrm.org

kurund
Found this reply helpful? Support CiviCRM

Gabrielh

  • Guest
Re: Search listing results with duplicated records
September 05, 2007, 02:52:49 pm
Hi Kurund,
This is what I did: I created a profile for search individuals. The profile queries a specific group (called Group B, option "Limit listings to a specific Group" set). I created two ACL entries in order to control who and how access this profile: one ACL allowing a group (called Group A) "to View" through this profile, and other ACL allowing Group A "to View" data from the Group B.

The result when search and listing is always the same:
Alberto, John          View
 Alberto, John          View
 Almeida, Luke         View
 Almeida, Luke         View
 Walker, Rachel        View
 Walker, Rachel        View
....
Every duplicated line refers to the same individual record in database. >:(

I'll try to reproduce it in demo site.

Thanks for any insight.

davej

  • Ask me questions
  • ****
  • Posts: 404
  • Karma: 21
Re: Search listing results with duplicated records
October 04, 2007, 09:08:50 am
I have a similar problem: duplicates in profile listings, each with the same ID. I've just been trying to track down the issue by turning on MySQL logging. The attached file query.sql is the SQL that was being run. Our db has loads of custom fields so I stripped these out of the query to generate a simpler test case, attached as query2.sql .

Setup
------
CiviCRM 1.8.11248 (also previous versions)
Drupal 5.2 (also 4.7.4)
MySQL 4.1.21 (also 4.1.20)
PHP 5.1.4 (also 5.0.4)

Findings
---------
Both versions of the query produce duplicates when run in mysql on our db.
The duplicates seem to occur where there are multiple instances per contact of Main-phone-Mobile.id or Main-phone-Fax.id .
The multiple phone entries appear to be erroneous, e.g. 2 records for the same location_id with the same phone_type and same number, or different numbers where one has the wrong phone_type. Our data was imported from various sources into CiviCRM 1.4 and has subsequently been upgraded to 1.5, 1.6, 1.7 and 1.8, along the way being edited both via the CiviCRM interface and profiles.

Example
---------
A sample run of query2.sql is attached as query2-result.txt .

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Search listing results with duplicated records
October 04, 2007, 01:23:53 pm

I suspect cleaning up the location data is probably your best bet and ensure that you dont have multiple entities of the same type. Also check the is_primary flags. In recent versions we've put in FK constraints to prevent some of this from happening

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

davej

  • Ask me questions
  • ****
  • Posts: 404
  • Karma: 21
Re: Search listing results with duplicated records
October 05, 2007, 08:13:54 am
Thanks for the reply, Lobo. Looking at the set of problem cases more closely, most appear legitimate: there is simply more than one phone no. for a given location, e.g. freephone + geographic numbers, which is permitted by the CiviCRM UI. In all cases where there is > 1 civicrm_phone record for the same location_id + phone_type, just one of the records has is_primary = 1.

I have now replicated the issue with a very simple test case on demo.civicrm.org and created an issue in JIRA: CRM-2328.

Dave J

twowheeler

  • I post occasionally
  • **
  • Posts: 115
  • Karma: 11
    • Harrisburg Christian Performing Arts Center
  • CiviCRM version: 4.3.4
  • CMS version: Drupal 7.22
  • MySQL version: 5.1
  • PHP version: 5.3
Re: Search listing results with duplicated records
October 13, 2007, 07:45:57 pm
Just wanted to jump in here to say: me too.   Doing a search builder on a group and a custom data field causes these duplicates to be displayed whenever two phone numbers are stored in one location, even if one is for home and one is a mobile.  Thanks for tracking this down. 
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • Search listing results with duplicated records

This forum was archived on 2017-11-26.