Working With Unique Or Duplicate FileMaker Values

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

There are always times when a FileMaker table will contain unique values in fields and times when it will contain duplicate values in a field across a set of records. There are times that you may want to find duplicates and there may be times you want to find only unique values. FileMaker does have an operator for finding duplicate values in a field ( ! ) but does not have an operator for unique values.

However, the Show Omitted Only command (under Records menu) takes the found set of records and makes them the omitted set and vice versa. So your found set and omitted set flip and this means that you can do a find for duplicates and then swap that found set to the omitted set. This would give you a found set of records with only unique values.

Another option is to use the Omit check box in the search. You can also do a find for duplicates but click the omit checkbox in the status area. This is better suited for constrained finds within a found set.

If you are wanting to build a list of unique values, you can define a value list based on that field. Follow that up with a calculation using the Design function ValueListItems. This will give you a unique set of values, since a value list uses the fields index.

More info about the author and FileMaker in general, contact me at

© 2008 - Dwayne Wright -

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.