Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
One of the most powerful and useful capabilities of any database system is its capacity to output, or report, on the data that it's storing. And if you look at this List View that we have here, the Invoice List View in our Exercise File, you see that it's a list of 146 different Invoices. Now certainly by creating different found sets, we can create compelling report- like Outputs in our database. Now this is great; the information looks just like it does when the user has entered it into the system however, but with one simple change we can turn this List View into a Report.
So, for example, if we go into Layout mode, you'll notice that this Layout has a footer part on it, and by just entering one special type of field into this footer, I can make this List View a report. The type of field I'm discussing is called a Summary Field, and this is the only type of field that we haven't yet discussed in this title. So let's take a moment to define what a Summary Field is. If you open File > Manage > Database, you'll see that the very last option is something called the Summary.
Unlike the other types of fields we've already discussed - the Text, Number, Date, Time, Timestamp, Container and Calculation - the Summary Field is unique in that it isn't a field that contains data, but it's rather a field that aggregates data across a found set. The value in a Summary Field will be different based on the Records in a user's current found set. So unlike a Text Fields, or any of these other types of fields which will just display the data for a specific Record, a Summary Field is going to give us a total value based on a found set of Records.
So let's create a Summary Field to see one in action. We're going to type into the Field Name, we're going to call it TotalSummary, and we're going to choose the Type Summary, and we're going to hit Create. Now you'll see the Option for the Summary Field dialog box gives us some interesting choices. You see here we've got a bunch of aggregate options, since what we're doing is aggregating against a current found set. We can do things like Total a series of values across the Found Set, Average, Count, provide Minimums, Maximums and so on. So you'll see what we have here are available fields, but you should note that some of the fields are grayed out, making them unavailable.
Depending on which aggregate option we choose, we're going to have different fields available to us. So, for example, Total of allows us to total Date or Number fields; most commonly you're going to be using Number Fields. Average only allows us to choose Number Fields and Count allows us any field, because what it's going to do is literally count every record in the found set. Minimum and Maximums work on Numbers or Date fields. In our case we're going to choose Total of, and we're going to choose Invoice Total.
So what we're instructing this field to do is to determine all of the records in a current found set and then go into the InvoiceTotal fields and all those records in the found set and provided a total of all the InvoiceTotals. When we hit OK, we can see these field in action. Now you see that a field has been added to the bottom of my layout. Your preferences may be different. If so, you can go under the Insert > Field option and drop the TotalSummary on the layout. What I'm going to do is I'm going to Shift+Select both of these values, and I'm going to drag them just under the InvoiceTotal.
I'm going to reset the size of my body part. And while I've got the field selected, since I'm already in Layout mode, I'm also going to go into the Data tab of the Inspector. And I'm going to choose Currency, because I happen to know that this field is going to contain currency values. We'll choose a fixed number of digits, Currency dollars and thousands separator, and then maybe make it just a tad bit longer. So let's go into Browse mode and take a look at how this works. Save our changes. So now if you look at the value on the bottom of the layout, it gives us $418,390.60.
What that is is the total of every Invoice across the 146-record found set. What if I went in and I chose only Shipped records by using our fast match in the contextual menu? Now you see we only have 19 Records, and our total has changed to $57,266.75. So you can see here evidence that every time we change our current found set, this total will change. So this is a great way to create a very simple report, because now you can feed it all sorts of different found sets, and all the while it's going to give you different totals.
So this simple List View has now just changed into a simple report.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91127 Viewers
80 Video lessons · 138165 Viewers
59 Video lessons · 56980 Viewers
52 Video lessons · 70615 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.