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.