Join Cris Ippolite for an in-depth discussion in this video Using the basic find functions, part of FileMaker Pro 13 Essential Training.
One of the key requirements to any data storage application, is the ability to retrieve your data. FileMaker provides that through its Find Mode features. So far in this title, we've been talking about adding records or editing records or even working with layouts, but now I want to show you how you can work with the data that's being stored in your database in a meaningful manner. Well, we're going to work with now is what's called Find Mode. If you go into Find Mode, you see that basically all Find Mode is, is just a blanked out version of the layout that you're on, with some interesting field frames.
The first thing you'll notice is that everything blanks out, and just allows you to enter some search criteria. We'll go through that in a second. And I wanted to point out that you can see these little magnifying glasses that indicate that you are in Find Mode. And you're not just creating a new record for example. This is something that was added a few versions ago, to make the user experience a little bit easier. We see that it doesn't affect any of the data that I've got in my database. It just allows me to find them and create something that's called a found set. So really understanding finds is all about talking about this concept of the found set.
A found set, are all the records that match the criteria that you enter in, in your FileMaker database. So the first thing to understand about finding in FileMaker, that actually is sort of a plus for FileMaker, is that wherever you are in the data base, whether you're in the customer list, the customer detail, the product detail, whatever it is. You can hit the Find button or go into View > Find, or the most common approach is to use the quick keys, enter find mode, and any layout that you have in your database can be used to enter search criteria.
So what this means is this can really be a benefit to your users if you train them on the different ways that you can perform finds in databases. That way, they can mine the data inside your database any way that they see fit. And all they have to do is use the functionality that's built right into the FileMaker application. Not only can you do a search and find mode inside your layout, like for example here, I'll enter CA into the state field, and hit Perform Find. I can also see the results in the same layout. So this might be a little bit different for those of you familiar with the web.
For example, when you do a search in Google, it goes to a different page and lists all the different results for you. So, if you can understand seeing the results in the same layout as entering the search criteria. You can understand what this experience is going to be like in FileMaker. But the important way in which we can distinguish whether we're looking at results of a find or all the records of the database, is in the upper left-hand corner of our database. You'll notice that we have this number right here, 1,523. That number indicates the total number of records that are stored in the table that we're on.
So, in this case, we're on a layout based on the customer table so that means we have 1,523 customer records in that table. The 24 in front of it indicates that those are the number of records within that 1,523 that actually match the criteria. So, as you can see here, I entered in CA in the state field and that tells me that we've got 24 matching records in our database. That's the basic concept behind a found set. Found sets are very important because isolating a found set allows you to do things like print a subset of records, export a subset of records do all sorts of tasks to adjust some of the records in your database and not all of them, and you can reset your found set by simply hitting Show All, or the corresponding quick key for Show All, which is Ctrl or Cmd+j, and it goes back to the default 1,523 records.
But if I went to print right now, it would print 1,523 records, because that's technically my found set, but if I went back and did my search. Now it's going to only print 24 of the records. The other thing that I would like to show you for some of the basics here, about how FileMaker identifies a match from the criteria that you enter versus the result. Let's do another find. I'm going to type into the city field the term los, l-o-s. And I'm going to perform my find. We see that it finds four matching records.
Lost Nation, Los Lunes, Lost Creek and Los Angeles. So even though I typed in LOS, it still found a match with LOST. So what's important here, is that FileMaker is doing something behind the scenes, that it's searching a value index and word index, which I'll get into in a second. The other part that's interesting about a found set, is you can toggle between the found records and the omitted records. Notice under the records menu I've got a show omitted option, that allows me to create a found set of all the records that don't match that criteria.
I can go back and see those again. And I can also do this through my find by saying, search for los. And instead of including the matching records, meaning including all the records that match this criteria in my return found set, I'm going to press this omit button and hit Perform Find. Now, what it did, is it shows me all of the records in my found set that do not match that criteria. So it really saved me the step of switching over to show omitted. Looking back at why I was getting the lost in my Los search, if I click inside the field and do Cmd+I on my Mac or Ctrl+I on Windows.
I'm going to see something called the Field Index. One of the ways that FileMaker does such extremely fast searches is that it's not actually searching every one fo the 1,523 records, it's actually just searching this thing called the Index. This is an inventory that FileMaker creates of all the different data that's inside of this field. So for example there might be several different Kirkland. Cities inside of this field so several records that I'll have Kirkland in it. But Kirkland is only listed here once in the index. So File Maker creates an index and sorts it alphabetically and when you search for L-o-s it looks for different words that might match that index.
So here you see all the individual words. By selecting this, show individual words option that are available to us. FileMaker is looking at these as values when they don't break them into words. So this is really just looking at al these characters in order, and seeing if we can find a match. So this is what's called the value index, versus the word index. So if we just put in the word Alice, we're going to find all the records that have Alice at the beginning, Alice at the end, it could be Alice doesn't live here anymore, or go ask Alice. Both of those will match the criteria if we enter in Alice.
But in the case of los and lost, what we actually did was enter in the value los. So that means that Los Angeles, Los Lunas, Los Creek, all these match because they all start with Los. So that's an example of the value index in FileMaker. Same thing you'll see happen if we go into. Find mode and type in angel into city, we got Los Angeles and Angels Camp, even though Angeles is the second word and angel is the first. So, what FileMaker does is break them down into individual words and then it uses the value index on those individual words to do a search.
So, once you have this expectation in mind, you should be really able to leverage these finding activities to be able to help mine and find all the data that you're looking for in your database. These are just basic ways to find data inside your FileMaker database. Keep in mind that in addition to FileMaker's ability to be able to create the data storage mechanism, like tables and fields, and also provide a user interface or layouts. It also provides this very powerful and rich way to retrieve data, otherwise known as Find Mode.
- Creating databases from Starter Solutions
- Determining what tables you need
- Defining key fields and creating relationships between tables
- Creating fields
- Creating, duplicating, editing, and deleting records
- Importing and exporting data
- Managing layouts and layout objects
- Using the Layout Assistant
- Applying themes to layouts
- Finding records and working with found sets
- Building reports and charts
- Exploring calculations
- Creating and triggering scripts
- Working with relationships in scripts, calculations, and charts