"When I perform a duplicate find on the "C_Full_Name" field, I get duplicates. For example, it tells me there is a duplicate for the name "John Daly" (see attached).
However, if I then search on the "C_Full_Name" field for "John Daly", it only finds one record, not the two I expected."
I suggest describing how you did those two findsstep by step. Your screenshot clearly shows two records with the same name. So it is not clear why you would need to perform a second find.
A little more info on this. So FMP is telling me I have a duplicate record. When I go to the source software to search for the duplicate record, I do not find it.
So I was playing around in FMP to see if the problem might be there. That is why I was testing the duplicate by doing a find on the "C_Full_Name" field, just to make sure the duplicate record was there. That is when I found the inconsistency.
Again, when I do a find using the duplicate feature ("!") on the "C_Full_Name", it says it finds a duplicate for "John Daly" (and displays two records). But when I actually do a find in that same field, entering in the "John Daly" text, I expected to find two records, but it only finds one.
Doesn't that seem strange?
One more point - I originally did not h ave a Serial Number field on the table that I was importing into, but decided to create one just so I could be sure there were actually two different records when I did the duplicate search. However, I can not seem to get the new SN field to populate. I am running the import script from the server side (see original attachment for the import script) but can not see how to tell it to fill in the SN field as it imports the records.
But when I actually do a find in that same field, entering in the "John Daly" text, I expected to find two records, but it only finds one.
so this is a find on the field in the original table?
I'm not sure about ODBC importing, but regular imports pop up a little dialog asking if you want to enable auto-enter options. If that is also the case for importing from an ODBC source, that option should allow an auto-entered serial number to populate as part of the import.
And as I recall, an ODBC import can specify an SQL query in order to control what data is imported. If so, I wonder if there is some issue in the SQL that is generating the duplicate record--such as a join that matches to the same record with that name for two different records on the other side of the join.
And this is pretty obvious, but presumably you don't have this happening because "John Daly" was already in a record and then you do an import that adds it in again?
Is it possible you are searching for a field in a related table to the layout you are in? That will cause this to happen sometimes. It is possible, but unlikely, that your index has become corrupted. You can unindex it, then turn indexing back on to see if that works. Or you can try to recover the database which will rebuild all indexes.
EDIT: My bad.... I didn't realize the datasource was external. What happens if you do a SQL search for it via ExecuteSQL? Can you import using DISTINCT? Or import using that as a matching field to eliminate duplicates?
OK, I finally figured it out. It was an issue of bad indexes (I think). The clue was that I was able to run the import script locally and use the option to create serial numbers. When I then did a find on duplicates, I noticed the records it was finding did not have any serial numbers. However, the total count of the table did not change, so it was almost as if there were "ghost" records still there. To confirm the issue, I deleted one of the found records that had no SN and then ran the find duplicate process again and that record did not show up as a duplicate.
I then went into the table and turned off all of the indexes for each field, deleted all the records, then imported new data in and it seemed to fix it.
Thanks for all your help!
Glad you figured it out. It is pretty rare that indexes go bad, but I've seen it happen. It seems to happen more often on non-Server hosted files.