I must be missing something in your description, because your requirements seem to conflict: if you want records to be grouped by category, you MUST sort by category first. Then, if you want them to be sorted by another field WITHIN the category, use that as the second field in the sort order.
Is this what you want?
If so, first sort by category then by "sequential number". If this is not what you want, perhaps you could post an example?
An brilliant office mate just solved this problem.
We created a new field which assigns each category a number in the order we want the categorie to appear (non-alphabetical). Each record in a category has the category's number in this new field.
Now I can sort by category number, then sequence number. The page break and sub-summary is set for category number field, a trick to get the desired flow of categories, while keeping the deired record sequencing.
Just to clarify, if anyone is still puzzling over my description of the problem or finds this post in the future, searching solutions for the same problem.
The problem was solved (see previous post) by adding a field to represent categories numerically.
I wanted to have a sub summary with page breaks based on my second sort criterion (sub summary parts can only be based on the first sort criterion), however FMP only allows sub summary parts based on first sort criterion.
"Category" could not be my first sort criterion because I did not want them to appear in alphabetical order. My first sort criterion is record sequence numbers, to make the records fall in desired order.
The solution should have occured to me, because it's the same concept used to get the records to fall in desired (non-alphabetical) order: assign sequential numbers to each record (our use of decimal points is because records got inserted and deleted, so whole running numbers got modified with decimals).
Each category was assigned a number, i.e. in the example below, all records in C category assigned "001." All records in A Category assigned "002" and all in W Category assigned "003."
That way my first sort criterion (category numbers) puts the categories in desired order and drives the sub summary, with its page break, based on category. Second sort criterion drives the order of the records.
Sounds more complicated than it is. Basically, if you want non-alphabetical sorting, think about using numbers, either sequential numbers or same numbers (for a like group).
MANY THANKS TO ALL WHO POSTED. FORUMS ROCK!
all records in C category assigned "001."
A simple way to do this would be to create a separate table for Categories, with a field for the category's rank number. Then either sort by the related Category::Rank field, or use a calculation field in the data table to get the related rank number from the Categories table.
Another option is to define a custom value list of the categories, and use it to sort the records.
sub summary parts can only be based on the first sort criterion
No, that is not true. A sub-summary part can be based on any field. A layout can even have several sub-summary parts, based on different fields. If the specified field is anywhere in the sort order, the sub-summary part will be shown - thus you can re-order the sub-summaries by changing the sort order.