Logic to omit duplicate records

[John_P]

I have a relational FMP database that creates monthly and YTD reports. In the YTD reporting I need to omit records that have duplicate values for one particular field and only count the most recent record with that field. I created a counter and used the duplicate function in FMP, but that works the exact opposite of me needs...meaning it calls every record after the first iteration a duplicate. I need a process that will call the prior records duplicates and identify the last record as unique. Any help is greatly appreciated. Thanks

[HazMatt]

If you are getting a found list of exactly what you DON'T want, just throw a Show Omitted script in there.

[CobaltSky]

If you are getting a found list of exactly what you DON'T want, just throw a Show Omitted script in there.

If the duplicates have just been found, clearly a 'Sho Omitted' step will find all the records that aren't duplicated (along with any members of the duplicate groups that have been omitted), so that's not likely to be of any use.

Instead I suggest that you pay a visit to the second on 'Finding only one of each record when there are duplicates!' on my web site and download the (free) demo file you'll find there. The link location is:

http://www.nightwing.com.au/FileMaker/demos5.html#d26

The script you'll find in that file is constructed to find the first record with a value in the target field, however as part of the process it sorts the database by the target field. If you add a secondary sort key with an descending order sort date and a third field with a desccening order sort on time (if required), then the version of each record (where there are duplicates) which will be found will be the most recent.