AnsweredAssumed Answered

Getting Duplicate Records

Question asked by DrDave on Dec 7, 2009
Latest reply on Dec 7, 2009 by DrDave

Title

Getting Duplicate Records

Post

I have a database (Customer) that is linked to another database Countries, using Customer:AddressCountry. Imagine 500 customers, living in 40 Countries.

 

I'm writing a script that populates the Countries database and at the same time, tallies the number of customers per country, using the field Countries::CountryCount.

 

I have other fields in the Countries database that are used for other purposes, but they are not relevant here.

 

I begin by sorting the customer database by Country (no reason for this, except that it shows my problem more clearly)

 

As I loop through the Customer database, I check to see if we have a Countries record for that customer's country. If not, I add a record to the Countries database. I then add 1 to the CountryCount. If the Countries record already exists, I simply add 1 to the CountriesCount.

 

As the end of the process I have an extra set of Countries! :-) More specifically, if I have 60 customers from the USA, I have a USA record with a count of 59 and another USA record with a count of 1. 

 

I'm guessing that this has something to do with the fact that I'm replacing a field (Countries::fkCountry) that is used to link the Countries database to the Customer database.

 

I have tried flushing the cache when I create a new Countries records, but that does not help.

 

Any and all ideas are appreciated. 

Outcomes