Join Cris Ippolite for an in-depth discussion in this video Using related fields in calculations, part of FileMaker Pro 11 Essential Training.
In this chapter, we've been looking at many different ways to use the relationships that we've set up in our FileMaker database to display different types of related data, either in the context of a parent or a child table. There are also some other things that you can do with related fields, including using the calculation engine. In this example, what I'm going to do is I'm going to create a calculation field that's going to give us the grand total of all of the related invoices to any given customer. So, it's going to total up all these values, for example, or that value.
This is going to be a dynamic piece of data. So, every time a new invoice is added, it's going to automatically update. So, this is going to be a little bit different than the concept of borrowing a field from a related table or using a portal. In this case, what we're going to do is create a calculation field that uses a new set of calculation functions I want to introduce you to. So, let's go into File > Manage > Database and inside the Customers table let's create a new field that's called InvoiceTotal.
Let's make it a Calculation. When we hit Create, we're going to see the Specify Calculation window. Now I'd like to introduce you to a couple of functions that fall under the Aggregate functions group. These are pretty simple. Although you have these optional parameters in each one, really it's just a function and then you put a field inside of it. So, for example, you see things like Average and Count and Max and Min and Sum. So, what you would do in the case of Sum, for example, is you could put a list of fields in here, all delimited by commas, and it's going to total up the numeric values inside the fields. Or I could just put 1, 2, 3, or 1, that kind of thing, and it will give me a total.
But forget those uses. The real magic behind an aggregate is when you use a related value, specifically a value that comes from a child table. So, here we are in the Customers table, in the context of the Customers table. By the way, you can see the context here, just to remind you. What we're going to do instead is reach down into the Invoices table, which is the child, and we're going to grab one field, InvoiceTotal. So, now what this does, anytime that you put a single related field in as your parameter inside of an aggregate, what it's actually going to do is go and find all related records and give you the sum of just the values inside of InvoiceTotal.
So, let's see how this works. So, we've got Sum (Invoices:: InvoiceTotal), Calculation result is a Number. So, let's say OK, OK, and you see we've got our fields here. But let's go into Layout mode for a second and Shift+Click on these values. Put them over here. We can click on this little line here to shrink our body back up to what it was before. If the fields weren't automatically added to your layout, then you have different preference settings. You can then go into Insert > Field, and choose the field we just created.
I'm going to pick this field, then I'm going to go in and give it some currency formatting, widening it out a little bit. I notice here that my portal is based on Invoices. So, that's going to help me view all of the invoices that we're going to be summarizing here. I see it's got a sort value applied to it, but I also see it's got a filter. So, let me double-click on this, turn that filter off, because we want to see all of the invoices, because that's what's going to be reflected in this total.
So, now let's go into Browse mode and take a look at what we've got. Hit Save, and now we see what we've got is the total of all of the related invoices. If we go over to the next record, we see yup, $49,737, $9037, giving us $58,775. So, you see it's updating for every record that we go to, but then it's also updating every time a new invoice is created, whether by you in another layout or by another user if this is a shared file.
Let me show you another one since these are so quick and easy to create. We've got InvoiceTotal. Let's hit Duplicate and let's delete this name and say invoice average instead, and we'll say Change. We see a change. We say Options and instead of Sum, you see there's another Aggregate function called Average. It's got the same format. So, all I have to do is change this name to say Average. Keep everything the same and I say OK.
Now we go View > Layout Mode. I'm going to Shift+Select these two fields, and actually if I don't want to have to format them again, I can just delete them. Instead, I'll grab this one, holding down the Option key on Mac, pick our new one, grab a title. Now, I don't have to go and apply this format into it. Now, we go in Browse mode, save our changes, and now we see the average. We see here this is what the average order is.
So, very simple and easy fields to create. I'll show you. There is a couple of more. We've got Average, Total, how about we'll create a Max, meaning the high end, duplicate that. Create a minimum, meaning the low end. Let's go and update these. So, you see Aggregate functions. We've got a Max and a Min, and what the Max does is it tells me out of all the related invoices, what's the highest total that I've got, the maximum value.
Since this is a number field that we're referencing, it's going to be the highest number, the maximum number. That was our Min. We're going to do Min in that case. See how easy it is to change? You can see I'm just typing in the name of the function. I could double-click on it, but it saves me a couple of keystrokes there. Here, I'm going to change Average to Max, OK, OK. We go back into Layout mode. We'll get rid of these again. Hitting the Delete key, and in this case, we're going to hold down the Option key on my Mac and hold down the Option key on my Mac again.
Now, we're going to go back into Browse mode, saving our changes, and now we've got all sorts of really cool information, all using related values from our portal. And it updates from record to record. Using related fields in your calculations, especially Aggregate function calculations, when you're in the context of a parent can provide some very useful dynamic values with very little overhead.
- 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
Skill Level Beginner
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.