8 Replies Latest reply on Apr 12, 2015 6:34 PM by philmodjunk

    Join help required



      Join help required


      Hi all, I am new here and quit new to filemaker. I am currently using the trial version.

      I have created three tables as follows;
      1. Sites
      2. Join_sites_keys
      3. Door_Keys

      All tables have a primary key, and the Join_sites_keys has two more numeric fields designated as ID_sites and ID_keys.

      I have created a "Sites" layout showing the sites address etc. The "Keys" table contains a list of all the various keys used to unlock the doors at all the sites. I have created relationships from the "Sites" table to the JOIN table, and from the "Door_Keys" table to the JOIN table with allow creation of records via this join enabled.

      There are about 20 door keys and about 200 sites. One door key can be used at more than one site.

      My questions are;
      1. How do I show a list of 'unrelated' keys on the sites layout.
      2. How to I create a new record in the JOIN table from a button on the Sites layout.
      3. How can I assign the current Sites primary key and the Door_Keys primary key (from the list in question 1) to the JOIN table.

      I have tried placing a drop down list box on the "Sites" layout and selecting the "Door_Keys" text value to display but nothing is shown in view/edit mode.

      I have been trying to work this out for days without success. All my google searching has not helped me. Any advise or guidance would be appreciated.


        • 1. Re: Join help required

          First, let's confirm that these are you relationships:

          Start with these relationships: (Use your field names in place of mine)


          Sites::__pkSiteID = Join_sites_keys::_fkSiteID
          Door_Keys::__pkDoor_KeyID = Join_sites_keys::_fkDoor_KeyID

          You can place a portal to Join_sites_keys on the Sites layout to list and select  Door_Keys records for each given Sites record. Fields from Door_Keys can be included in the Portal to show additional info about each selected Door_Keys record and the _fkDoor_KeyID field can be set up with a value list for selecting Door_Keys records by their ID field.

          1. Unrelated keys would require adding another relationship to different Tutorial: What are Table Occurrences? of Door_Keys and your join table:


          Sites::anyField X Door_Keys|unassigned::anyfield
          Door_Keys|unassigned::__pkDoor_KeyID = Join_sites_keys|unassigned::_fkDoor_KeyID

          Put a portal to Door_Keys|unassigned and give it this portal filter:

          IsEmpty( Join_sites_keys|unassigned::_fkDoor_KeyID )

          2. You don't actually need the button. 3, as described next, will both create a new record in the join table and link a Door_Keys record to it all in one operation. But some people prefer a button:

          Set Variable [$SiteID ; Value: Sites::__pkSiteID ]
          Go to Layout ["Join" ; (join_sites_keys) ]
          New Record/request
          Set Field [join_sites_keys::_fkSiteID ; $SiteID ]
          Go to Layout [original layout]
          Note that this only creates the new join table record and links it to the current site record. It is not yet linked to a door key.

          3. If you have "allow creation..." enabled for the join table in the sites to join table relationship, you can put the _fkSiteID field into your portal row and format it with a value list that is set up as a "use values from field" value list where the first field selected is __pkDoor_keyID and the second field is some kind of name or description field in Door_Key to help you know which key you are selecting. This allows you to select the key by its name or description, but the value list enters the needed ID into the field to link the join table record to this Door_Key record.

          For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Join help required

            On a side note, it seems the Join table describes "Doors." One Site has many doors; one Door_Key opens many doors. The many-to-many between Sites and Door_Keys remains, but the join is more tangible.

            • 3. Re: Join help required

              Well Glenn, good thing you came here :)

              Check this out: Youtube Video

              The file is there in the description.


              Guy Stevens

              • 4. Re: Join help required

                Well Glenn, good thing you came here :)

                Check this out: Youtube Video

                The file is there in the description.


                Guy Stevens

                • 5. Re: Join help required

                  Hi, all. First I must say thanks for such quick replies, it is greatly appreciated.

                  I have not yet looked at the youtube videos, but have been trying to implement the solution suggested by PhilModJunk. Upon initially reading your reply, it seemed to make a lot of sense, however now there are some things I don't understand. I don't know how to actually do what your saying in response 1. Can you possibly provide a little more detail please? I think I know what you mean by creating an occurrence of the table; I now have Door_Keys2 in the relationships view, but I am not sure how/where to create the relationship?

                  Korry, I don't quite follow what you mean. I will try to explain a little more. Each site can have up to 4 doors. Each door can use one of about 20 door keys. Some sites use the same key for all doors, some sites can use a different key for each door, and some sites can use the same key for 2 doors and a different key for each of the other doors. Not sure if this helps your comment?

                  Guy Stevens, thanks for your link and comments. I agree coming here was good smiley

                  • 6. Re: Join help required

                    You can double click a relationship line to open a dialog where you can specify relationship details. One of those options is to change the operator from the default = to a different one such as the Cartesian join operator (X). This matches any record in the first table to all records in the second table. You have to drag from a field in one occurrence box to a field in the other to set up the relationship, but with this relationship, it does not matter what field you select. Literally "any field" can be specified on both ends of this relationship. In fact, you could later delete these match fields from your table and the relationship still works.

                    To get this to be a portal that only shows unassigned keys, the portal filter is used to drop out all records that are not linked to a record in the join table.

                    • 7. Re: Join help required

                      Hi PhilModJunk, thanks again for your detailed reply. I read through your linked post from your first reply helping me understand your terminology. It has helped me somewhat and I must thank you for that.

                      I put in quite a lot of hours yesterday trying to get some of this to work with mixed success. I have worked out how to make the Cartesian join no problems, however with the Filter applied, I then can only choose a specific key once for each site. I have removed the filter and it seems to work the way it should (no button as you suggested is a fantastic idea).

                      Something I am still not too sure about is when you say "Sites>---x--<Door_Keys|unassigned>-----Join_sites_keys|unassigned", do you meant that I need to name the table occurrence "Door_Keys|unassigned" as the pipe symbol together with the word unassigned makes it easy to understand the relationships etc when you see that name in various places when editing the database?

                      I have tried to duplicate the Sites---<join table>----DoorKeys for another many to many relationship in the same database and am having some trouble with it but there are some differences in what I am trying to do. Once I have a bit more time I will come back here and ask some more questions if you are happy to use your valuable time and respond I would be greatly appreciative.

                      • 8. Re: Join help required

                        however with the Filter applied, I then can only choose a specific key once for each site. I have removed the filter and it seems to work the way it should (no button as you suggested is a fantastic idea).

                        I don't quite understand how you are trying to use this portal. You asked for a list of unassigned keys. That's all that you should get here. Without the filter, you get a list of all keys whether assigned or not. This was not intended as a means of directly editing data as you already had a different portal for that purpose (and my comments about not needing a button apply to the portal based on the join table, not to this portal listing unassigned keys). On the other hand, a button in the portal to Door_Keys|unassigned could perform a script to select that unassigned door key record and link it to the current site record by creating a new record in the join table with the needed match field values from the current Site record and the selected unassigned key.

                        Door_Keys|unassigned is simply the name that I selected for the additional table occurrence. There are any number of different "naming conventions" in use by different developers. This just happens to be one of mine.