Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you're searching for data in FileMaker Pro, you don't always have to specify the exact criterion; for example, if I want to find all the invoices in my example file here, that were created between the year 2000 and 2010, I don't actually have to create 10 new find requests each with a different year. For example, going into Find mode, I won't necessarily have to put in 2,000, then New Request, 2,001 and so on. And by the way, you'll also notice one example of this is I just put in the year 2000 and FileMaker recognized it, because I was in a date field, that will automatically reformat it to put in wildcard characters, and we'll get to those here just in a moment, but what I can do instead is use something that's called Search Operators.
So back in Find mode, you'll notice in the Layout bar, next to the Matching Records Include or Omit, that I've got a draft on your called Operators. We have a list of different characters that FileMaker interprets when added in a search criteria. For example, let me show you some of the easy and more useful ones: find duplicates. If you just simply put an exclamation point into a field and nothing else and then Perform your Find, you can then find all the records that have the same value. Of course, it's going to find the original and the duplicate. This is something that a lot of users use to clean up their database.
So, for example, if I put an exclamation point in the Invoice number field and hit Perform, we want it to come up with no records, because we would be little worrisome if there were duplicate invoice numbers in our database. So this is a good way to stay on top of the data that you have in your system, but putting that in the Invoice Date field like we had done before, in executing our find, gives us 64 records. What this actually tells us is that it's really 32 records with a duplicate. It's what that says. Or they could have multiple duplicates. It gives you the original and the duplicate, which is very useful when you're doing things like scrubbing your data, for example.
You will also notice a very handy one back in Find mode, the equal sign that's a called match whole word. What that really means is that if you put in an equal sign ,it's going to find an exact match for any value that follows the equal sign. So let me give you an example of that. If I go into Ship Via, and I put it in an equal sign, right now what I'm telling FileMaker, because I haven't put anything else in there, isn't that I wanted to find every record with an equal sign in the Ship Via field - that it actually would not do - but instead, I'm saying find any record where the value in Ship Via is nothing.
Why is it nothing? bBcause so far that's what I've got following the equal sign. So if you just leave it this way, this is the great way for you to find records that have empty values in certain fields, and that can be useful, again, when you are trying to clean up your data. So let me Perform Find here. You see that I have got five records out of my 146 that do not have a value in Ship Via. That's what the equal sign allows us to do, another excellent tool for cleaning up your data. And there are some more obvious ones, for example, less than, greater than, less than or equal to, those kind of things.
Those will work on number fields and date fields. Keep in mind that in those cases, you still have ranges. For example, I could put in Invoice date less than 2009, for example, and Perform Find, and it tells me that I don't have any matching records, but that works equally well in a Date field as it does in a Number field. But speaking of ranges, you will see there is also a range option there, which is really just ... So you will notice that you don't even have to pick this dropdown menu. This is really just more of a legend, or a guide.
You can simply just type these values in there. So, for example, if I went in to the Invoice Total field, which is a number field, and I type in 1000...1500 and hit Perform Find, you see I get seven records, and all those records have a value between 1000 and 1500. This one is even very close. It's 1490. In that case, it's giving me the range. I can also go into Find, into an open ended, 1000... range and hit Perform Find.
It tells me that 80 of these records are either $1,000 or more. And there are a lot of things to choose from. Another one that's helpful is the zero or more characters, which is that asterisk. So let's say you are trying to find any record that actually has a value in a field. Just simply type and asterisk in there and nothing else and perform a find, and you will see now we get 141 out of the 146. If you remember when we did the equal sign, meaning anything that had empty value or no value in Ship Via, we got five, so this makes sense. This is the inverse of that. So you should really experiment with these to get familiar with how they work with your data.
I think that not only will you find them useful, from a development perspective, but if you train your users on these, they will find them to be useful, but one special thing that you should be aware of, though. If you switch over to the Customer List layout, let's say I want to do a search in the Email field, and what I am looking for, let's say, are properly formatted Emails, which we would use this trick to say I want to search in this field for the At symbol character, and if I find records that don't have an At symbol in there, then I know that I don't have a real Email or a properly formatted Email in this field.
Let's see what happens when I put the At symbol in this field, and then we hit Perform. You see I get No records matches criteria. Now that might not actually be true, because we could have some records in here that do not have any values. For example, if I go in and I take the At symbol out of this first record. All right. Now let's do that search again. Let's say At symbol, and I want to omit anything without the At symbol. So when I hit Perform Find, now it gives me everything. Why is it giving me all the At symbols and the not? Well, the reason for that is because FileMaker has the At symbol as one of it's Search operators.
Unfortunately, they had this built-into the program before Emails were even around. So what this means is it's more of like a wildcard character, which means any one character. So if I am truly searching for an At symbol, or an equal sign, or an exclamation point, and I want it to be recognized just as the character, what I have to do there is wrap it in quotations. And anytime something in quotations, in this context, FileMaker will evaluate this as just the character that you are looking for, rather than the functions. So now you see it performs properly.
I omitted anything that had an At symbol in it, and sure enough, we see this record here that matches that criteria. How is this At symbol used? Well that actually has its own valuable use for us. So, for example, if we go into Find mode again, and under City, if I type in L@s, and I Perform a Find, I am going to get Las, and Las, but that's because we put the wildcard in between L and the S, so these are the correct values that we are getting in return. So in addition of doing multiple requests, you can also insert different Search operators to assists you in locating data with minimal data entry in the search requests themselves.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64672 Viewers
80 Video lessons · 124298 Viewers
52 Video lessons · 60237 Viewers
59 Video lessons · 46066 Viewers