Jump to content

Restart Subsummary Numbering

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

Recommended Posts

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.



Link to comment
Share on other sites

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

Link to comment
Share on other sites

Sorry, I guess it really was too little information.

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


GetNthRecord(Category; Get(RecordNumber)) ≠ GetNthRecord(Category; Get(RecordNumber) - 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);


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


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


Link to comment
Share on other sites

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.


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:


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

Link to comment
Share on other sites

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 :crazy2: ); that's how I learn. Now I need to take some time to understand and apply your suggestions.


Link to comment
Share on other sites


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.


Link to comment
Share on other sites

This topic is 6061 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
  • Create New...

Important Information

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