Video: Establishing search operators

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.

In FileMaker Pro 11 Essential Training, Cris Ippolite demonstrates the principal features and functions of this popular database software, including creating tables and relationships, managing fields and records, and working with layouts. The course shows FileMaker developers how to find, sort, and share data as well as how to create reports, calculations, and scripts. It also covers brand new features in FileMaker Pro 11 such as the Inspector tool, charting, and portal filtering. Exercise files accompany the course.

Establishing search operators

When you're searching for data in FileMaker Pro, you don't always have tospecify the exact criterion;for example, if I want to find all the invoices in my example file here, thatwere created between the year 2000 and 2010, I don't actually have to create 10new find requests each with a different year.For example, going into Find mode, I won't necessarily have to put in 2,000, thenNew Request, 2,001 and so on.And by the way, you'll also notice one example of this is I just put in theyear 2000 and FileMaker recognized it, because I was in a date field, that willautomatically reformat it to put in wildcard characters, and we'll get to thosehere 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 MatchingRecords 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 elseand 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 andhit Perform, we want it to come up with no records, because we would be littleworrisome 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 executingour 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'redoing things like scrubbing your data, for example.

You will also notice a very handy one back in Find mode, the equal sign that's acalled match whole word.What that really means is that if you put in an equal sign ,it's going to find anexact 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 tellingFileMaker, because I haven't put anything else in there, isn't that I wanted tofind 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 thevalue 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 recordsthat have empty values in certain fields, and that can be useful, again, when youare 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, andPerform Find, and it tells me that I don't have any matching records, but thatworks 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 numberfield, and I type in 1000...1500 and hit Perform Find, you see I get sevenrecords, 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 afield. 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 theequal sign, meaning anything that had empty value or no value in Ship Via, we gotfive, so this makes sense.This is the inverse of that.So you should really experiment with these to get familiar with how theywork with your data.

I think that not only will you find them useful, from a developmentperspective, but if you train your users on these, they will find them to beuseful, 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 asearch in the Email field, and what I am looking for, let's say, are properlyformatted Emails, which we would use this trick to say I want to search in thisfield for the At symbol character, and if I find records that don't have an Atsymbol in there, then I know that I don't have a real Email or a properlyformatted 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 herethat 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 ofit'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 exclamationpoint, and I want it to be recognized just as the character, what I have to dothere is wrap it in quotations.And anytime something in quotations, in this context, FileMaker will evaluatethis 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 thisrecord 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 inL@s, and I Perform a Find, I am going to get Las, and Las, but that's because weput the wildcard in between L and the S, so these are the correct values thatwe are getting in return.So in addition of doing multiple requests, you can also insert differentSearch operators to assists you in locating data with minimal data entry inthe search requests themselves.

Q: In the Chapter 16 tutorial, “Using Text Functions,” the instructor discusses how to calculate the First Name and Last Name from the Full Name. However, the method does not account for names ending with “Jr.” or “Sr.” or “III,” etc. How can I account for added suffixes in names?

A: For cases like this, you can create a third "Suffix" field. Then change the FullName calculation to:

NameFirst&" "&NameLast&" "&Suffix

This way, nothing will appear if the Suffix has no value, but if it does have a value the suffix will appear.

Q: What information is actually on the “Invoice Line Item” table in the examples, and how does it actually connect to the tables that it comes from?

A: The information in each line item is native to the "Invoice Line Item" table. The fields are defined in that table and each record represents "A Product appearing on an Invoice."
Each time a product is used on an invoice, a record in the line item table is created. Many of the fields, for example "Quantity," are native to that table because those values only exists when a Product is used in an Invoice, and not as attributes of a Product itself.

Sorry, there are no matches for your search "" —to search again, type in another word or phrase and click search.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.

Already a member ?

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships.
Learn more

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.

Sign up and receive emails about lynda.com and our online training library:

new course releases

newsletter

general communications

special notices

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

new course releases

newsletter

general communications

special notices

Here’s our privacy policy with more details about how we handle your information.