omitting all but one duplicate record

[1presence]

Hi !

I'm using found set to find records after todays date and then I want to remove the duplicate records of the namecode (unique value in the related table but not in the main search table) with either omit !(duplicate) records or a similar request with using constrained set.

Both times it removes ALL of the duplicates without even leaving one instance.

There must be an easy way to do this without creating calculations or complicated combinations of relationships... nO?

[color:blue]more detail:

2 tables...list of events with dates including the name code date, time, place, namecode etc.

other table.. is a profile of the namecode of a band (or client)..

so for my report I need to filter out one instance of every event after a certain date for my security badges. (one badge per band even if they show up for mutiple events)

my query is like:

(find)date=>//

(omit)namecode!

but this doesn't work because it doesn't remove just the extra listings of namecode but it removes all of any listings which occur in the first query more than once.

I hope this detail helps.

Thanks,

Michele

stumped and curious :qwery:

Michele[color:red]

Edited October 29, 2009 by Guest
I don't think I gave enough information

[Makah]

You could create a script that loops through all the duplicate records. It would do a check if the namecode value is the same as the record before it. If it is, you omit it. You will end up with only the first instance of namecode in your foundset. In order for this to work, you need to sort your records by name code.

Here is the sample code. It assumes you are in the correct foundset and you have sorted by the keyword you are omitting (namecode).

===============================================

Go to Record/Request/Page [First]

Set Variable [$i; Value:1]

Loop

If[nameCode = GetNthRecord( nameCode; $i-1)]

Omit Record

End If

Set Variable[$i; Value:$i+1]

Go to Record/Request/Page[Next;Exit after last]

End Loop

==============================================

Let me know if that makes sense!

Makah Leal

Soliant Consulting

http://www.soliantconsulting.com/apps/filemaker/