Jump to content

sub-summary in a portal???


drjpowis
 Share

This topic is 6435 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

  • Newbies

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?

Link to comment
Share on other sites

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>>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6435 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.