Sorting Sub-Summary fields?

[GoHogs]

Is there anyway to work with summary fields and/or parts without going into preview mode? I made up a small example of what I am trying to accomplish. I have figured out how to add the total sales of each rep and figured out how to sort those totals, but how do I display just the rep name and total sales without going into preview? The db I am actually working on has dozens of these totals and I would like the information to display on screen in a table so the end user can sort easily.

I know just enough about FM to know I have no idea what I am doing. Am I just missing something right in front of my face?

Thanks in advance!!!!

SortBySummary.fp7.zip

[mr_vodka]

You can try using get summary GetSummary ( Total Sold; Name ) or you could have the sales reps in their own table, have the sales figures in a related table, and then sum up the related sales records.

[GoHogs]

I cant seem to get the Get(summary) to work, but the different tables idea might be the ticket. It will be a lot of work to set up, but miht make it easier in the long run.

Thanks for this and future thanks for any other ideas anybody comes up with.

[LaRetta]

You don't need preview mode. You can delete the Trailing sub-summary entirely. Move the summary total up into the body. Sort by sales rep and display it in Browse mode.

[comment]

It will be a lot of work to set up

You have done it already. Just rename your 'Sort by Summary' table to 'People' and change the calculation field 'Total Sold' to =

Sum ( Data::Amount )

There are a few other changes you should consider, such as relating on a person's ID (auto-entered serial number) instead of name, but the above should be enough for your request.

[GoHogs]

I will give these a shot.

Thanks all.

[GoHogs]

You don't need preview mode. You can delete the Trailing sub-summary entirely. Move the summary total up into the body. Sort by sales rep and display it in Browse mode.

But then I get all the instances of each sale date, when all I want is a display of name and total sales. Which is why I was trying to use a related table.

And failing obviously!

[GoHogs]

You have done it already. Just rename your 'Sort by Summary' table to 'People' and change the calculation field 'Total Sold' to =

Sum ( Data::Amount )

This is it, it's working. You guys are the bomb. It's so simple and I just shot right past it looking for something I thought would be complex.

Thanks again.

[GoHogs]

One more thing, if anybody is still out there, that sum(data:amount) counts ALL the records in the data table. What if you just want it to count a found set of records?

[Fenton]

In my opinion, if you want to do a found set then it's easier to just use a regular Summary field, in a Subsummary part in Preview mode. If you create a new window for this, and a dedicated layout, it will not require any changes to the Browse list view. Then it becomes more a matter of opening the window, pausing it for viewing, asking if they want to print, then closing the window.

Yes, you could do it in Browse mode, by capturing the IDs, using that for the relationship, along with the SalespersonID. But it would be more difficult. It would also need to be scripted.

[comment]

sum(data:amount) counts ALL the records in the data table.

No, it counts all RELATED records in the data table. So another option you have here is to filter the relationship (or, preferably, a new relationship to another occurrence of the data table) by some additional criteria - the same criteria you would use to create the found set.

[GoHogs]

But if they are related by name, it would be all the record in the data table. Right?

I would think being able to sort summaries of a found set would be a simple thing to accomplish, but I guess not.

[comment]

But if they are related by name, it would be all the record in the data table. Right?

No, just the records with a matching name.

[GoHogs]

So, maybe if the script that does the find auto generates a key field that relates to the "people" table, thus just those records would be used. Maybe that's my path to enlightenment!

[Fenton]

That's what I meant by "capturing the IDs" of the found set, into a multi-line text field, with global storage. This global field can be in any table, as the originating point of the relationship. In FileMaker there's various ways to do that, Loop, Custom Function, or the Copy All Records step.

I usually use the latter method, because it is the fastest; though it does require a couple layouts, and it wipes out the clipboard contents unless you save then restore it; a recent post by comment (I think)recommends using a global container field for this, accepts plain text also.

Once you have the IDs, in a global field, a relationship using that as one of its criteria will limit the matches to the found set. Add another criteria for whatever else, the person's name in this case, to get a subset of the found set.

It would be faster, though less flexible, to incorporate whatever your Find is into the relationship itself. For a date range use 2 global date fields, start to finish, up in the Header part. May require a Refresh Windows [x] Flush cache join results to redraw the totals on the screen properly; it depends on how complex the relational calculations. Buttons for "This Week", "This Month", "This Year" can be used.

[GoHogs]

That's what I meant by "capturing the IDs" of the found set, into a multi-line text field, with global storage. This global field can be in any table, as the originating point of the relationship. In FileMaker there's various ways to do that, Loop, Custom Function, or the Copy All Records step.

Yes, yes I see. I finally "get" it. Fantastic. I never really understood relational tables before and it just hit me. Now all the things you folks were saying before make sense.

Thanks so much for your patience! And knowledge! This opens a whole new FM world for me.