New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Start learning with our library of video tutorials taught by experts. Get started

FileMaker Pro 11 Essential Training
Illustration by

Using logic functions


From:

FileMaker Pro 11 Essential Training

with Cris Ippolite

Video: Using logic functions

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.
Expand all | Collapse all
  1. 7m 22s
    1. Welcome
      1m 8s
    2. Understanding the FileMaker family
      2m 15s
    3. Using the FileMaker Quick Start screen
      2m 52s
    4. Using the exercise files
      1m 7s
  2. 21m 9s
    1. What is a database?
      2m 26s
    2. Flat vs. relational databases
      2m 29s
    3. How FileMaker works
      4m 48s
    4. Understanding the essential preferences
      3m 13s
    5. Touring the interface
      8m 13s
  3. 11m 44s
    1. Creating databases from templates
      2m 29s
    2. Creating new databases in the spreadsheet-like format
      5m 35s
    3. Importing tables
      3m 40s
  4. 9m 9s
    1. Determining which tables you will need
      5m 10s
    2. Creating tables in the Managing Tables window
      3m 59s
  5. 34m 47s
    1. Understanding relationship types
      6m 58s
    2. Diagramming relationships (with ER diagrams)
      8m 50s
    3. Determining which key fields you need
      5m 18s
    4. Defining primary and foreign keys
      7m 56s
    5. Creating relationships using the relationships graph and table occurrences
      5m 45s
  6. 50m 34s
    1. Deciding what fields you will need
      5m 34s
    2. Understanding field types
      7m 54s
    3. Defining fields
      9m 56s
    4. Using Auto Enter options in fields
      9m 33s
    5. Reviewing field validation options
      8m 16s
    6. Building with container fields
      9m 21s
  7. 14m 8s
    1. Creating and duplicating records
      5m 40s
    2. Editing and locking records
      4m 42s
    3. Deleting records and backing up files
      3m 46s
  8. 21m 6s
    1. Importing data
      9m 57s
    2. Importing records to refresh data
      7m 2s
    3. Exporting data
      4m 7s
  9. 18m 30s
    1. Understanding layouts
      4m 15s
    2. Using the Layout Assistant to create List views
      7m 59s
    3. Using the Layout Assistant to create labels and envelopes
      6m 16s
  10. 27m 11s
    1. Using the Layout Setup dialog box
      4m 54s
    2. Understanding layout parts
      4m 40s
    3. Understanding the new Inspector
      2m 26s
    4. Exploring the Status Area in Layout mode
      6m 46s
    5. Managing layouts and layout folders
      8m 25s
  11. 41m 9s
    1. Arranging, aligning, grouping, and locking layout objects
      11m 5s
    2. Placing and formatting objects, parts, and graphics
      4m 10s
    3. Formatting fields and applying field attributes
      8m 26s
    4. Setting field behaviors
      4m 4s
    5. Using the Tab Control feature
      9m 8s
    6. Setting tab order
      4m 16s
  12. 36m 48s
    1. Using the basic find functions
      7m 31s
    2. Reviewing new requests in the Find mode
      5m 54s
    3. Establishing search operators
      6m 43s
    4. Constraining or extending found sets
      3m 24s
    5. Finding records using date, time, or timestamp criteria
      5m 18s
    6. Using Fast Match and Quick Find
      4m 41s
    7. Working with saved finds
      3m 17s
  13. 17m 28s
    1. Sorting with one criterion
      6m 4s
    2. Sorting with related fields
      2m 18s
    3. Sorting with multiple criteria
      1m 36s
    4. Sorting using custom values
      3m 14s
    5. Sorting using buttons
      4m 16s
  14. 17m 14s
    1. Reviewing Field/Control styles
      5m 43s
    2. Creating and applying static value lists
      5m 20s
    3. Creating and applying dynamic value lists
      6m 11s
  15. 23m 52s
    1. Previewing pages and print options
      6m 20s
    2. Printing in different views
      2m 54s
    3. Sliding objects
      3m 26s
    4. Printing merge letters
      4m 53s
    5. Saving as a PDF or Excel file
      6m 19s
  16. 15m 1s
    1. Building simple reports with summary fields
      4m 36s
    2. Creating subsummary reports
      6m 51s
    3. Creating subsummary reports in Table view
      3m 34s
  17. 52m 19s
    1. Defining calculations
      2m 31s
    2. Exploring the Calculation dialog box
      5m 8s
    3. Using number functions
      12m 41s
    4. Using date and time functions
      4m 58s
    5. Using text functions
      11m 43s
    6. Using get functions
      4m 0s
    7. Using logic functions
      11m 18s
  18. 46m 56s
    1. Understanding scripts and script steps
      2m 23s
    2. Assigning script steps to buttons
      3m 54s
    3. Understanding the ScriptMaker dialog box
      8m 28s
    4. Creating multi-line scripts
      6m 44s
    5. Adding find criteria to a script
      4m 58s
    6. Understanding the If script step
      8m 36s
    7. Using script parameters
      4m 42s
    8. Reviewing the Send Mail option
      7m 11s
  19. 28m 0s
    1. Understanding script triggers
      2m 41s
    2. Using object-based triggers
      11m 58s
    3. Using layout-based triggers
      7m 51s
    4. Using file-based triggers (Open and Close scripts)
      5m 30s
  20. 56m 42s
    1. Using related fields
      7m 18s
    2. Creating portals and using portal filtering
      10m 38s
    3. Using related fields in calculations
      7m 6s
    4. Understanding multi-predicate relationships
      11m 11s
    5. Using the Go to Related Record script step
      7m 26s
    6. Creating a chart
      13m 3s
  21. 30s
    1. Goodbye
      30s

Watch this entire course now—plus get access to every course in the library. Each course includes high-quality videos taught by expert instructors.

Become a member
please wait ...
FileMaker Pro 11 Essential Training
9h 11m Beginner Jun 25, 2010

Viewers: in countries Watching now:

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.

Topics include:
  • Creating databases from templates
  • Creating fields in spreadsheet format
  • Creating tables and relationships
  • Defining key fields
  • Adding validation and auto-enter values to fields
  • Managing records, including duplicating, locking, and deleting records
  • Creating and managing layouts
  • Formatting layout objects
  • Finding and sorting data
  • Creating calculation fields
  • Building reports
  • Printing and saving as PDF or Excel
  • Writing and triggering scripts
  • Using relationships throughout a database
Subjects:
Business Databases
Software:
FileMaker Pro
Author:
Cris Ippolite

Using logic functions

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.


Expand all | Collapse all
please wait ...
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.
 
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

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.
Upgrade now


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

join now Upgrade now

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed FileMaker Pro 11 Essential Training.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

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

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Are you sure you want to delete this note?

No

Notes cannot be added for locked videos.

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:

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:

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

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.