Lesson 5: Search Features Part 1


Welcome to Part 1 of 3 talking about Search Features in FileMaker Pro. FileMaker has an interesting concept when you do a search called a Found Set. Let’s do a really simple search – if we just hit the Find button (Command/Ctrl F), it brings you into Find Mode, and it uses the same layout you use for data entry. You can do something really simple like Portland in the city field – enter or Perform Find will execute that find. So the Found Set is the number of records in your database that match the search that you just did. One of the interesting things you’ll see is you can click on the chart to flip the Found Set from the positive and the negative – if you click the button to flip it in the menu and choose that feature, you can get the opposite, which are all the records not in Portland. Sometimes that’s really useful because you want to find something that’s really complex and the search for the opposite of that thing is actually simpler.

FileMaker allows you to search more than 1 field at the same time. If I only want to find Portland, Oregon, I can put in Portland into the City field, then in the State field OR. This will find only Portland, Oregon. This gives me a smaller set of records. FM also has, when looking at a search, a drop down menu called Operators. The default mode is Starts With – it actually searches for anything that starts with that particular word. You also have Exact Matches – there are two different types of Exact Matches that match exactly with the field.

Ranges are interesting. Let’s take a look at a few of these. Let’s say I wanted to find all of the records that were contacted in 2017. One way to do this is to put in a date range – 1/1/2017.. Either two or three dots works for the range field 12/31/2017. That’s kind of laborious, but that’ll give you the 283 records that match.

FM offers a really cool feature – in a date field, just put in 2017 and then it’ll automatically know that you’re looking for a year and it’ll automatically put in */*/2017, which is the same as the date range above.

You can even combine these and say do this search for 2017 and then find only for people who are in OR. That finds just this small set of records for which both things are true. An interesting thing you can do to join these is also do, rather than both types of search in one request, you can do two separate search requests. Say, find all the records that are in OR, then make a new request for the ones last contacted in 2017. That’ll actually get you, rather than the records that had both things true, will get you all of the records that had either thing true – a much larger set of records.

Range features – if I just type in Portland, I get Portland and S Portland. You can modify your previous search by hitting Command R/Ctrl R, which is Refind. You can see here in the menu it’s modify last find. That brings up the last search that you did – it might’ve been pretty complex, so that’s a good timesaver. I’m going to put in an =portland and that will still get me South Portland, because it’s looking for that exact word. If I put something in like Port, I get Port Richey, and if I get an =Port, that’s going to not search for Portland anymore, it’s going to look for the whole entire word Port. If I put in ==port, I get nothing. There’s nothing that’s just Port. If I put in ==portland, I get all the ones that are Portland but not South Portland. So those are some of the other features that you can get when you’re looking at the operators function.

Duplicate values are really useful if you’re looking for duplicate names of companies. I have some companies in my sample set of data (free data that comes with Search Results). If you use the duplicate value operator, that will find all of the records that actually match where those things are duplicated. These don’t necessarily jump out right away because I haven’t sorted them yet. If I sort them by company name, I can see I have 3 Amanda Jones’ in different cities, Archdiocese of PA, etc. so I have duplicate records.

Let’s take a look at one more thing – in Operators, you also have greater than and less than. What if you want to find all records contacted after June 1st of 2017? That would be >6/1/2017. That will find all records greater than that. If you sort by date, you can see records in chronological order as descending or ascending order. Notice that 6/2 is there but not 6/1. There’s also greater than or equal to, which is >= in FileMaker. This will actually find the 6/1 as well as the 6/2/2017.

Lesson 6: Search Features Part 2


We’ve talked about Found Sets and basic searches in the last video and also looked at the Operators menu. Let’s talk about Relational Search. On this particular layout, all the records that we’re searching on are in the company table. If I click on one of these, I can see I’ve got data in 2 other tables. Some of these tables might have more than 1 contact, some might have an invoice.

If I want to find all company records that have an invoice for a telecaster, I can go to find more and put telecaster in any of the lines. This will find all of the records that have a telecaster in any line from the related data. You can also do combined searches for this. For example, if you only want to find somebody whose name starts with E that bought a telecaster, that would be Refind (Command R/Ctrl R) that keeps the telecaster in here, then I go to the First Name and put E – this will find only people whose names start with E that have bought a telecaster. Only 12 names in this list. You can further narrow that down – you can only find ones that are in Washington, D.C. with names that start with E that bought a telecaster – only 1 record.

You can omit search requests. In Search Mode, look in the menu – you get a different menu bar. A new request we looked at in the first video, when you look for things that don’t overlap. Everybody in OR, Everybody in WA. If I do a new search, I get everyone in either of those two states, which is pretty cool. You can do a second request – everyone that’s in OR. Second request, omit all the ones that are in Portland. So I’ll find everyone in OR, but I don’t want to see everyone that’s in Portland.

If I did the same search but in the other order – first record Omit Portland, new request (not Omit) for Oregon, you would think that you would get the same result, but you don’t. We’ve got 20,000 results with a bunch of records that aren’t in OR at all, and some that are in Portland. Omit requests must be the second request, not the first one. You can also have multiple Omit requests – you can say you want OR but not Portland (Omit), Bend (Omit) and Eugene (Omit). I call these positive requests for an include request and 3 negative requests to omit. That gives me my list of other cities that are not those three.

I talked about Refind being useful – in the drop-down menu, this is Modify Last Find. I’ll see what I previously searched for. This is a good way to keep sculpting it. If we’re doing a large accounting report, I find this to be a really useful feature.

Lesson 7: Search Features Part 3


This is part 3 of using FM Search Features. We’ll talk about the Quick Find feature that’s built into FM and some of the caveats and cool features that aren’t obvious about this feature. The other thing we’ll talk about is searching against email addresses, which isn’t as obvious as you might think.

We’ll start with Quick Find. FileMaker by default puts a Quick Find widget at the top of the layout. There’s a keyboard shortcut where you hide the menu bar area. If this is hidden you don’t have access to Quick Find. A second thing you need to know about Quick Find is that users can control and configure that menu bar. They can actually click Customize Toolbar when they’re in layout mode and use a different toolbar that doesn’t contain Quick Find. A user could defeat the feature for Quick Find in their copy of FileMaker, which will affect your solution and any other solution on that computer. Quick Find is on by default.

Quick Find is pretty neat because it’ll search against everything on the layout by default. If I search Honda, it’ll search against my database and find all the records that match Honda. If I put in Portland, it’ll find all my Portlands. If I search for date, like everyone who was contacted in 2018, it’ll search against whatever date fields are on the layout. This is useful, but not that useful on a detail layout, because the record doesn’t change.

If I’m looking for AppWorks, Quick Find will take me to the first record that matches, but it doesn’t work well when looking for multiple records. Going back to my Portland example, if I hit Portland and hit Enter, nothing changes on the screen. It isn’t obvious that anything has changed, but my Found Set changed from one record to 267 records. It’s actually more useful to use Quick Find when you’re on a list view. If I put in a name, I instantly get the reaction I’m looking for, which is the set of records actually changing on the screen.

Here’s a cool feature about Quick Find – you can configure which fields on the layout are searched with Quick Find. When you go to layout mode, if you don’t want the State field to be searched on, for example, there’s an option on the right that says include field for Quick Find. There’s a green icon that shows you whether that field is on or not for Quick Find. If you have a field in your database that’s related, for example, the invoice number, if you have that field set for Quick Find, it will show a yellow search magnifying glass telling you that the search will be slower – its a related field, so it won’t be as fast. Generally those are to be avoided, but there’s some utility to it as well. That’s how you can configure and control it – I took out the State field, so if I search for OR, it won’t show Oregon anymore. Here’s ortho and New Orleans, etc.

The second feature is that Quick Find joins multiple fields together as one, even across different field types. Let’s say I look for everybody in Orlando, but I only want to find the ones in Orlando that have a a contact date in 2014. I can put Orlando 2014 in and FileMaker will magically join those fields together. It knows that 2014 is a year, and knows to look in the date field for a year. When you click out, it expands it to */*/2014 – that same thing works in Quick Find as well. It’s really cool that FileMaker will combine those together. If I put in blak 2014, then I get the record for Bruce Blakley. It works in either order, so if I put in 2014 blak, I get the same search. The order doesn’t matter, and it will search on multiple types of fields. It’s not just date fields and text fields, it will also search on text fields. If I want to find all invoices that are $1584, I can search 1584 and this will return all records that have that number.

I’ve configured this to search in the zip code and phone number fields, and I don’t have the invoice dollar amount set to be searched. When I go to my detail layout, I don’t have the invoice amount set to be searchable in QF. If I turn that on and then do my exact same search again, now I’ll actually get a search that includes all of the searches for that invoice $ amount as well. I do want to note that those related searches are slower. Even though this is a hosted file that has 20,000 records, it’s still pretty fast. The more searches you do against fields that are related, the more distantly related, and any kind of unstored calculation field are going to be slow searches.

The second thing that I wanted to show is searching for emails. When you do a search, you can use the operator. One of the operators is the @ sign – it looks for one specific character. For example, if I wanted to search on Keith, I can just type in Keith. If I wanted to find it, but wasn’t sure what one of those letters was, I can type k@@@h, that will search on any words any letters, but it still has to be a five letter word that will that will match that search. That’s a little tough to demo because there aren’t many words start with that. An easier way to do that is to actually type k and rather than using the @ character, use the asterisk character. Type in k*h and that will do the same thing, except this one actually finds Kenneth and Keith and a bunch of other things that start with K and end with H.

Let’s go back to the @ character because one of the things I really want to be able to do is to be able to just put in an email address. You would think that if you did a search for that it would work, but it won’t. I’m going to show you two ways around it. One way around it is if you put that in quotes. Quotes are one of the other operators. You can actually search for something that’s in quotes, which will match the specific phrase and it will ignore that character, so it will actually search more intelligently. I could even put in Matt@* and that will define Matt at any email address.

A second way to search correctly on an email address is by going to Manage Database and going into your table that has the email field. Double click on the email field. By default the language is English or the native language of whatever country you’re in. At the very bottom of the list there’s actually a choice for Unicode. Store the language of the field as Unicode, instead of English. Now if you do a search it will actually work correctly, because it’s basically ignoring that @ character. There is an issue, though, if this entered with uppercase. If I modify my email address and put an uppercase M, the Unicode doesn’t work. The downside of a Unicode is that it’s case sensitive, whereas the normal FileMaker search is not case sensitive. The normal FileMaker search doesn’t care if it’s upper or lowercase. For example if I put in like you know aPPwO, that’s actually still going to find AppWorks even if the case doesn’t match. If you turn Unicode on, it will defeat that.