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

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

FileMaker Pro 11 Essential Training
Illustration by

Using number functions


From:

FileMaker Pro 11 Essential Training

with Cris Ippolite

Video: Using number functions

In this movie, I am going to introduce you to a couple of the most useful and common functions that FileMaker offers for manipulating numeric data. You will also gain some additional experience using functions in the Calculation dialog window, as well. That Number functions are used to manipulate and perform functions with numeric data, and as a class, they are relatively easy to learn because most of them only have one or two parameters. We'll talk again about what parameters are. So this is a pretty good starting point for learning calculation functions, as they all just produce the numeric results, and you may be familiar with some of these due to some of their mathematical properties anyways.
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 number functions

In this movie, I am going to introduce you to a couple of the most useful and common functions that FileMaker offers for manipulating numeric data. You will also gain some additional experience using functions in the Calculation dialog window, as well. That Number functions are used to manipulate and perform functions with numeric data, and as a class, they are relatively easy to learn because most of them only have one or two parameters. We'll talk again about what parameters are. So this is a pretty good starting point for learning calculation functions, as they all just produce the numeric results, and you may be familiar with some of these due to some of their mathematical properties anyways.

So let's open up our Exercise Files, and we are going to navigate to the Invoice Detail layout. To get us warmed up, we are first going to create a couple of mathematical equations that actually have a numerical result. They don't necessarily use numerical functions, but we're going to talk about results and numerical results in particular. So first, we've got a Field. If we go under File > Manage > Database, and it's a field called Sales Tax. Right now, it's a number field, but we are going to change it to a calculation. You can create calculations by either creating a field and choosing the Calculation as the type in the first place, or you can change existing fields over to Calculations, which isn't quite as common, but for our purposes, that's the example we are going to use.

So now I choose Calculation, and I am going to hit Change, and FileMaker just makes sure that I want to do that, because if I had any data in the Sales Tax field already, it's going to now override that data since we are creating a calculation. Calculations don't actually store their own data; they just take data from other sources and produce results. So I am going to hit OK, and here we see the Specify Calculation dialog window. Now, in this example, let's say the Sales Tax amount, it's going to be a pretty standard operation here. It's going to be the result of the InvoiceTotal multiplied by SalesTaxRate.

So that's a pretty familiar mathematical operation. It's going to involve a couple of different fields, and remember, we pick fields from the list here in the left-hand side, and remember, they act as placeholders for the data that's inside of those fields within each record. In order to get a field down into our formula entry space, we will select one from a list, and the first one we want to select is InvoiceSubtotal, and we are going to double-click on it. Now, we see that that field has appeared in the entry space, and what we want to do is multiply InvoiceSubtotal by a SalesTaxRate.

If you remember in our operators, we have the mathematical operators here, and the asterisk indicates a multiplier. Now, you could type this in on your keyboard, and after awhile, when you get familiar with these functions that's what you'll do. Now all we need to do is bring in the SalesTaxRate value, and we'll double-click on that. So this is a pretty simple mathematical operation, but what we'll notice down here below, is that for every calculation, it's going to create a result. That's the key; a calculation creates a result, and the result is what your users will see, and that's what gets displayed or printed or exported in the FileMaker database.

But just like other fields, a result has to have its own type. So we want to make sure that this result has a Number type because then we can then use the results of the sales tax calculation and yet another calculation, if we wanted to. Now, we are going to hit OK, and we see our Calculation underneath the Options and Comments column. Now, we hit OK, and we will see that we have a Sales Tax Rate, and I wouldn't want to live in this particular county, but now you see the sales tax calculated, and you'll see that it's different for each record because we've got a Subtotal times a Tax Rate, boy! Times are tough with these tax rates, aren't they? That's how we create a calculation with a numeric result.

Now, another quick example here. You'll notice that we've got a field called Invoice Total. Right now, there's no value inside the Invoice Total, but we are going to use a calculation to create this value. So let's go into File > Manage > Database, and select InvoiceTotal, and let's change that to a calculation as well, and we are going to do another quick mathematical operation. I'll hit OK, like we did in the last example and in this case, we are just going to do the same type of math; any kind of total is going to be Subtotal minus the InvoiceDiscount, plus the SalesTax that we just created, plus Shipping Costs.

Pretty standard stuff, but here, I am just going to show you a couple of the mathematical operators and again, the result is going to be Number. We hit OK again, and now we see each record; the important thing here is that each record has its own value. So the calculation has taking all the numbers in these fields and performing the mathematical operation per the instructions that we just gave it. Back in the Manage Databases window, what I am going to do now is show you the Calculation dialog and some of the different numeric functions. We are going to look at how each one of them uses fields as parameters.

The field that we are going to use is a field that's already created; it's a Number field that I've already created in the database here. So if we go and create a new field, called NumberCalc, let's make that a Calculation field, and we'll hit Create, you'll see that we've got an empty Specify Calculation dialog. The first thing I want to show you is over on the right-hand side; this is where all the functions live. So if we click on this dropdown menu, you see that they could be viewed by type, or you see the different categories. So we are going to focus on Number functions, and you see in almost every case of the number functions, they are all one, single parameter which says Number, which means put either a number or 90% of the time, you are actually going to put a field that's a field type of number.

A couple of them have two parameters, but most of them just have the one. Most of these number functions you won't run in to unless there are specialty circumstances, and some of them you might be familiar with just from your own experience with math. But there are some that are pretty popular, and I am going to show you a couple of those. For example, we've got Round. This is one of the mathematical functions with two parameters in it, and when you double-click on a function, it puts it down into your entry area. The other thing it does is it highlights the parameters, because again, FileMaker is reminding you you have to put something in there; it can't just run with these values on its own.

So, for example, we have a field that also happens to be called Number. We are going to double-click on that to put it inside of our function. Now, in the case of a Round function, what it requires is a numerical value, or a field that represents a numerical value, and then the precision that that needs to be rounded to. So if we want to round it to two places, for example, we would just put a number 2 in here, select the word "Precision," which is just an instruction or a placeholder, keep our Calculation result as Number, and let's hit OK.

So now you see we're back in our layout, but if we go into Layout mode, we are going to add our new field underneath the NumberCalc, and one way we can do this is hold down the Option key on Mac, or Ctrl key on Windows, and then release, and FileMaker will allow us to choose a new field. So in this case, we'll choose the NumberCalc. We'll go under Browse mode, saving our changes. So now we see we have an original value of 123.4567, but when we applied the number with the precision of just two decimal places, you see that it has rounded it to 123.46.

There's another function that would truncate this that would make it 123.45, but in this case, we've asked it to round. So it changes the 5 to a 6 based on the next value, which is a 6. Let's take a look at that truncate. If we go Manage Database > NumberCalc to Options, we're going to change it this time to another number function called Truncate, and we'll do the same thing here. Put the Number field in there, and of course, we have to put the Precision.

We'll say 2, hit OK, hit OK, and now we see a change, 123.45. So looking back in the Manage Database, you can see how creating a calculation that has a function in it and then applying that function to another field that contains a number gives different results depending on what function that you use. So those are just a couple of quick examples, but let me show you some others, and some that you can see, for example, in the NumberCalc are Floor because there's Floor and there's Ceiling; they are the opposites of each other.

Also, there's an Integer, which is somewhat similar. So if I choose Floor, what it's going to do - let me put Number in there and hit OK - is it's going to give me the next lowest integer. There's one called Ceiling that will give you the next highest integer, and then there's one called Integer, which is just going to extract the integer from your number. So there's a couple of different ways that you can use those. Now, in the case of the Integer function, let's try creating the calculation in a tad bit different way. For example, let's go back in the Manage Database, and again, we've got our number functions.

Some of them are pretty advanced. Now, certainly not within the scope of this entire training to expose you to every one of these Number functions, but I would strongly recommend that you go into the FileMaker Help, and just look under Calculation functions where you are going to find some very helpful tools, as you see here, to allow you to get familiar with FileMaker functions. This is probably the best guide that you can have. It's called the Function Reference, and here's a category list. You see you can go through every type of function that's available.

When you find one that you like, you can just click on it and get a good description. So that's a good "what's next?" for some training for you here. But back inside our exercise File > Manage > Database, we are going to use a calculation in a different way. This time we are going to choose the number Field, and we are going to hit Options. You'll see that we've got the Auto- Enter tab selected, and we talked about this in one of the early movies in this title. But if you look down here, you'll notice that we've got the Calculated value autoentry option with the Specify button.

So let's hit this button right there, and you see we get the same Specify Calculation dialog. In this case, we don't have to select what the number is because it already knows it needs to be a number because this is a Number field. So if you remember, in autoentry calculations, we're giving FileMaker instruction on what value to put into the field whenever the record is first created. With this trick I am going to show you, this is a way that we can take the data that the users have typed in and then apply a function to it before it actually gets committed to the database. So let's say we only want users to enter in raw integers.

We don't want any decimal places or anything; we just simply want the integer that they're entering. So instead of training them, and reminding them to do that; instead, what we can do is pick a number function - this one is just called Int or integer - and then in this case, we're going to reference ourselves here. We are the field; we're going to reference ourselves. So if you see here, we are doing Int(number) and this is in the field Number. So what that's going to do is say, instead of it being a placeholder for a field value, it's going to be a placeholder for the field value before it gets committed to the database.

So whatever the user types into this field, we are going to then apply the formula to it, and then it gets entered in. There's one other option that we need to check to make sure that that works. You see there's a default check box here, Do not replace existing value of field (if any). Well, that means don't replace the value in the field if the function is coming from another field reference. But in this case, we are referencing ourselves. So essentially, what this is saying in English is don't apply this formula to the value that I'm typing into the field when I create a record. That's not what we want to do here; we actually want it to apply the formula.

It's some strange language; FileMaker does have a couple of cryptic dialogs, but this one basically is, do you want to apply the formula that we just put in there against the value or not? In this case, we're going to uncheck it because we do want it to apply the formula. So now we hit OK and we hit OK again and now, if we go and create a new record, we are going to type a value in here, so you see we've got a value with some decimal places, but we have yet to commit the record.

So I am going to click outside the field to commit it, and now you see what it's done is it's actually applied our formula to the original field, and of course our number calculation is referencing that field, so it ends up with the same value. So in this movie, we focused on a couple of core number functions that are used most frequently, or that could be the most useful to you. So it's best to become adept with these functions without needing to refer to a reference as a source. So practice and apply these functions as often as you can, for example, as an autoenter data entry option.

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.