Join Cris Ippolite for an in-depth discussion in this video Use number functions, part of FileMaker Pro 15 Essential Training.
- [Voiceover] In this movie I'm going to introduce you to number functions which are used to manipulate and perform functions with numeric data. As a class, they're relatively easy to learn because most of them only have one or two parameters. I'll show you what those parameters are here in a second. Let's go into File, Manage, Database and we're going to create a new field called Number Calc. Choose the field type of Calculation, which is the only field type we haven't worked with yet and hit the Create button. This will pop up the specified calculation dialogue and allow us to create a number field calculation.
So we've already got a field that's called Number and it's the type of number as well. We'll take a look at what value it has inside of it but it's a large decimal value. So one of the examples that I can show you in the number functions is the round function. You'll see that as a class, there's a lot of mathematical functions here. Most of them just take one number field. That's what this word number is inside the parentheses. That's just a place holder to tell you what type of field you want to put in there so that you can actually get an output from the formula.
Take a look at round. It's actually one of the more complicated ones with two parameters. So what we need here are two things. A field that's a number field and to make it really easy I named the number field Number. Then the other parameter here is guiding us to tell us what we need to put in this formula. So the precision field is to what precision do we want to round the decimals in our number field. So let's say we want to round it to two positions.
Let's take a look at what that looks like on our layout. So first let's go into Layout mode and let's create another field. Drop it on the layout and we'll find the Number Calc field and we'll add that right above Number. So this is just for training purposes. We are showing the field that we're referencing inside of our formula right next to the formula itself. So we picked round and do so by two positions. So what the round function actually does is exactly what it says.
It rounds it and you'll notice that it's not just truncating it because otherwise it would be .45. Instead it's rounding up to the next value after it establishes the two positions. There is in fact a number function called Truncate. So if we change this to the truncate function and we go back and take a look at our formula, we see that it does in fact just chop it off after five instead of rounding to the next value at the hundredth position.
You'll also notice that all I have to do is free form type in here. So if I already know what the formats are of my functions, and in the case of number functions it's pretty easy. Just remember what the name of the function is and then you can just type the parentheses and the values inside of it yourself. There's a couple other ones here like Floor, for example. I can just choose the Floor function. It's easier if I have a clear calculation pane. So if I hit Floor and I put in the number field, what Floor does is find the next lowest integer, so 123.
There's a ceiling function that finds the next highest integer which would be 124. Then there's an integer function which just gives me the integer, which would be just 123 without any of the decimal values behind it. So you can see the number functions really just kind of do things to existing numbers. So they're not hugely compelling but they can help you carve out and reformat numbers when you need to. So where's an area that we might be able to use these formulas besides a calculation field? Let's go into the Manage, Database, and Fields list, and select the Number field and hit Options.
If you remember from early on in this course, we talked about field options and you remember that Auto-Enter instructions tell FileMaker what to create inside of a field when it's creating a record for the first time. We did not talk about calculated value at that time, but let's do that now. We can invoke the Calculation dialogue by hitting the Specify button and frankly, anywhere you see the Specify button in any one of the environments you know that that will invoke a Calculation dialogue. So here's the Calculation dialogue. What's interesting about Auto-Enter is we could have it automatically enter a value into the number field based on a calculation formula, but I'm going to show you a really interesting trick here which kind of filters the values that users enter into a field.
So let's say for example, we pick Number, and we have a rule here. Let's say our users can only enter in integers and we cannot accept any decimal values. We keep trying to tell our users stop entering decimals in here, but they keep putting decimal values in here. So we want to create an Auto-Enter filter that will strip anything out but the integer. So I'm going to select the integer. I'm going to put the number field in there and that's as simple as the formula goes. So here's the interesting thing. Notice this sort of cryptic check box here, Do not replace existing value if any.
Well really what that actually means is allow FileMaker to apply the calculation field if the field has a value in it and in this case we want to do that. So what we're actually going to do is uncheck it. So what we're doing is something kind of interesting here. So what you may not already know is that when a user first enters a value into a field, it sort of lives in that field but is not yet saved into the database. Then when the user commits a record, it goes through a process where it has to pass through field validation and maybe in the case of like a date field, FileMaker has to check it for a second to make sure it's got a four-digit date.
If it passes all the rules, then it gets saved into the database, but what's interesting is if we use this technique here where we put in a calculation formula, where we are referencing the field that we're actually defining it in, you'll notice these are the field options for the field number, what we're actually saying is take whatever the value is that the user types into the field before it's commited, then run this formula against it, and then take the resulting formula and save that to the database. Then that's what the user will see after they commit the record.
Let's take a look at this in practice so you can get a better idea. So here's the number field. Let's say we go to a new record. All of them have the same record so if I go in here and I type in 1234.56789. Now right now, I'm just entering data into the field. I have committed the value. So the FileMaker has not gone through to do validation checks or in this case, FileMaker has yet to run it through the calculation formula that we gave it before it commits. Let's look at what happens when we commit.
I'm simply clicking anywhere on the field and what that's doing is telling me what the value is supposed to be now. So what it did is it stripped out anything but the integer. So this is just FileMaker with number functions in an Auto-Enter calc but you can use Auto-Enter calcs in various different areas throughout the application. We'll look at a couple of those other ones in upcoming movies.
- Understanding the FileMaker Pro interface
- Creating new FileMaker apps from spreadsheets
- Building tables and relationships
- Defining primary and foreign keys
- Working with fields
- Creating, editing, and deleting records
- Importing and exporting data in a FileMaker Pro database
- Creating and formatting layouts
- Finding data and sorting data
- Printing from FileMaker
- Building reports
- Using calculations
- Triggering scripts