Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The first step in understanding calculations in FileMaker is getting familiar with the Calculation dialog window. The Calculation dialog appears in a wide variety of places within FileMaker Pro, and it's really one of the primary areas where actual programming comes into play. So in this movie I am going to show you the mechanics of the dialog, so later you'll have the understanding needed to write some initial formulas. The most common place that we'll find the Calculation dialog is under the Manage > Database window. And if we create a field, let's say we just call one Calc, and choose the field type Calculation, after hitting the Create button, we'll see the Specify Calculation window.
So let's take a quick tour of this window, so you'll know what all the elements are onscreen. The window in the upper left-hand corner allows you to view fields from your database. This is important because nearly every calculation is going to use field data, so from here you can choose fields from the current table, as you see selected, or from any other related tables. You can also mix those fields together within your formula. Remember that you are just using field names as placeholders, but when they are evaluated, FileMaker Pro will put the values in those fields and then evaluate the values record by record.
So really what you're picking are placeholders. In addition to placeholder fields, you can also choose from a wide variety of operators, as well. That's what all these items are in the middle. Just to give you an idea what these are, hitting just a button will place one of these characters into your Calculation window. And the ampersand in FileMaker allows you to concatenate, or group, two different fields or two different functions together. So, for example, if we want to create a simple formula, let's get rid of that ampersand, double-click on one of our fields, and if we wanted to have first name and last name together to create a full name value, we could take the field name for first name, concatenate that, and then we'll use our second button.
This button allows us to put literal text, or in this case, by hitting our Spacebar allows us to put a space in between two fields. But any time you're connecting a field value and text values, you need to include an ampersand in the middle to connect them. So the ampersand is used to connect different values. So this is an example of our first Calculation formula that uses both the ampersand and the quotes. This next button inserts a Return character, which is popular inside Text functions.
And on the right-hand side, you see all the mathematical functions. The top button is used for Division, and the reason that we have a forward slash inside of our formulas is because you don't have a divided-by sign on your keyboard. Below it, we have an asterisk, which stands for a multiplier within FileMaker Pro Calculations, and the reason for that is because if we used an X, FileMaker would be confused as to whether or not that's actually the text for an X or a Multiplier. You also see operators that include statements, like equals, meaning does this field equal that field or not equals, greater than, less than and so on.
And on the very right-hand side is a very important window. This is where we list all of the Calculation functions that FileMaker can interpret. We will be talking about these functions in detail in the upcoming movies, but this is where you can find each one. You'll notice, as you select and double -click on any one of these functions, they'll appear down in your Calculation writing area below. Clearing those out, choosing another one will drop not only the function into our formula entry space, but it gives you words in a certain format.
These words, like you see here, field, are what are called parameters. They act as placeholders for a field, or a value that matches that type of data. So FileMaker is giving us instructions here to say if you're going to use this Calculation function, you need to put a field here. Or, for example, here, if you're going to use this function, you need to put a date field here, specifically. Every function will have at least one parameter, and you can see why FileMaker warns you if you try to name a field with the same name as a function, or as a number or a character, because it can get confused as to whether or not you meant a field name or a function that it evaluates.
Imagine if you had a field called ampersand, forward slash, asterisk. That might be very confusing for FileMaker in the formula entry space. The formula entry space is where you're going to author all your FileMaker formulas. And the other pieces that you'll need to be familiar with, on the bottom of the Calculation dialog, are the result options. So here, you'll say that a calculation we'll need to results in either a Number, Text, Date, Time, Timestamp or Container. These are all different types of fields. Even though the type of this field is Calculation, its result has to be another specific type, so that FileMaker knows how to handle it while it's storing that information in the database.
So given the wide range of uses for calculation formulas within FileMaker Pro, establishing a solid familiarity with the Calculation dialog, as well as the function parameters themselves, is essential in becoming a professional FileMaker developer.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97505 Viewers
80 Video lessons · 141050 Viewers
59 Video lessons · 59400 Viewers
52 Video lessons · 72723 Viewers
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.