Finding sub-summary of an unknown number of items

[Quartekoen]

Not sure if this is the best place for this thread, or if the layouts section would be better. Anyways...

Our database system is great for showing the number of sales broken down by month for a range of items, years, and/or customers. The problem is displaying the total sales for each item.

What I'm looking for is a way for the user to enter an ID range, such as 50...75, and Filemaker finds all those ID numbers (easy). Then, I need it to calculate the sub-total for each item in the range (easy) and display it for each item (not easy). Example:

User enters 100...150 and does the find.

Filemaker returns the sorted records for all the sales of each item, broken down by item ID, with a sub-total of all sales in dollars for each item listed (somewhere... I don't care where at this point). Totals do not have to be listed per month or per year or per customer. It just needs to be the total sales for that item.

The problem for me is displaying the totals. Sure, if I had a field to put the value in, I'd be fine, but I have no idea how many sub-totals will be needed. The user may enter a range of 2 items, and he may also enter a range of 200 items. Having potential fields for this is just not an option.

So far, the best idea I can come up with is having 2 more layouts. One that stores the item ID and its sales total, and another that lists all the items in the range selected. When the user would put click some 'go' button, a script would populate the first table with the item IDs, find those IDs' totals and store them there as well, then display them in the second table. But is there an easier way? Thanks.

Edited July 2, 2008 by Guest

[Fitch]

If you use a subsummary report, it operates on the found set, and it doesn't matter how many different items there are. A single Summary field handles it.