9 Replies Latest reply on Feb 5, 2013 1:18 PM by philmodjunk

    Sorting in List View or Perform Find

    JohhnyHilly

      Title

      Sorting in List View or Perform Find

      Post

           Not sure if this is in the forum already but I couldn't find it. I have two tables, Clients and Vessels. One client can have multiple vessels. I would like to list all the Company/Client names, then under that show all the vessels in that company all on one layout. Am I able to do this in a list view with a sort list key etc, or do I have to perform some sort of find?

        • 1. Re: Sorting in List View or Perform Find
          philmodjunk

               The first thing to look at are your relationships. Otherwise a find or Go to Related Records won't work for you as needed.

               Am I correct that a vessel can have multiple owners and that an owner can have multiple vessels?

               If so, the relationship between Vessels and Owners requires a Join table.

          • 2. Re: Sorting in List View or Perform Find
            JohhnyHilly

                 Technically speaking a vessel can have more than one owner but in this case these vessels are unlikely to pass between different owners. If I was to put a join table in between Clients and Vessels what would need to go in there? By adding this, will it effect my tables further down the line? If a vessel can only have one owner, how would I go about setting it up as mentioned in the 1st post?

            • 3. Re: Sorting in List View or Perform Find
              philmodjunk

                   I wasn't thinking of Vessels "passing between owners" but of the possibility that one vessel might have several part owners. But different owners over time could also justify a many to many relationshp, but that would depend on whether you need to track such historical data. (Do you need to know who previsously owned the vessel?)

                   I wouldn't add the complexity of a join table unless there truly is a need for managing a many to many relationship such as mutliple owners for one vessel and multiple vessels for one owner.

              • 4. Re: Sorting in List View or Perform Find
                JohhnyHilly

                     Okay, no there are no part owners so no concern there. No need to know who previously owned the vessel either. All I am concerned about is the current owner/company. Soooo with this in mind what would be my best way forward? As you said a Find or GTRR won't work for me...

                • 5. Re: Sorting in List View or Perform Find
                  philmodjunk

                       Actually, I said they won't work until we are sure that the data model, the underlying tables and relationships, are set up correctly. Then they can do their job correctly.

                       We've now confirmed that this is a one to many relationship:

                       Owners----<Vessels

                       Owners::__pkOwnerID = Vessels::_fkOwnerID

                       With that data model, you can set up a summary report based on Vessels that includes data from Owners in a sub summary layout part that serves as a "sub head" for each group of records owned by a given owner.

                       Use layout Part Setup... to add a sub summary part to your layout. Select "when sorted by __fkOwnerID" for this layout part.

                       Put fields from Owners in this new layout part while putting fields from Vessels in the body layout part.

                       Then you can perform any kind of find that is of use to your or do a Show All Records if you want the full list. Just be sure to include the _fkOwnerId field in your sort order or the owner info will not appear on the layout.

                  • 6. Re: Sorting in List View or Perform Find
                    JohhnyHilly

                         Thank you. In your last sentence referring to the "sort order", where is this specifically? You mean under the Part Definition "Sub-summary when sorted by..."?I want to Show All Records but as you said the owner info is not appearing.

                    • 7. Re: Sorting in List View or Perform Find
                      philmodjunk

                           I mean that when you select Sort from the Records menu, one of the fields you select to produce your sort order, the list of fields shown in this dialog, must be the _fkOwnerID field. You can also sort by other fields such as sorting by an Owner name to get an alphabitized list, but you must also include sorting by the _fkOwnerID field--the same field you specified as the "when sorted by" field for th sub summary part.

                      • 8. Re: Sorting in List View or Perform Find
                        JohhnyHilly

                             Ahh got it! Many thanks!

                        • 9. Re: Sorting in List View or Perform Find
                          philmodjunk

                               Note that the ability to make sub summary parts appear and disappear depending on how you sort your records can make for a very powerful and flexible report layout where a number of different reports can be produced just by how you find and sort the records used to produce it.