Multiple summaries within a portal?

[ScottR]

I've got to believe I'm just having a brief episode of "stupid" and I'm just over thinking this...

 

I'm essentially trying to create a portal of summaries of items that appear in another portal. Here's the plan...

 

I have a table with individual expense items. I then I have table used for invoicing the expensed items. The data is sorted by date and customer and category creating an invoice with the total amounts. There are several items on each invoice of the same category (i.e. meals & entertainment, or car rental or hotel).

 

A simplified list of expenses on the invoice might look like this..

 

04/01/14   Hotel   $250

04/20/14   Car      $63

04/02/14   Meals   $50

04/03/14   Hotel   $200

04/12/14   Meals   $50

04/06/14   Hotel   $175

04/16/14   Meals   $48

 

 

I'm trying to create a portal that lists the summaries of the categories.  Something like this...

 

Hotels  $625

Meals  $148

Car      $63

 

Since the categories are dynamic, and I'm filtering by invoice number I can't set up summary fields for each category. I've created a self-join relationship and used the GetNthRecord trick to grab the first occurrence of each category from the main portal but now trying to grab the totals of each category is presenting a problem. If I simply do a GetSummary of each category it only lists the amount of the first record (due to the GetNthRecord used in the portal filter).

 

I'm looking forward to learning the errors of my ways! Thanks.

[eos]

Why are you asking via PM, then posting the question again?

 

Anyway, see if the attached helps you:

SelfJoinInFilteredPortal_eos.fmp12.zip

[ScottR]

EOS - thanks for the quick reply. Sorry for the double hit (The forum was an afterthought). Let me look at what you've created. I have a feeling you've nailed it again. Thanks.