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.
In this movie, I'd like to focus on some of the functions that are known as logical functions. The Logical Function group contains some of the most interesting and powerful functions available in FileMaker Pro. And unlike some of the other categories of functions that we've been reviewing, in which several functions are often similar in syntax and behavior, these logical functions are a collection of unique, and sometimes complex functions. So they really break up into two different kind of categories, and I'll show you examples of both. The first of these categories within the logical functions are used for performing conditional operations, and they're extremely popular when used either in calcs or in scripting.
So, let's take a look at a couple of these first. If we open up the File > Manage > Database window, you see that we've got our Invoices Table showing, and what we're going to do is create a new field called Commission. This is going to be a field that helps us determine how much commission each salesperson gets on a sale. So, let's go into the Field Name, type in the Field Name and choose Calculation, and then hit Create. Here we are in our Specify Calculation dialog window.
Now the popular and useful logical function I want to introduce you to is called the If function. You see that when we isolate all the logical functions, there's not a whole heck of a lot of them, but they're all pretty useful. Let's focus on the one right in the middle here, If. So you see it's got a few different parameters. The If is the most basic type of logical function that there is, in that it just says if this test is true, then do this result, but if this test is false, then do the other results. For example, if we did a plain English version of this, and we said something like, if it's raining, which would be our test there, then bring an umbrella - so that would be true - or if it's not raining, which would be false, then don't bring an umbrella.
Our brain works in this way, and that's why these functions are called logic, because really an If where it looks at a test and says if it's true or false is really part of the definition of what logic is. I'm going to show you an example, back in this Commission field that we're working on. So let's say that what we want to do is create a field that conditionally determines what the Commission rate on an invoice would be for a salesperson. Let's say that we're going to say one commission price for a salesperson every time they create an invoice would be 5% on any order over $1,000.
So, you see this is where the condition comes in. It's a test. It says, if the InvoiceTotal is greater than $1000, then you get 5%; otherwise, you don't get anything. So our test is whether or not the value is over 1,000, and if that's true, then we come up with a mathematical formula that determines 5% of the total. And if it's false, we just say nothing. So in our parameters, what we do is we enter in the test, which in this case is going to be InvoiceTotal > and we'll just type in 1,000.
So, that's our test. Is the InvoiceTotal value greater than 1,000? And the first result is the true, really, should say resultTrue and resultFalse, but if you can remember which is which, it gets pretty easy. So, inside resultOne, we don't have to just put a field in here or a value, we can put a whole mathematical formula. So in this case, I'm going to say InvoiceTotal*0.05, which of course, would give us 5% of the overall total. That's if the result is true. What if the result is false? Well, they get nothing.
And this result, of course, is going to be, well, a number. We'll keep it as a number, and we'll hit OK, and OK again. Now, let's go into Layout mode, and you see we've got the Commission field that's been added to the layout. I will hold down the Shift key and select the field, and the label. If yours did not appear at the bottom of your layout, in your exercise file, that's just because your FileMaker Preferences are set differently. So, you can go ahead and insert the field, if you'd like.
But now that we've got the field on our layout, and by the way, we know it's Number field, so let's go into our Inspector and choose Currency, just to make this one a little bit easier to read. So let's go back into Browse, Save our changes and take a look at what we've got here. So, we see we've got an invoice here that is certainly over 1,000. It's 32,000. So that means that it gives the 5% commission for this salesperson of $1,611 and some change.
But you see that this order is less than 1,000, it's only $493, so the salesperson, Marie, gets $0 commission. You see that it's evaluated record by record. So that's how we can apply a condition to a certain field. So, it's a very useful function in that regard. Let's go back to the Calculation, and I'm going to introduce you to something that could be described as a cousin of the If function. It's probably used just as often as the If function, and between those two functions there, they're used more than any other logical function in FileMaker, and this one is called Case.
Now, let me back up a second. Let's say that the commission structure has now changed. So instead of it being if it's over $1,000, you get 5%, and if it's not, you get zero. Let's say now there's several tiers to it. For example, you just got a memo that says the commission structure has changed. In this case, rather than just being, if it's above $1000 you get 5% or if it's not then you get 0, there's going to be some different tiers to it. Now the problem here is though, as you see, that we've got four different evaluations. It could be, if it's under $500, it's 5%.
If it's between $500-1199, it's 7.5%. Well, now we've got multiple tests. So, instead of an If function where we just have the one test and then the two results based on whether they're true or false, now we have the problem of multiple tests. So, the way that we rectify that is by using the very powerful Case function that you see here at the top of the Logical Function list. You see anytime there are parameters inside the curly brackets, those are what are called Optional Parameters.
So we're going to ignore those for a second here. Now, what you'll notice, in the Case function, is we just have a test and a result. But what's cool about the Case function is you can have as many tests as you want with their own corresponding results. That's going to be perfect for what we're doing here. So let's apply that new tiered structure to our Commission field, using Case. The other thing I want to point out to you is that you'll notice that I am clicking and hitting the Return key, and creating some white space for me in my calculation entry area.
White space is ignored when FileMaker evaluates your calculation, but it's very helpful when you're programming these functions to be able to see things in a little bit more organized fashion. So, let's put in our first test. And based on the memo, it was whether or not the InvoiceTotal was less than $500. InvoiceTotal<500, and if the InvoiceTotal<500, then what would be the result? If it's true, then we're going to say InvoiceTotal*0.05, which gives us 5% of the total.
Now, you notice we have the tests, and then we have the first result, if it's true. What if it's false? Well, then what the Case statement does is it moves on to the next test. So, if it's true, it stops right here. That's the first very important thing to understand here is it's just got kind of a cascading effect. That means is a Case function will stop the moment that it evaluates and finds a true result. It doesn't bother going and evaluating the rest, and we'll see how that's significant here as well. But to delimit the different tests, we're going to put a semicolon in here, which is a very common delimiter in FileMaker functions.
Now, we could save ourselves some time by copying and pasting these different tests. So, the next one is if the InvoiceTotal < 1,199, then it's InvoiceTotal*0.075. Then we could do the next one. Now notice what I've done here. I'm doing less thans all the way across the board, and so the reason is that let's say something comes in at 700. Well, 700 is not less than 500, so it passes this test, but it is less than 1,199, so it stops right here.
So, since I'm using less than in a cascading fashion, which I'm going to have to continue doing here on this next one, then that means that any number will fall within one of these tiers. And if you do them in the right order, then you'll get the right result. But if I, for example, do InvoiceTotal > 2,000, we'll look at these all together here in a second. It's 0.1. So now I'm missing one more tier. I've got the <500. It'd be 5%, between 501 and 1,199 would be 0.075, which is why we've got the two less thans in the tier, but we've got one more that we can add, which would be if the InvoiceTotal is less than 2,000, that gives us 0.1, and if it's greater than, it's 12%.
So now, well we've actually got our ranges, 0-500, from 501-1,199, from 1,200-2,000, and anything greater than 2,000. So that's what having these in a cascading fashion gives us. Of course, that result is going to be a Number, and we hit OK, and now we see -- so the first record is over 2,000, so that's 12%, giving us a total of 2,684 and some change. Next record is under 500, so that's 5%, 15.74, and you get the gist. Now, the next record is over 1,199 but under 2,000, so that makes it 10%, giving us $121.23.
So you see, it's evaluated differently and conditionally, based on every record. So those were just a couple examples of the logical functions, but the most popular one. So, if you're going to learn any of them, learn the If function, and the Case function, because you'll use those all throughout your FileMaker Application. Couple things just to remember that we've noted here, that anytime you're putting text inside your functions, make sure you wrap them in quotations, and you should always remember that you don't always have to choose the function. Sometimes it's a great way to get familiar with the syntax by just typing it out inside your formula entry area.
It's good practice that way. So, at some point, you will be writing these all freehand, so the sooner you can do that and feel comfortable with it, the more adept you'll be at creating FileMaker calculations. And also don't forget you always have to pick the results before you save the calculation formula. Even with calculations, the result must have a type. So, don't forget to choose that option appropriately, because you might get strange results. There are lot of logical functions to learn, and they're all very powerful, and you'll find yourself using them inside scripts, calculations, validations, Auto-Enter calcs, all sorts of different things.
The Logical Function set's a particularly good one to get familiar with, so do your best to integrate these and apply these functions as often as you can throughout your FileMaker solution.
Find answers to the most frequently asked questions about FileMaker Pro 11 Essential Training .
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.