sub-summary in a portal???

[drjpowis]

Hello

I need some help with a portal.. hopefully somebody will be able to help

For simplicity, imagine I have 2 tables - customers and transactions. In the customer table for example, customer A has bought 6 products - 3 Apples, 2 Pears and 1 Banana. Obviously with a normal portal, it will simply all the products as separate line items in a portal in the customer screen.

However, is it possible to have a summarised portal as well

[RalphL]

Is there any reason why you counldn't have a quantity field in transaction table?

Then your portal would show"

Apples 3

Pears 2

Banana 1

[Ender]

Portals show ALL records for the specified relationship. There's no natural way to sub-summarize within a portal. You can filter a portal to show only records that match specific criteria, (just show Apples, for example.)

The only way I can think to summarize through a portal is to use a script to go to the related records, sort by Product Name and set a Marker on one of each different product name, then jump back to Customer, switch to a layout with a portal that uses the Marker as part of the match criteria, and in this portal show the related Product Name and a Product Count calc field. The Product Count would be defined in Transaction to be something like GetSummary(Count of Records, Product Name), where Count of Records is a Summary (count) field.

Might be better to have a Product table, where you have each type of product defined. Link to this from Customer and show Count fields.

[The Shadow]

I tried getting this to work without a script, and it does a little (see example attached).

I can't see how to get the totals without also having the customer id present in the "Products" table, which provides the uniqueness. Otherwise, trying to join directly back to Transactions gives the sum for all customers who bought that fruit.

Any ideas?

[drjpowis]

many thanks for this suggestion. I have downloaded your file which works really well - thanks. my only problem is as you probably guessed, the Apples and Pears thing was just an example.

My database has 35 products and 50,000 customers which constantly grows. What is the best way of populating the 'uniquefruit' table with this many records - any ideas?

[Ender]

The Shadow:

I see the trouble you were having. It took me a while but I think i got it working. The refresh is'nt pretty but it seems to work by making the custID in Product a global. See attachment.

David:

The easiest way to populate products is by sorting by product and exporting with the "Summarize by..." option. you can then import the export file into your Product table. Though if you only have 35 products you can just type them in.

<<Attachment added>>

[The Shadow]

Thanks Ender - thats much better.

I also think the "Refresh" script should have another SetField[] of the global customer id in products, that way you could use that script whenever moving to here from another layout.

PS: This would really be a nice spot to have a script-trigger - whenever we navigate to a new record.

[Ender]

>>PS: This would really be a nice spot to have a script-trigger - whenever we navigate to a new record.

Ya, we keep seeing new uses for that little plug-in.