finding one record from a set of duplicates

[nena]

Hallo,

Here is the case.

From the point of view of one field in my database most of the records are duplicates. They are grouped in sets of records with the same value in this field.

I need to filter the records and display only one record (regardless which) having this value, but not to delete the rest. A script is to be used to automate the task.

Would you help me with this?

[RodSierra]

One way in a script:

Find duplicates using the !

Show all records

Omit record

show omitted only

So we found the dupes, and you are on the first found, then show all to clear omitted, then omitted the current, then show only that record.

[nena]

Thanks for the quick response, RodKp

Forgive me for not being specific enough.

I need not just one record representing all the duplicates, but one from each set of duplicate records.

[RodSierra]

Then you probably need to do something like this:

Create a field called marker

Find all duplicates

Sort by the field that is duplicated

Set a Variable or global field to the current name of the field

Loop thru the records and check the variable or global for a match to the field, if no match set marker to one and set the global to the new name and continue to loop thru the duplicates. Hope this makes sense. I'm about to call it a day or I'd put in script form for you.

[mr_vodka]

See here for sample script

You are using FM7 though and variables do not exist so you would have to create a Global field and use that rather than what I used as my variable ($DupCheck).

[nena]

Thank you both RodKp and mr_vodka

As a newbie I'm supposed to read the FM Help more often. So I did it.

I solved my problem using the example in "Identifying duplicates using self joined table" to mark the first record in each duplicate set as Unique. In addition a find for the Unique records is performed. It works now.

I thank you for your effort. A community to rely on - it's a great feeling.

nena

[stolen_gtr]

hi nena,

i was wondering if you could explain a little more clearly what exactly you did to mark the first record in the set of duplicates. i know how to search for unique records but am unsure about how to mark them as unique initially.

thanks

Lucas