3 Replies Latest reply on Sep 6, 2012 10:37 AM by philmodjunk

    Self-Join Absolutely Stumped



      Self-Join Absolutely Stumped


      As a new user to Filemaker, having diligently worked through the tutorials, read the documentation and spent two days googling "Self-Join Relationships", I am at a "postal" emotional level currently trying to resolve what is in effect, a relatively simple ask.

      The scenario: 1 File with 3 tables
      The tables are:
      Company (Name[Unique], Switchboard, Address, WEB etc)
      Contact (Company, Surname, First Name, Mobile, Landline, email etc)
      Stock (Company, Contact Name, Contact Number, Item etc)

      Stock is provided by multiple vendors and an entry will not be accepted without both a company name and a contact (from the existing company)

      Trying to achieve:
      In Stock view, company selection via pop-up (which works) will filter out and display (via pop-up) on Name, only those contact names who work for that company (not-working). From there the related information such as mobile and email etc are auto-calculated from the Contact table (working)

      In theory the above is really straight forward, actually implementing it has stumped me. I understand the right solution is to use "Self-Join", however for the life of me I am unable to get this to work. Not a single example of "Self-Join" on the site or via Google has pointed me to a working solution.

      Please help me out whilst I still posess my own hair. I can take instruction.

      Many thanks.

        • 1. Re: Self-Join Absolutely Stumped

          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

          Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

          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.

          • 2. Re: Self-Join Absolutely Stumped
             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.
            • 3. Re: Self-Join Absolutely Stumped

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