Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Business and personalized recommendations.
Start Your Free Trial Now Overview
 Transcript
 View Offline
Released
6/25/2010 Creating databases from templates
 Creating fields in spreadsheet format
 Creating tables and relationships
 Defining key fields
 Adding validation and autoenter 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
Skill Level Beginner
Duration
Views


















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 lefthand 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 doubleclick 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 doubleclick 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 righthand 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 doubleclick 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 doubleclick 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.




Public Link
Video: Using number functions