You don't need a self join. A self join is a relationship linking two occurrences of the same table--not what you need here.
What you need is a conditional value list.
You appear to have relationships based on company name, this is not the optimum approach as company names are not unique, they change and users fail to enter them perfectly every time. A serial number field, __pkCompanyID defined in your company table is a much better field to use for linking to Stock and Contact. The same is even more true for linking a contact record to a stock record. (Don't see any field in your first post that can be used to link a contact to a stock record.)
In Manage | Database | relationships, make a new table occurrence of Contact by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box ContactByCompany.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
Stock::Company = ContactByCompany::Company
You can use this relationship to get your conditional value list to work. See these links for more on the subject:
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
Hi Phil. Thank you so much for your reply and apologies for my tardy response. Been working not browsing on the forums where I definitely need to spend more time. Phil your solution worked, thank you. I had found an article on the same subject immediately prior to your response that put me on the right path but your response clarified it for me to a working solution. Again my thanks. Your comment re a unique ID field within each table got me to rethink and rework the existing setup. That side is working fine. However it has added a complication I'm struggling to wrap my head around currently. That is "How do users lookup information - they will never know IdentificationID's nor do I want them to". Again I know I'm on the right track, just need a simple version laid out for me in context to what I'm trying to achieve. Example scenario: Real Estate company (Comm & Ind) - they select properties for opportunity identification based on SQM Size (Property Size, always numeric, not unique or mostly unique to the address) and Address. I adopt a route similar to an invoicing system to allow them to create an opportunity (Link to Customer, Unique OpportunityID, Multiple line items displayed on a portal lower on the form) First time making use of a portal (Amazing thing!) How do I allow them to select records (within the portal) based on a pop-up of SQM and Address (Sorted by SQM) I have a working portal for this already, however I can only get it to work by popping up on StockID (The property unique ID). This I need to change to a display popup of SQM and Address I think the solution to this - hopefully I'm not up another garden path - is a self-join on Stock Table (Where the property stock resides). I also may need a Value List. Ive tried so many iterations and combinations that its gotten difficult to see the wood for the trees. Could you be so kind as to provide a step by step quick response on the above scenario? (An easy explanation and example on the above, should help me put it into practice with the other tables) Again many thanks for taking the trouble to reply.
"How do users lookup information - they will never know IdentificationID's nor do I want them to"
Value lists can be defined to list values from two fields in two columns. The data in the first field is what is entered into the field formatted with this value list when the user selects a value. In the case of ID numbers, the ID number can be field 1 in the value list and a name field can be field 2. Thus the user sees and selects a name, but the value list enters the ID number. Sometimes a calculation field is used for the secondary field that combines data from multiple fields such as a first and last name field or individual address fields to pull together the needed data for the second field.
It is possible to hide the ID number so that the user never knows that they are entering an ID number, but if you choose to hide the first field--an option in Value List Setup, make sure that the values in the second field are unique or some ID numbers will no longer be selectable from this value list.
There are also more sophisticated setups that use a search portal or an auto-complete enabled value list of name data that use scripting to make the set up work. These options take more programming to set up, but make longer value lists more manageable.