1 2 Previous Next 15 Replies Latest reply on Apr 15, 2011 5:50 AM by MatthewMackay

    Sorting Sub Summary Part by Field

    MatthewMackay

      Title

      Sorting Sub Summary Part by Field

      Post

      Evening,

      I've posted this question previously but thought by adding a picture of my relationship table, it may help. I basically want to let the user have some control over the way records are displayed on the report by numbering them 1,2,3....

      At the moment my sub summary part is sorted by PK_Room_ID, I thought I could just sort by a field in which the user assigns a number to the record.

      Any suggestions would be much appreciated.

      Cheers

      Screen_shot_2011-04-13_at_21.11.23.png

        • 1. Re: Sorting Sub Summary Part by Field
          philmodjunk

          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.

          • 2. Re: Sorting Sub Summary Part by Field
            MatthewMackay

            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.

            • 3. Re: Sorting Sub Summary Part by Field
              philmodjunk

               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.

              • 4. Re: Sorting Sub Summary Part by Field
                MatthewMackay

                I have done this, is it perhaps something to do with the relationships between the tables I'm displaying records from?

                • 5. Re: Sorting Sub Summary Part by Field
                  philmodjunk

                  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.

                  • 6. Re: Sorting Sub Summary Part by Field
                    MatthewMackay

                    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;

                    Room

                    Category

                    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?

                    • 7. Re: Sorting Sub Summary Part by Field
                      philmodjunk

                      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.

                      • 8. Re: Sorting Sub Summary Part by Field
                        MatthewMackay

                        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.

                        • 9. Re: Sorting Sub Summary Part by Field
                          philmodjunk

                          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?

                          • 10. Re: Sorting Sub Summary Part by Field
                            MatthewMackay

                            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.

                            • 11. Re: Sorting Sub Summary Part by Field
                              philmodjunk

                              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?

                              • 12. Re: Sorting Sub Summary Part by Field
                                MatthewMackay

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

                                • 13. Re: Sorting Sub Summary Part by Field
                                  philmodjunk

                                  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.

                                  • 14. Re: Sorting Sub Summary Part by Field
                                    MatthewMackay

                                    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.

                                    Cheers

                                    1 2 Previous Next