AnsweredAssumed Answered

Retrieving a list of duplicate values via a relationship

Question asked by calexmac on May 30, 2015
Latest reply on Jun 3, 2015 by philmodjunk


Retrieving a list of duplicate values via a relationship


I am trying to work out how to retrieve a list of duplicate values in the fastest possible way from a big table and I would like the duplicated values in a value list.


The table in question has a lot of rows (~25 million).  The table has a primary key field - PKey -  and a field - X - that can legitimately hold duplicate values.


As a result of prior steps, I have a value list of primary keys, and I would like to get all values of X from the records that have a primary key represented in this list.  This operation gets run many times for different primary key value lists, so speed is paramount.


Using the primary key value list as a multi-key into the big table works well.  Its fast.

I have also tested FMPs duplicate query operator - ! - and that works well - its also fast, even when operating on millions of records.

But this is where I run aground…. I want the results in a list (not just a found set).  So I could do that from either the match TO side of the relationship or from inside the big table itself.


Both have a problem.  Using the relationship, I don’t know how to configure the relationship (based on the primary key value list) to find only the duplicated X values (if that were possible, it would be easy to get the list - which is a very fast operation).  And from the context of the table itself, the ! operator will find all the duplicates, but then getting them into a list - and quickly - is a problem.


Any suggestions?