NightWing Enterprises Demo for FileMaker Pro™ Isolating a single occurrence of each value!   Distinct Values Toolkit For FileMaker 13 and later For the benefit of clients and fellow developers, the following demonstration file is provided for free download†. As the functionality of the FileMaker Pro platform increases, new techniques and new options become available for addressing long-standing challenges. Among them is the frequently encountered requirement to deal with multiple instances of the same values within a record set. Whether for identification or consolidation of duplicated values or for preparation of summaries or analyses, it's often necessary to extract a single instance of each value. Historically, isolating a single instance of each value when there are duplicates has generally involved creating additional relationships and calculations - at least one of each for every field that you may need to look for duplicate values in - and then performing a Find on an unstored calculation field to achieve a found set of unique values. That's quite a lot of overhead to address a single requirement - not to mention that Finds on unstored (and therefore unindexed) fields are relatively inefficient. Against this background, with the release of FileMaker 8.0 we published an innovative new way to isolate distinct values without the customary overhead. While this technique and others like it still have merit in many cases, additional options have since become available, and here we gather a selection of them together to form the basis of a toolkit for dealing with distinct values in FileMaker. As a point of reference, we include in this demo two scripts that apply conventional looping techniques to achieve found sets representing distinct values (either in the whole table - if that's where you start - or among found records). These are valid and useful techniques that have continuing application - but in the context of this demo they provide a starting point. A further set of techniques shown in the demos exploits a suite of three highly efficient (tail recursive) custom functions* that traverse the found set to return subsets of distinct values (either as a count, the values themselves, or the IDs of the records where the values are found). As is the case with summary fields, these functions are well suited to use in situations where the record count will be of moderate size - whereas they will result in noticeable delays if the number of records runs to many thousands (moreover the maximum record set that they will address is 50,000 due to the limits of recursion in FileMaker). The third set of techniques included here, again returns results either within a calculation or as part of a script that performs a Find. It uses the ExecuteSQL( ) function, and the intersection of its results with a Summary List field, to render an SQL SELECT DISTINCT result applicable to the found record set. While this technique is not constrained by the limits of either of the first two techniques, it does not perform as well, and is less flexible in application (eg it does not readily provide a basis for constraining an existing found set, as do the other methods shown here). While the examples included here don't set out to be exhaustive, we believe that they point the way towards solutions that will serve a majority of requirements for isolation and/or handling of distinct values using the available features of the FileMaker 13 Platform. * Installation of Custom Functions in your solution file(s) requires FileMaker Pro Advanced - however, once installed in a given file, they can be accessed and used in that file throughout the FileMaker platform.   Distinct Values Toolkit demo download (.zip archive)   Note 1: The .zip archive download (above) provides a copy of the demo file suitable for all supported platforms. Note 2: Before opening the demo file, please ensure you extract it from the .zip archive (eg to a local drive) - otherwise it will open in read-only mode and will not function as intended. †  This demo is provided free for private and educational purposes, and may be licensed for commercial and business applications. Should you require assistance implementing procedures such as those shown in our example files, please consider establishing an account with us for developer support.