F is for find the found set
One of the advantages of using a database rather than a spreadsheet is the innate ability to find data. Sure, with a spreadsheet you can apply filters but it is not really the ‘wheelhouse’ of a spreadsheet.
When using FileMaker Pro, the status area shows how many records are in the table you are viewing on the current layout. In the top screenshot below, the current table has 102 records in total (and the current record is #98).
After a find request has been performed, there are 62 records in the found set. When you show all records, the found set is the entire table.
Why is the found set important?
Many operations in FileMaker apply to the found set of records (and therefore not to the ‘unfound set’). The following will apply only to the found set:
Replace Field Contents
Delete Found Records
Export Records
Constrain Found Set
Sort Records
Save Records as PDF or Excel
Print
It is commonplace to find a set of records before performing these operations. For example, if you wanted to print a daily invoicing report, you would first find invoice records from the day, then sort and print the found set.
Records not in the found set are effectively excluded or protected from bulk operations.
How is a found set managed?
As shown above, the most common way you will create a found set of records is to perform a find.
A found set is also created when records are imported. The imported records form a found set after import. So you may have 100/1100 records when you import 100 new records into an existing table of 1000 records. This import behaviour allows you to look over the imported records and possibly Delete Found Records… if there was an error in the import order.
An existing found set can be reduced by performing a Constrain Found Set command. This will perform the find request on the found set of records only. For example, if you had found all contacts in Queensland, enter Find mode and type Annandale in the city field, then choose Requests > Constrain Found Set, the found set will be constrained (reduced) to those Queensland contacts from Annandale (but not those from Annandale in NSW).
The Omit and Omit Multiple commands also reduce the found set. These commands are commonly used to manually remove certain records from the found set rather than by performing a find. For example, if you had a list of outstanding debtors to call and you knew that certain people had already made contact, you might omit their record before printing a call list.
A found set can also be extended. If you had a found set of all contacts in Victoria, enter Find mode and type F in the sex field, then choose Requests > Extend Found Set, the found set will be extended to include all female contacts (from states other than Victoria).
Did you know?
You can have a found set of zero (0) records. This may result from a scripted operation where a built-in find did not find any records. Or it could be that you had a found set and omitted records until there were none left.
Inverse of the found set
It is sometimes useful to show all records that have not been found. For example, you may find all the contacts with an email address and run a script to send each an email. Then you want to work with those who did not get an email. While you could do a new search, there is an easier way – Records > Show Omitted Only. This command effectively inverses the found set.
Did you know?
Clicking the ‘pie’ next to the found set indication in the status toolbar performs the Show Omitted Only command.
Multiple found sets
Any one window can only display and manage a single found set for any table. What if you wanted to compare two found sets for the same table? For example, if you wanted to report separately on 2016 and 2017 invoices at the same time.
You could do this in two windows – create a new window (Window > New Window) and perform a new find request. Initially, the new window will inherit the found set from the original window. Thereafter, each window can maintain different found sets of records (and different sort orders).
Show All Records
The Records > Show All Records command is used to return to the found set of all records in the table. The keyboard shortcut is unusual to say the least – Cmd/Ctrl–J. Just one of those weird ones that you commit to memory.
When the Show All Records command is run, any sort order that had been applied to a restricted set of records is removed – the total record set is presented unsorted.
Ignoring the found set
There are some operations that ignore the found set – they operate the same way regardless of whether you have found one, ten or all records.
The new found set places the old found set for any Perform Find request performed. For a standard find request, FileMaker will search all records in the table. The found set will be unsorted.
Viewing a field index (Insert > From Index…) will show the field index for all records regardless of the found set.
The final word
The found set is an important concept for FileMaker users. While not difficult, it is vital to understand how found sets work (and don’t work) to make your life easier.