Summary total per page?

[MacHammer]

Hi everyone,

The boss just asked me to add a page total to one of my layouts and I said, "Sure Boss, no problem..." Then I dug into it and couldn't find an easy way to make it happen... :

So, what I'm trying to do is total a column of numbers and display that total at the bottom of each page. I tried a sub-summary, but that places the total after a group of like records and not at the end of the page. I can't find any easy way of doing it...

Please help! :

Mac Hammer

[Søren Dyhr]

What is steering one record to a specific page structurally?

--sd

[MacHammer]

Sorry. Not sure I understand the question entirely, but I'll try it this way: the layout is a list view, where there is an item count (Quantity) as part of each record. We would like a total of these quantities (30 per page) placed at the bottom of each page. I tried the sub-summary part to try to add a quantity summary at the bottom, but it only adds a total at the each of each sorted section. We want a total per page, not section break.

I hope that helps explain it. I'll try again if you need more clarification. Thanks.

Mac

[Mikhail Edoshin]

Check whether this helps: http://fmforums.com/forum/showtopic.php?tid/178962/post/215890/hl//

Edited October 3, 2006 by Guest
(subscribed to updates)

[Søren Dyhr]

While I still am in doubt where it could be used at all, have I made you a template. Only thing I could think of is in accounting where you as a check to prevent typos are summing all the account numbers in the ledger ...so you can single out pages where the type must exists if this nonsense-sum doesn't add up correctly.

The method I've chosen to use is to make a multicriteria global primarykey for a GTRR, in order to make found sets for the Summary field.

--sd

Untitled.zip

[MacHammer]

Soren,

Thank you. While I can see that your idea works, and I've tried to understand the calculations and fields you created, I'm not versed in "multicriteria global primarykey for a GTRR". :

Like so many FMP projects, the nature of "why" we need it is only relevant to our business. We have a need. We want FMP to figure it out for us. The data is there. It should be fairly easy to add it up and display a total. I was actually a bit shocked when I went to simply "do it" and couldn't find a built-in command to make it happen. It is a very common task in spreadsheets, and what are list view reports, if not spreadsheets with the added capabilities and powers of a database? But your guess would be a perfectly valid reason for wanting this feature in a db. Ours is more related to keeping track of paper counts in a warehouse. Again, the main reason I want to do it is "the boss asked me to do it." :

I'm sincerely grateful for your help and appreciate that you took the time to create a model db for me. I might have to change my profile from "intermediate" to "beginner" though, as I'm still trying to figure it out! :

Thank you.

Mac Hammer

[Søren Dyhr]

Like so many FMP projects, the nature of "why" we need it is only relevant to our business

I humbly dissagree:

http://www.systems-thinking.org/dikw/dikw.htm

We (europeans) have had our fair share of what blind obedience can lead to ...Treblinka, Auschwitz, Birkenau, Saxenhausen and Bergan-Belsen.

What seems to be ignored back then was:

http://www.mind-revolution.com/forums/showthread.php?p=292

--sd

[comment]

If the number of records per page is known and constant, then it's very easy (as shown by Mikhail). If not, then it's quite complex, and VERY slow to boot (takes about a minute for 500 records).

Most multi-page reports I have seen are not concerned with page totals. After all, which record is on which page is largely a matter of chance. A running total, with perhaps a balance from previous page shown in the header, should be quite sufficient IMHO (but then I am not your boss...).

[MacHammer]

Soren,

(Read with Tongue planted firmly in-cheek)

Believe me. If the boss starts acting genocidal or homicidal, or any other "cidal" and wants me to track it in FMP, I'll be the first to put an end to it! :

As long as we are simply keeping track of paper quantities per page, I think I can live with the request.

Mac

And Comment, I think your link and idea will probably work. Actually, after I added the sum-summary body part and the calculation, the boss said that having it there would work. So, I might just leave it at that. But the information I've gained will be put to use some how! :

Thank you all for the help and examples. You've been great.

Mac

[Søren Dyhr]

Most multi-page reports I have seen are not concerned with page totals.

This is exactly why I "why'ed"! Since databases by some are considered vessels of meaning, is summing at an arbitrarily breakpoint regardless of the sortorder of the data is the desired slicing of data weird - and seems to reflect vanity more than actual use.

--sd

[MacHammer]

Because of the way the file is processed before being injested into FMP, the page totals are meaningful. We warehouse a paper product. The requests we get are "pick lists" for that product and break the totals down into how many items are to be "picked" from each item location. The quantity to be picked varies greatly, thereby making one "page" of picks range anywhere from 400 to 4000 items to be picked. If we hand a random page to a picker and then expect him to have it done in 20 minutes when it is a 4000 pick page, it becomes important to be able to recognize immediately that this guy is going to need help to pick this page and we can re-slice the page to share it with another picker. Having the data report that there were 15,000 total items in one grouping, doesn't help us split up the pick. Only a page total will do that. And the sort order is broken down to keep the items in groupings by location, so we don't have pickers walking from one end of the warehouse to the other from pick to pick to pick. I think that is the best I can describe it without breaking NDA's.

Mac

[comment]

It seems that what you call 'page' is not an actual piece of paper, but rather a 'group' or a 'category'. And it is not determined by paper size, but by you. If so, that is an entirely different animal.

[Søren Dyhr]

Alright, but wouldn't it be better to see at the number of pickers and the time availiable using the avarage to pick 4000 each 20 minutes.

This number can be found by GetSummary( on a running total, scripted or by a recursive CF getting the exact recordID where to split, so each picker get the right sized list to pick from, there what you do is a little trial an error to give equal shares by omitting a record here and there???

--sd

[MacHammer]

No, a page is exactly that. One piece of paper with pick data on it. Need total to print at the bottom of the page that is just a sum of all the quantities from the fields above. Sounds simple, doesn't it? :

Mac

[comment]

No it doesn't sound simple at all, and it isn't. Page is a meaningless concept in a relational database - until the moment it's time to print. Filemaker doesn't know which records you will choose to print, on which layout. It doesn't know if there will be records with more data than others, if there will be sliding required, and so on. The paginating is done on the fly. There is no assigning of records to page numbers. In fact, a single record can be on two or more pages. To which page is the amount supposed to be added in such case?

[Søren Dyhr]

a meaningless concept in a relational database

Indeed; this is the conditions we won't pledge allegience to : Databases is founded on group teory and predicate logic!

--sd

[MacHammer]

Before I say this, I want to re-iterate that I appreciate all of your help. That said, before this disintegrates into a discussion of why or why not this is a meaningful process and trying to talk me out of doing what I've been asked to do, the point is, I've been asked to do it. I've asked for assistance in pulling it off because I had problems finding a way to do so. Collectively, you all have provided possible solutions to how it can be done. I'm examining these and tonight when I get some time, will attempt to see if I can make one of them work. But telling me that this idea is crazy or that relational databases don't care about page numbers is wasting all of our time.

FMP is the popular solution that all of us have made it because it doesn't try to tell you HOW to do something. It provides a ton of great tools to let you do what you want to do. That's all I'm trying to do. We have a problem and Filemaker is the solution. All I've asked for is how to do it. Help or don't help, but let's please try to stick to the solution and quit trying to sell me on why I shouldn't want to do what I've been asked to do. It isn't a bit helpful to finding a solution.

Thank you all. My intention here is not to upset anyone, but I'm spending a lot of time reading these posts, many of which are now just telling me not to do something because it doesn't meet someone else's standard of "normal use."

Mac

[comment]

I was merely trying to explain WHY this can be difficult - IF the number of records per page is not constant (see my first post in the thread). You never said if it is or isn't. If it is, you have had the HOW provided by Mikhail for some time now. I was sure by now you were busy implementing it, and that we are merely shooting the breeze here.

Knowing why someone wants to do something is often leading to better advice. Usually, when someone asks how to swim across a river, they're glad to be pointed to a bridge. Your boss may be a good reason for YOU to insist on swimming - but he's not MY boss, so...

[Brudderman]

Though I haven't had the need to do it recently, years ago I used a "brute force" method that went something like this:

1. Add a field to each record called PageNumber

2. In a script view the report in preview mode, go to the last record, get the page count and put that in the PageNumber field.

3. Omit the last record and repeat the process of getting page count and putting that in the PageNumber field until every record has a number in the PageNumber field.

4. Now create a report with subsummaries based on the PageNumber field.

This worked OK for a small number of pages but would probably be quite slow for a very lengthy report.

James

www.james-mc.com

Edited October 20, 2006 by Guest

[comment]

I remember that thread from CompuServe. I don't think there's another method to do it, even in the current version. One thing should be noted: if you have a trailing grand summary, you need to run the script on a duplicate layout, with the trailing grand summary removed - otherwise there will be an extra page when the last record is at the bottom of the page.

[Brudderman]

Yes, I think that's where I first picked it up. And though I haven't had the trailing grand summary problem, I see where it would be troublesome. Thanks for pointing that out.

[watsj]

Hey

I think I found a solution to your problem. I had the exact same request made to me by an employer & thought yeah, FM can do anything. Well here is the secret. You have to define how many occurrences per page.

Add a field to designate the page that the record will be found upon. (Page Number for ex.).

When defining parts, tell FM (in the body section) to make a page break after so many records - in my DB I had six per page.

At the end you will need a Summary Part and also a sub-Summary. Your Summary Field will go into both these parts. When you define the sub-Summary, FM will ask you under what sort conditions it should appear. Define this as the Page Number field

You figure out which pages the records occur on by simply by dividing the Record Number by the whatever number of records you choose to appear per page (in my case 6).

There is a little trick tho: You've got to slip in an empty record onto the first page, Otherwise you will be missing one record on your first page and when you sort that will throw the entire report out of whack by one record.

Hope this helps.

[watsj]

Earlier in this discussion it was mentioned that having the summary from the previous page at the top of the page would also be useful. I thought so too.

I did it by carefully measuring the number of records that would fit onto one page which is necessary for the procedure that I posted before. The cool thing is to extend the sub-summary to be a little longer than able to fit on a page, add your Summary Field at the top and bottom of this Part, check the option to have this section extend to the next page... Voila the second summary field which contains data from the that page is extended to the top of the next.

The key tho, is careful measurement! Millimeters count, because as you add pages - if the layout parts dont precisely fit, those extra mms will creep onto the later pages. This shifts the sub-summary section and eventually it ends up in the middle of the page.

[comment]

Earlier in this discussion it was also mentioned that if the number of records per page is constant, then the problem is rather trivial (see the link in Mikhail Edoshin's post).

Placing the balance from previous page in the header is even easier, and it does NOT require a constant number of records per page - see here.

[Colin Keefe]

Just a bump as a reminder why I love FMForums, and to emphasize how searching here can dig up old nuggets.

I've been racking my brains on how to get subsummaries based on page number into a sliding report, and this just unstuck the logjam in my head. I knew I need the page numbers stored locally, but I would never have thought to recursively omit the records to get the actual value.

That's it, I'm hanging up my hat for the day.

Though I haven't had the need to do it recently, years ago I used a "brute force" method that went something like this:

1. Add a field to each record called PageNumber

2. In a script view the report in preview mode, go to the last record, get the page count and put that in the PageNumber field.

3. Omit the last record and repeat the process of getting page count and putting that in the PageNumber field until every record has a number in the PageNumber field.

4. Now create a report with subsummaries based on the PageNumber field.

This worked OK for a small number of pages but would probably be quite slow for a very lengthy report.

James

www.james-mc.com