From the course: FileMaker: Tips, Tricks, and Techniques

Subsummary ranking reports - FileMaker Pro Tutorial

From the course: FileMaker: Tips, Tricks, and Techniques

Start my 1-month free trial

Subsummary ranking reports

“

- [Instructor] This technique is going to build upon an understanding of sub-summary reports in FileMaker. If that's something you're unfamiliar with I urge you to go check out the learning FileMaker 17 course and check out the reporting chapter first but if you already understand sub-summaries I think you'll find this technique to be pretty interesting. So what we're going to do here is go into Layout Mode first and kind of take a look at how this layout is set up. If we go under Layouts to Part Setup we see that we've got Header, Footer and Body but we have a Sub-summary part already in place and the break field has been established as the company so that's why when we're in Browse Mode we see this list broken up by company. Also we happen to have a Script Trigger set up that will sort based on the break field so that the user doesn't have to do that. Now let's say though, that instead of showing the company and then the invoice details and then their summary let's say instead we just want to show the company and their summary when we can do it in the form of a ranking report for example. Well let me show you a couple quick steps that'll make that possible. First we're going to go into Layout Mode and we're going to duplicate this layout so that we can preserve the original one and we'll call this one Invoices Subsummary Ranking since that's what this technique is referred to as and we're going to do something kind of radical here. I'm going to go into the body part since we don't need that in a ranking and I'm just going to delete it. Then you see now we have two sub-summaries they're really intended to bookend all the list of invoices in the body but I'm going to take this grand summary and move it up onto the same sub-summary part as the company name. I'm going to now delete the second sub-summary and make a couple quick formatting changes here. And let's go into Browse Mode and see what we get. So now you see we have a list of all of our customers and if we sort by all of them, we see that what it's doing is showing just the customer and their grand total. It's kind of interesting because the context of this layout is actually invoices even though we're looking at a list of customers. But here's the thing, it's not truly doing a ranking yet even though it's giving us the total of all their invoices in the found set, by the way you notice that before when I had done a search of only five records that had a shorter list, it's still based on found set which makes it really compelling and that way your users can create their own found set navigate here. So how do we get the ranking? Well it's really about the sort. If you go into the Sort you notice that you've got the break field that we're sorting by here and previously we had been sorting by the date as a secondary sort, we don't need that because we're not even showing the invoice here so really we just keep the break field. Now the deal is, we really want to sort by these values but we can't do that because the rule behind sub-summaries is that you have to keep the break field as the primary sort but if you look down here we've got this option to reorder these values based on a summary field. So if I click that option we see that the specify field list shows up, pretty much everything's grayed out except for anything that's a summary field. And as it turns out that's exactly the one that we're looking for here because that's the field that we see in that list. So since we want it to be ranking meaning the highest number on top and the lowest number on the bottom, I will make this descending so we keep the break field in place we reorder based on the summary field that's showing on the layout and then we show them in descending order. Now we hit Sort and sure enough we've got our ranking list. Then last thing we really have to do here is because there's a Script Trigger assigned to this layout that is doing the sorting, I'm going to do a quick on the fly edit and I can change that sort order here as well. We're really just doing the same exact thing so that this is automated and now whenever a user navigates to this layout they're going to actually see the ranking in the correct position. Of course we could have duplicated the original and created a new one so we didn't disrupt the previous script, but either way this is going to work now for any user that creates a found set and then navigates over to this layout so that they can see the ranking report.

Contents