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) ]
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
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.
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?
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.
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.
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.