Thank you for your post.
I recommend that you enter this suggestion into our Feature Requests web form at:
The entries into this web form populate a database that is hosted and monitored by Development and Product Management. Each entry is discussed and considered for possible implementation in a future release. Although I could copy your post and paste it into the web form, there are a couple of questions asked that only you can answer.
Thank you, will do!
It's possible that FileMaker envisioned this to work this way:
"A duplicate search is ALWAYS based on whether the record has a match anywhere in the database, regardless of how the found set has been limited."
However, even if FileMaker envisioned the behavior to work in this fashion, there is yet another bug when doing a Constrain Found Set on duplicates (the exclamation point), which is that it will also return results that have no duplicates at all in the entire table. So it doesn't actually omit the unique records out of the found set at all.
I can think of a work around that eliminates the looping.
Perform your find or other action to get your original found set.
Use the new List of summary field to put a list of all the found set's primary keys into a global field.
Define a self join that matches by the global field of primary keys (to limit the match to the current found set) and also by the field that contains possible dupcliate values.
A calculation that uses the count function to count the related records from that relationship will return a value greater than one of there are duplicate values within the found set.
Thanks so much for the idea! I don't think that idea will work, though, because the relationship will match ALL the records in the entire table, not just the found set.
This is the method that I ended up using:I sorted my found set by the field which I'm looking for duplicate values in, then looped through the found set from top to bottom... using GetNthRecord() to test the current record's value against the previous record and also against the next record's value. If both tests are negative, I omit that record. Otherwise, I go to the next record.Thanks,Scott
because the relationship will match ALL the records in the entire table, not just the found set.
Not when you match records using the list of IDs from the current found set AND the field that may contain duplicate values. The first part limits the matching to only records from the current found set. It's really only practical in FileMaker 13 due to needing the new "list" type summary field added in that version of the software.
But every record in the entire table has a value in that field, so it would be matching on other records that aren't in the found set. (BTW, you could still manually get the "list of" feature in previous versions by doing a "Copy All Records" or looping through records.)
Here is the exact situation, so you can see a real-world example. This is what my client is trying to accomplish:- He is a talent agent who books children on movies & TV shows & commercials.- He is looking at a found set of hundreds of children.- Each child has a "Family ID", which is the ID# of the family that they belong to.- He would like to reduce (constrain) his found set to only those children whose Family ID's have 2 or more children IN THAT CURRENT FOUND SET. In other words, he wants to reduce his found set to siblings who are already in the original found set.- So let's say that Family ID #35 normally has 8 children in the entire FileMaker table who all have that family ID, but ONLY ONE child from that family is currently in his current found set. He wants that one child to be removed from his current found set, because none of that child's brothers or sisters are already in the found set with him.- But let's say that TWO (OR MORE) CHILDREN from Family ID #35 are in his current found set. He wants all of those children to stay in his current found set.To solve this problem, I wrote the script in the screenshot below. I really didn't need to add that $last_record variable, but I thought it would be cleaner to add it in.See screenshot below.Best,Scott
But every record in the entire table has a value in that field, so it would be matching on other records that aren't in the found set.
That is not the case. As the relationship only matches to records in the current found set.
Your match fields would look like this:
LayoutTableOccurrence::gIDList = LayoutTableOccurrence 2::PrimaryKey AND
LayotuTableOccurrence::DupField = LayoutTableOccrurrence 2::DupField
The first pair of match fields limits the matching to only those records in your current found set.
So let's say that Family ID #35 normally has 8 children in the entire FileMaker table who all have that family ID, but ONLY ONE child from that family is currently in his current found set.
In which case, the count returned is one and it is not identified as a duplicate within the found set. What you describe would be the case if the self join only matched by the DupField and not also by the list of IDs.
BTW, you could still manually get the "list of" feature in previous versions by doing a "Copy All Records" or looping through records.
I'm aware of that but don't consider it a truly practical option due to the amount of extra layout design and coding needed to make it work.
Ahhhhh, I see what you're saying. Thanks for the further explanation! That makes sense!! The list of ID's is narrowing the relationship to just the current records. That's a pretty nifty way of doing this trick, without a looping script. Thanks, Phil! :)