I looked at your demo, thanks. The tables are set up exactly like mine and are behaving as I'd LIKE mine to when entering group choices.
I'm using checkboxes to display group selections in my people layout so I can make the group associations. When I check or uncheck the group selections, the only thing that changes is the group_id field in the 1 record in the join table. It should be adding new records in my join table, not adding additional group_ids in the group_id field within my join table. Conversely, if i remove a group association, that record shoud delete from the join table.
You can easily DISPLAY existing joins by means of a checkbox field (a calculated one!) - but you cannot MAKE associations just by checking a selection in the field - for this, you need to create a new record in the join table. Similarly, to remove a join, you need to delete that record.
If you want, you could run a script on modification of the checkbox field to add/delete the appropriate record/s in the join table, or (preferably IMHO) use a portal showing all records from the Groups table, with a button to toggle them on/off.
So I guess my real question now is: What's the best way to collect data in a layout that establishes these relationships? I thought my method was pretty sound. Now it seems it is not.
I figured if I have a new person that needs entering into my database, this would be a swell time to note the group associations.
I am not really sure what you are asking.
Please read from the beginning of the original post so you are aware of my tables and relationships. Let's assume these are all perfect.
Now. Let's say I'm working from a people layout. I am entering data associated with a person.
Within this layout, I WAS using a checkbox list of group text (I hid group_id number) using a value list. I mean, I have to have SOME way of selcting none or many groups this person is affiliated with.
Now, I am trying to figure out how I can achieve what I've described above, whether a checkbox is needed or not, so that the associations are clear in the layout, but I also have the proper entries for each relationship in the join table.
I don't know how to ask this any other way, I'm afraid. I don't know how to make this happen.
I think I have already answered this: people are associated with groups by means of records in the join table. Selecting (or deselecting) an item in a checkbox field will NOT cause a record in the join table to be created (or deleted). All it does is add an an item to the field (or remove it).
The simple way to add a person to a group is to select the group in the last row of the portal to the join table.
NOTE: you could use a checkboxfield to assign a person to one or more groups directly; but then you wouldn't be using a join table and - as a result - you would lose quite a lot of finding/reporting abilities. Also, the join wouldn't be able to have any attributes of its own (for example the date of joining the group).
Okay. I set up a portal like you showed in your demo. This DOES work, but from a user interface perspective and knowing that many people will be using this from a Web bbrowser, this does not seem ideal. People can go to the last column and delete the group name and it actually deletes it from the group table.
Also, it looks a bit silly having an ID number present instead of the text on the far left. You can hide the first field and use a pop-up menu, but it does not look very good or perform well when using a Web browser.
Also, if one enters a duplicate, it's not readily apparent how to delete what you've entered. Really gets weird via a Web browser.
Just seems like there's a better way. I will keep searching and reading my missing manual book :-)
Well, one has to learn to walk before they can run. I called that demo "basic" for a good reason: it purposefully lacks anything that is not essential to the fundamental principle. However, even in this rudimentary implementation you cannot "go to the last column and delete the group name" - because the field is not enterable.
Once you understand the basic principles you can add more features to it (e.g. prevent duplicate joins) and build the user interface in whatever way you wish. I have already mentioned one such alternative in message #4 above.