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.
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?
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.
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...
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::__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.
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.
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.
Ahh got it! Many thanks!
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.