Restart Subsummary Numbering

[Lougee]

I don't know whether to give too much information (include the actual calculation) or too little, so I'll assume the wonderful experts on this forum will be familiar with the specifics of the calculation I'm referring to.

I'm trying to implement John Mark Osborne's RestartNumbers (from FileMaker Advisor, June/July 2006) and it's not working and I haven't been able to figure out why.

The report is being generated from a table called "activeYears" and that's where I created my own RestartNumber field. I did a copy/paste of John's calculation and edited the name of the break field from his 'category' to my 'school::schoolName_short'. I've double-checked that the name of the subsummary part/break field, the referenced field in the calculation and the field in the sort order are all the same. And still all I get in the RestartNumber field in my report is question marks instead of numbers.

So, hoping I've given enough information for someone to figure out what I'm talking about, what am I doing wrong? It should be so simple and I'm tired of beating my head against a brikc wall.

TIA

Abbe

[comment]

Frankly, I have no idea what you're doing, and - more importantly - what you are trying to accomplish, but one thing caught my eye:

In GetSummary(), breakField cannot be a related field (although it can be an unstored calculation field = related field).

If that's not it, then you'd better start explaining...

[Lougee]

Sorry, I guess it really was too little information.

John Mark Osborne published a calculation to restart the record numbers for each subsummary grouping --

[color:red]Case(

GetNthRecord(Category; Get(RecordNumber)) ≠ GetNthRecord(Category; Get(RecordNumber) - 1);

1;

GetNthRecord(RestartNumbers; Get(RecordNumber) - 1) + 1

)

where 'category' is the break field. So, when I created my report to group results by school, I copied and pasted this calculation into my own 'RestartNumbers' field and changed the break field referenced to my own 'schoolName' which comes from the related table SCHOOLS.

When I sort by schoolName, my report divides properly, separating students by school, but the numbering (using my RestartNumbers field instead of the 'insert record number symbol') I see only a question mark, not a number.

The RestartNumbers field is plenty large enough to show even a 5 or 6 digit number so it can easily handle my 1 and 2 digit numbers. And I can't think of anything else I could be doing wrong.

My calculation looks like:

[color:red]Case (

GetNthRecord (schools::schoolName; Get (RecordNumber)) ≠ GetNthRecord(schools::schoolName; Get(RecordNumber) - 1);

1;

GetNthRecord (RestartNumbers; Get (RecordNumber) - 1) + 1

)

Sorry for being so uninformative earlier; I was foolishly trying to keep it short.

Abbe

[comment]

Oh. Well, surprisingly the answer is the same, although for a different reason.

The GetNthRecord() function can work with records in the same table, OR with records from a related table.

This:

GetNthRecord ( Category ; Get(RecordNumber) - 1 )

returns the value of the Category field in the previous record of the SAME table. If the "current" record is #10 in the found set, it will return the value from record #9.

OTOH, this:

GetNthRecord ( AnotherTable::Category ; Get(RecordNumber) - 1 )

returns the value of the Category field in the 9th related record - assuming our record #10 has at least 9 related records in AnotherTable. Otherwise it will return a "?".

What you really want to compare here is the LOCAL matchfield to the other table - in your example I guess that would be the SchoolID field.

BTW, this:

GetNthRecord ( Category ; Get(RecordNumber) )

is a very elaborate way to get the same result as this:

Category

Finally, I cannot help wondering why you don't simply use a summary field, defined as running count of some field that will never be empty, with the option to "Restart summary for each sorted group".

[Lougee]

This is one of those days when I'm reminded of how little I know and how much more I have to learn.

Thank you for the input, Comment. I'm a 'fiddler' (taking things apart and putting them back together, not a violin player ); that's how I learn. Now I need to take some time to understand and apply your suggestions.

Abbe

[Lougee]

Comment,

I spent some time fiddling with subsummary report parts and summary fields as you suggested and learned how to use the summary field to make my record numbers restart after each group. One more thing crossed off the to-learn list.

Why was I trying to reinvent the wheel? Partly because I was really really ignorant of summary fields and how to effectively use them, partly because I always make things more difficult and complicated than necessary -- seems to be in my genes, and because I read the article about creating the RestartNumbers calculation and bound and determined to make it work.

My supervisor is happy now that her report numbers the way she wants which makes me happier too.

Thanks again for the help.

Abbe

[comment]

I am still puzzled by that article. It uses GetNthRecord() which only became available in version 8. IIRC, the option to restart summary fields was added in the same version - so it looks like the entire idea was obsolete at birth.