Subsummary-esque functionality

[Quartekoen]

Consider my 3 tables: Customers, Line Items (that is, lines of invoices), and Items

When in the Item database, it is very easy to show all the invoices on which that item has appeared in a given timeframe, via a portal on the item layout:

Item::ID = Line Items::ID_Item

and

Item::gDateRangeStart <= Line Items::ShippingDate

and

Item::gDateRangeEnd >= Line Items::ShippingDate

Next, my Line Items are related to the customer that bought it.

Line Items::ID_Customer = Customers::ID

Then, I conditionally format the customer name to only appear once within the portal row

if: Line Items::ID_Customer = GetNthRecord( Line Items::ID_Customer ; Line Items::RecordNumber - 1 )

then: conditionally format to hide the customer name

The result is that I get one portal row for every invoice on which the item appears, and It lists the customer name only for the first record for that customer that bought it.  It works quite beautifully.



The problem is that some customers will purchase the same items several times during a single month.  As such, there are a large number of invoice that appear in their section of the portal, and it would be very useful to see the total that that customer has bought during that month, rather than have to add up all the individual invoices.  Is there a way to get this desired result?

Customer 1 Inv. 123   Qty: 5  Total Purchased: 50  

           Inv. 124   Qty: 30

           Inv. 173   Qty: 15

Customer 2 Inv. 164   Qty: 75 Total Purchased: 100

           Inv. 185   Qty: 25

Customer 3 Inv. 174   Qty: 5  Total Purchased: 5	

Customer 4 ...

I realize that this can be done on a list view report with a subsummary section, but this data is helpful, not critical, and I don't think it merits creating an entire new layout, the scripts to control getting there, etc... I'm just curious if it can be done.

Note that I have tried using a Subsummary when sorted by... with and without running totals, restarts...

Thanks for the help.

[comment]

It is possible, but if you think it's going to be cheaper in terms of resources than producing a report with sub-summaries ...

In a nutshell, you need to place the currently viewed item into a global and use another relationship (or set of relationships) to sum up the item's purchases during the date range in the Customers table.

[Quartekoen]

Hmm, so my options are basically either a layout in the item database or relationship(s) in the customer database.

I'm somewhat sad that this isn't something that is easily done in portals, but I'll work with what I've got. Thanks!