Subtotals in Portals

[DrMonkey]

[color:orange]Short Version:

Is there anyway to resort a table by a specific field so that the record numbers are renumbered?

For example:

Record Number::::Field

[color:green]1:::::01/01/2008

[color:green]2:::::07/01/2008

[color:green]3:::::03/01/2008

[color:green]4:::::12/01/2008

Becomes after you sort:

Record Number:::::Field

[color:green]1:::::01/01/2008

[color:red]2 ([color:green]old number 3):::::03/01/2008

[color:red]3 ([color:green]old number 2):::::07/01/2008

[color:green]4:::::12/01/2008

[color:orange]Long Version:

I have a timesheet database and have a problem with displaying subtotals. The Main table, Clients, has client details, the other table, ClientInfo, has the time detail for working on a clients case (Date and Amount of Time)

I have two layouts that I use for reports. The first layout has a portal that shows records 1-17 and the second layout has a portal that shows records 18-49. The portals are sorted by Date.

My Clients table has a field, that is used on the first layout, that is a calculation with a formula of

SumRange ( ClientInfo::TimeOut; 1; 17)

and another field, that is used on the second layout, with the formula of

SumRange ( ClientInfo::TimeOut; 18; 49)

and SumRange is a custom function:

Case ( start ≤ end and  IsValid ( GetNthRecord ( fieldName ; start ) ) ;

GetNthRecord ( fieldName ; start ) + SumRange ( fieldName ; start + 1 ; end )

)

This function uses the Record Number and doesn't care about how the table is sorted.

This works great for the most part.

If you enter data that is not in chronological order the formula doesn't work. The function is looking at record number as it is stored in the table and not how the records are displayed in the portal.

[comment]

If you sort your child data by Date (in the relationship's definition) it should work as you expect. However, there's something fundamentally wrong with this approach. What makes records 1-17 different from records 18-49?

[DrMonkey]

That did the trick, thank you very much.

The 1-17 records and 18-49 records are not different but the report has to be broken out in this grouping.