GoHogs Posted July 28, 2007 Share Posted July 28, 2007 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 More sharing options...
mr_vodka Posted July 28, 2007 Share Posted July 28, 2007 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 More sharing options...
GoHogs Posted July 28, 2007 Author Share Posted July 28, 2007 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 More sharing options...
LaRetta Posted July 28, 2007 Share Posted July 28, 2007 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 More sharing options...
comment Posted July 28, 2007 Share Posted July 28, 2007 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 More sharing options...
GoHogs Posted July 30, 2007 Author Share Posted July 30, 2007 I will give these a shot. Thanks all. Link to comment Share on other sites More sharing options...
GoHogs Posted July 30, 2007 Author Share Posted July 30, 2007 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 More sharing options...
GoHogs Posted July 30, 2007 Author Share Posted July 30, 2007 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 More sharing options...
GoHogs Posted July 30, 2007 Author Share Posted July 30, 2007 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? Link to comment Share on other sites More sharing options...
Fenton Posted July 30, 2007 Share Posted July 30, 2007 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 More sharing options...
comment Posted July 30, 2007 Share Posted July 30, 2007 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 More sharing options...
GoHogs Posted July 31, 2007 Author Share Posted July 31, 2007 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 More sharing options...
comment Posted July 31, 2007 Share Posted July 31, 2007 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. Link to comment Share on other sites More sharing options...
GoHogs Posted July 31, 2007 Author Share Posted July 31, 2007 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! Link to comment Share on other sites More sharing options...
Fenton Posted July 31, 2007 Share Posted July 31, 2007 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 More sharing options...
GoHogs Posted July 31, 2007 Author Share Posted July 31, 2007 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 More sharing options...
Recommended Posts
This topic is 6241 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 accountSign in
Already have an account? Sign in here.
Sign In Now