Jump to content

Sorting Sub-Summary fields?


GoHogs

This topic is 6168 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6168 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.