Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Another one of the benefits while searching in Find mode is that the ability to be able to search within date and time fields. If you look inside of our exercise files, you see that we have got a couple of fields here, in the Invoice list, that are date fields and some that are time. So, for example, we have got Invoice date and ship time timestamp and then the ship time. In some of the more recent versions of FileMaker, there has been a lot of new improvements to date and time searching capabilities, and they are actually kind of cool. Each of the syntax that I will show here applies only to fields that are set up specifically as date, time or timestamp.
So the same types of techniques won't work in number fields, or text fields or anything like that. So, first let's look at date fields. You see that we have got several different dates in our database. If we hit Show All, we see all the different dates: 2011, 2009, and so on. They are all formatted as dates with month, day, and year, and normally when you search inside a FileMaker field, you will be searching in the exact syntax that's contained within the field, because as with a date field, I try to enter a value in a new record in anything but this format, I would get an error from FileMaker. So normally you would probably expect that same type of behavior when you are searching in FileMaker, but that's not the case, and it has some interesting effects.
So let me give you an example. Let's go in a Find mode and in the Invoice Date, I am going to enter in a value that's not formatted as a date. As a matter of fact, I am just going to try entering in 2010. What this demonstrates is that by typing in 2010, FileMaker recognizes that it's a four digit year, and it creates a wildcard search for you. So we have got a wildcard character, forward slash wildcard character, another forward slash and then the year. So really it's not searching on 2010 because, in fact, it does require date formatted fields. What it's doing is putting in wildcard characters on your behalf.
So let's cancel this Find, go into another one and this time let's type in 6/2010. Now, in this case, what it will do is it will give us 6/*/2010. So the wildcard is going to be in the date. So what we will probably get in this case, if we hit Perform Find, is anything that has the month of 6 and the year of 2010. So in English, what that means is it's giving us any day within June of 2010. How about if we go into Find mode and we just simply enter in 6? Now, let's see who FileMaker handles it.
We are just hitting the New Request because that allows us to see what happens, because otherwise, FileMaker does it so quickly. Well, if you are just enter in a single digit, or let's say you are enter in 12 for example, it assumes that you are looking for the month, and it also assumes that you are looking for the month in the current year. So if I just go in and redo another Find, and put 6 in there, what I am going to get back are all of the records from 6 of 2010. Now keep in mind that the exercises that I am doing here involve current year, and at the time of this recording, it's 2010.
So if you are looking at this with a computer date of anything but 2010, you are going to get different search results that I am getting here. In a previous movie, we talked about Search Operators and using ranges, but we can also use ranges with these abbreviated date functions. So, for example, by going here and I type in 4/2010...6/2010, now we are combining the wildcard search format with search operators. So in this case, what we are going to get are any date between April of 2010, including June 2010 and of course, May in between.
So let's hit Perform Find, and sure enough, you see we have got April, May, June all peppered in there. But one of the most interesting ways to search in a date, timestamp or time field involves not entering in numeric values at all. So if we go into Find mode, and I go into a date field and I type in Friday, you'd think this would kick it out because it can't even reformat it - we do with a little trick of looking in New Request - all it does is change it to Fri, which is something that's built-into FileMaker to identify any date that was actually a Friday.
So let's go back and just do one Friday and hit Perform Find. Now without there being a field that's says what day of the week any of these invoices were written up, FileMaker still knows that these 33 records represent a date that was a Friday on a calendar. So that could be very useful, if you are interested in doing some trending or digging through your data to try to find out when the most popular day for orders might be - something along those lines. The same techniques are true for time fields, as well. So you see we have got a field called Ship Time.
If we go into Ship Time, and just type in 3pm and do a Perform, we see that we will get any record that was written up in the 3 o'clock hour. Irrespective of the date it was done, we do get everything from 3:04, 3:17, 3:15. You see you get it just there and also these types of things can work with our ranges. 3pm...6pm. Now this is any record written up within the hours of 3, 4, 5, or 6, and also we can just put in pm.
So if you interested to see what kind of ordering you get at night or in the afternoon versus in the morning, you can just simply enter pm and FileMaker will go and find all of the records that have time that was in the pm instead of am. If you get familiar with the searching syntax, it's not only going to help you, but as you train your users, and possibly as you start to work with scripts, which we will cover later in this title, the syntax can help you more accurately find date, time, and timestamp date within your database.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64848 Viewers
80 Video lessons · 124392 Viewers
52 Video lessons · 60319 Viewers
59 Video lessons · 46142 Viewers