But what is the purpose of the sub summary part?
These parts are placed either just above or just below a group of records in your report. How your records are grouped is controlled by what field you specify as the "break" or "sorted by" field. You then have to sort your records in a sort order that includes this break field or the sub summary part will be invisible.
The sub summary part shows the room name then the body shows the contents of that room. If I have the sub summary part sorted by PK_Room_Id it works fine but just shows the room records as they were entered. If I try and sort it by the number field, that part is then invisible.
If I try and sort it by the number field, that part is then invisible.
If you want to sort by a different field, then you must also update the sub summary part settings to specify it as the "sorted by" field.
I have done this, is it perhaps something to do with the relationships between the tables I'm displaying records from?
What table occurrence box from your relationship graph is listed in Layout setup | Show Records From?
Need to know your starting point here.
Then please describe the design of your report, (what parts, what sorted by fields, etc.)
Then describe how you are trying to use the number field in place of the primary key field.
The body of my report is showing records from the table "Room Inventory".
There is a sub summary part directly above the body which is sorted by the field "Category", from the table "Room Inventory".
The sub summary part above that is where I'm having problems. This is from the table "Rooms".
The report should read;
All items within that category and room record.
I guess a starting point would be to understand how sorting the records by PK_Room_ID shows the records correctly but when I change the sort field, it just doesn't display at all?
That makes no sense at at all to me, since PK_Room_ID is not listed as a break field in either sub summary part.
I'd check the value in Rooms for your Room Inventory Records and see if it has the correct value for every record.
If Room and Category are your break fields, then you should sort first by Room, then by Category in your specified sort order.
I don't understand what you mean.
The report works almost perfectly with;
Sub Summary Part Sorted by PK_Room_ID
Sub Summary Part Sorted by Category
Body (Showing records from Room Inventory)
I'm just looking for some way for the user to control the order the rooms show on the report.
And I think I misunderstood. Your previous post said:
"There is a sub summary part directly above the body which is sorted by the field "Category", from the table "Room Inventory".
The sub summary part above that is where I'm having problems. This is from the table "Rooms"."
That doesn't list the primary key as a break (sorted by) field and thus it would have no impact on the appearnce of your report with these two sub summary parts.
If it looks nearly right with PK_Room_ID and not when you subsitute Room and sort accordingly, then the values in Room would be the cause of the trouble here. Since I can't see those values from here, I've suggested that you check to see what those values really are in your table.
What values are your entering in Rooms and how do they get entered into this table?
I've tried just adding a "number" field to the rooms table. So as well as the PK_Room_ID serial number, each room has a number just selected by the user from a drop down, 1,2,3.....
With this the Sub Summary Part Sorted by "number" just disappears.
We've talked of Room, Number and PK_Room_ID. I'm losing track here.
And when you changed the "sorted by" field to "number", did you then resort your records so that they were now sorted by this field instead of PK_Room_ID?
"Rooms" is the table i'm displaying records from in this sub summary part.
PK_Room_ID and Number are fields within this table.
I take it by running my report script it would resort the records anyway?
Only if your script has a sort scritp and then only if it specifies the correct sort order. It won't update automatically to a different order to match the break fields specified in your sub summary part.
When I try to produce a summary report and a sub summary part does not appear, the first thing I do is select Sort from the Records menu to see what the current sort order is so that I can sompare that order to what I specified in the sub summary parts.
PS. You have a table named Rooms, but also a field or fields named Room--which contributed to my uncertainty with some of your previous posts.
Apologies for the terrible table/ field names :).
I think you've hit the nail on the head with the report script, I'll have a look at this shortly and let you know if I have any issues.