Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 4643 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

I'm needing to clear out redundant records in a table where there is, say a Foreign Key field linked to 'People' and a Content field like, oh, "Activity" (Golf, Chess, etc.) The gist is the table 'tags' people with various activities, but there should be no duplicate tags -- we only need to know that Janice plays Chess *once* -- not several times.

This seems like it should be simple, but I'm not immediately seeing it -- and I'm wondering whether there's

(a) A direct way to find all such records via a Find technique that would give me a set of all records where the FK and Activity are both matching any other record. (This need just comes up in various scenarios.

(B) what's the most efficient way to find and delete these dupes, so that each record has a unique combination of FK/Activity. I'm thinking a self-join based on 2 relationships with the two fields matching, a Summary field to count record, and a looping script to work through all records and knock down the related Count to 1? Is that the right approach?

Posted

First, backup your database.

To prevent duplicates in the future, create a text field (I'll call it "Unique" but the name does not matter) with an auto-enter calculation:

foreignkeyfield & " " & activity

Set this field to validate unique, always, and not allow user over-ride.

Now, export the data. Delete all the records. Import the data back in again with all the auto-enter options enabled. The validation will prevent duplicates from being imported.

Posted

First, backup your database.

To prevent duplicates in the future, create a text field (I'll call it "Unique" but the name does not matter) with an auto-enter calculation:

foreignkeyfield & " " & activity

Set this field to validate unique, always, and not allow user over-ride.

Now, export the data. Delete all the records. Import the data back in again with all the auto-enter options enabled. The validation will prevent duplicates from being imported.

slick Vaughan..

Kewl technique

Posted

Ahhhhh --- nice! THis is useful in a lot of situations, because I had always been stymied in cases where I wanted to require a calc field to be Unique. Never thought of the auto-entry equivalent! Thanks a million.

Posted

I think Comment mentioned this technique w-a-y back, so I'll pass the compliments onto him (but keep 10% commission). :D

This topic is 4643 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.