Finding Duplicates

[bgscott]

I have a table with 200,000 plus records.  In that table, 1967 records have a BidID of 2018-XXX.  Each of those records has an ItemID.  Those records are imported from another table.  I want to check for duplicates so as to verify the user did not perform the import twice.  ItemID is unique.

Here is how I am searching for duplicates

First search for duplicate ItemID by placing ! in itemID field then perform find

Next enter find mode again and place 2018-XXX in bidID field then select constrain found set in Requests menu.

This does not find duplicated contained in 2018-XXX.  It does find a subset of only 1248 records however.

If I switch the order, first find BidID then constrain to duplicates, no luck, still finds 1248 records

I have tried many other ways to find duplicates within bidID 2018-XXX with no luck.

 

[comment]

1 hour ago, bgscott said:

First search for duplicate ItemID by placing ! in itemID field then perform find

Next enter find mode again and place 2018-XXX in bidID field then select constrain found set in Requests menu.

This does not find duplicated contained in 2018-XXX.

I believe it does. However, you should be aware that searching for duplicates (using the ! operator) finds records that have a duplicate value in another record in the entire table - not necessarily in the current found set. So you may get more records than you expect, not less. Why do you expect more?

 

Edited May 15, 2021 by comment

[comment]

1 hour ago, bgscott said:

I want to check for duplicates so as to verify the user did not perform the import twice.

You can prevent user importing the same record twice by validating a field as Unique, Validate always.

 

[LaRetta]

1 hour ago, bgscott said:

First search for duplicate ItemID by placing ! in itemID field then perform find

So the itemID itself is not unique in that table because it could have bidIDs from other periods of time against the same itemID, right?

It is the combination of itemID and bidID which must be validated as unique.  If I understand you correctly, add a new text field with an Auto-Enter ( Replace Existing Values ) and set THAT field as validation unique, validate always (see the validation on uniqueCombinationKey field (attached).  This, as Comment describes, will stop duplicates from being imported.

IgnoreDuplicates.fmp12

Edited May 15, 2021 by LaRetta
... technique learned from Comment anyway. :-)

[comment]

19 minutes ago, LaRetta said:

So the itemID itself is not unique in that table because it could have bidIDs from other periods of time against the same itemID, right?

That's a good question. I think the original post could be read either way.

 

[bgscott]

Correct, the item ID is not unique in that table.  Same Item ID could be in the table but with a different Bid ID.