Variable Sum over a Portal

[FM::Student]

Hi,

So I've got a portal on my layout that has two fields in it: "Fiscal Year" and "Amount". There can be any number of records in this portal and I want to be able to calculate two values; "5 year target" and 20 year target". Both these calculations sum the "amount" fields in the related portal for the next 5 or 20 years.

The current fiscal year is in a global field in the database so that the user can do historic reporting. Currently the "Fiscal Year" field is formatted "****/**" but I can change that if I need to.

I'm at a loss on how to do all this. Any help? Thanks.

[mr_vodka]

If I understand you correctly, then do a search for filtered portals.

[FM::Student]

I'll try and clarify myself a bit. I've got a portal on the form that looks like this:

Fiscal Year | Amount

1990/91 | 100

1991/92 | 150

1992/93 | 125

... | ...

Based on a Global Field that has the current Fiscal Year (Or any the user inserts into it) in it, I need a calculated field that sums the next 5 years, and one for the next 20 years.

So in SQL something like (the following SQL is not quite right but it's the general idea):

SELECT SUM(MyTable.Amount) FROM

(SELECT TOP 5 MyTable.Amount FROM MyTable WHERE MyTable.Fiscal_year > Global.fiscal_year)

So I want to sum up the first 5 amounts that have fiscal year year greater than the current. I hope that makes more sense.

[mr_vodka]

Create two calculation fields

cFiveYear

Case ( Global.fiscal_year; List ( Global.fiscal_year; Global.fiscal_year + 5 ) )

cTwentyYear

Case ( Global.fiscal_year; List ( Global.fiscal_year; Global.fiscal_year + 20 ) )

Create a new field (fyear) that represent the year (1990) in your child records rather than your description of Fiscal Year (1990/91).

Now create two new table occurrences and make a two new relationships keyed from the two calc fields to this new fyear field.

You will use a multi-predicate join such as:

cTwentyYear ≤ fyear

cTwentyYear ≥ fyear

Put the summary field that sums the Amt field on the layout for each of your 2 table occurrences.

The reason I had you create two new relationships instead of using the same one for your portal is because it seemed as though you wanted to always show all the records in the portal rather than filter them.