AnsweredAssumed Answered

Getting Duplicate Records

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


Getting Duplicate Records


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.