Subsummary reports in list view - unable to get desired layout

[bobearl]

This seems so simple that I hesitated to post it until I had thrashed around with it for a couple of days unsuccessfully. Any help would be much appreciated.

I am making a task list viewer that by default lists all tasks for a group of users. I would like to show all the users and the number of tasks they have in a subsummary report at the top of the page, followed by a list of all of the tasks sorted by user:

User 1 - 2 tasks

User 2 - 1 task

User 3 - 9 tasks

--Task A User 1

--Task B User 1

--Task C User 2

--Task D User 3

I am getting stuck, however. If I use the subsummary report and place the task list in the body of the document, I get:

User 1 - 2 tasks

--Task A User 1

--Task B User 1

User 2 - 1 task

--Task C User 2

User 3 - 9 tasks

--Task D User 3

This fails to give me the 'heads up' view with all of the tasks at the top (there are 30 users, so scrolling through is a pain). I can get the task view itself with just a subsummary and nothing in the body, but then can't figure out how to get the tasks on the same layout:

User 1 - 2 tasks

User 2 - 1 task

User 3 - 9 tasks

I really appreciate any advice anyone might have!

[bobearl]

A sample file might make this clearer. Attached is a simple file of tasks and users. Also is attached a few of the views of the data: I can get the list of people, and the list of tasks, but I'd love to have the list of people above the list of tasks rather than interdigitated as in the 'TaskListByPerson' layout.

SubSumPractice.fp7.zip

[bruceR]

Try this. Uses the Virtual List technique.

SubSumPractice.fp7.zip

Edited March 20, 2010 by Guest

[bobearl]

Very slick, Bruce, thanks - I would definitely not have thought of that. I really appreciate your help!

[bruceR]

Thanks. The "Virtual List" technique is extremely versatile. Lotsa ways to use it.

[bobearl]

I found another way of solving my problem, and wanted to share it if anyone is at all interested. It is in no way as flexible or as powerful as Bruce's method, but it solved one problem that I was wrestling with.

The problem I couldn't quite fix was the issue of being able to use the list to pass parameters. In this example, I wanted everyone to be able to see who all has tasks to do, filter those tasks if they wish, and then still see who has tasks within the found set. Bruce's solution works beautifully for this. What I struggled with was allowing the user to click on a task-doer, which could then filter the task list to only those tasks in the prior found set by that user.

I finally found a solution by adapting Ray Cologon's solution at nightwing.com for "Displaying the Found Set in a Portal - dynamically!" at

http://www.nightwing.com.au/FileMaker/demos8/demo804.html. It involves a custom function which captures all the ID's in a found set (in this case, all people in the found set of tasks) and using this set as a global multikey for a portal. My solution has a slight wrinkle, which uses a calc field in the people table to count the number of repeats of a personID in the multikey and show that as the number of tasks for all task-doers in the found set.

[i suspect that for really large numbers of records, keeping track of a really large multikey like this will erode performance, and that a different custom function which only adds to the global multikey unique values would be much more elegant. After 3 days stuck on the same goofy problem, though, I can't think of how to do it and still generate my tasks per user field, but I look forward to challenging this - later]

At the risk of being sappy, let me reinforce how tremendously valuable it is for novices like me to stand on the shoulders of the profoundly talented and experienced folks through this forum and other resources, in this case Bruce and Ray in particular. To those of you who tirelessly contribute and edify, I suspect you know but I wish to reinforce: people like me are tremendously grateful that you are willing to share your wisdom. Many thanks!

SubSumFoundSetPortal.fp7.zip

[Heatherrd]

I think I have a similar problem.

My tables are:

Materials, Material_Lines, Styles, Orders, Order_Lines, Customers

I need a report (production schedule) to look like this: (It was originally done in excel with each piece of data entered manually from the orders.)

Style: XYZ Shirt

Ship Date: 7/1/10

Customer XS S M L XL

Gap 2 4 4 4 2

Banana 4 4 4 2

Old N 3 3 6 6 3

Sub TTL 9 11 14 12 5 total 51

Style: XYZ Shirt

Ship Date: 8/1/10

Customer XS S M L XL

Macy's 10 10 10 10 5

Banana 4 4 4 2

H&M 5 5 3 3 3

Sub TTL 19 19 17 15 8 total 78

Grand total: 129 (of Style XYZ Shirt, all sizes, all Ship dates)

I've been trying to figure out if I can do this with self-join Order_Lines tables or portals in the Styles table, but can't figure out how to get the data organized and sub-totaled with so any parameters.

Also, I can't figure out how to look at the example files. I'm using a trial of FMP 11 and the example file says I don't have access to modify so it won't let me into managing where I could see the inner workings. Someone told me I could log in somehow, but I couldn't find the place. Your help is greatly appreciated!

Heatherrd

Fall_Winter_10_Production.zip