What you describe sounds like expected behavior. Creating a new record in one table does not automatically create a new record in another except in very special cases. You'd need to put a script in place to do that.
Why should creating a new Document record automatically create a new "join" table record? Are you adding a new Document record that you want to immediately link to the current character record? (You don't need to create a join record until you need to link a document record to a character record.)
If that's the case, put a portal to Join on your characters layout. You can include fields from documents in the portal row. Enable "allow creation of records via this relationship" for Join in the Characters to Join relationship. Put a button on this layout for adding new document records and linking them to the current characters record that performs a script like this:
Set variable [$CharacterID ; value: Characters::__pk_CharacterID ]
Go To Layout [ Documents ]
Set Variable [$DocumentID ; value: Documents::__pk_DocumentID ]
Go to Layout [Join]
Set Field [Join::_fk_DocumentID ; $DocumentID]
Set Field [Join::_fk_CharacterID ; $CharacterID]
Go to Layout [original layout]
Here's a demo file that contains a version of this script and that also demonstrates some different interface options for working with many to many relationships: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html
Thanks. I should have put some more information in my original post. I did create a portal to the join table in the characters table. I told it to allow creation of records and the field I put in the portal is the join::documentID field.
Here is what I envision. Some documents are already input into the db, but others have not been. When I had a new character, I want to be able to link him to an already input document if that is the proper link, but I also want to be able to add a new document if the document I need has not been input yet. Characters can have links to multiple documents, and documents can have links to mutiple characters.
When I click on the portal in the character layout, nothing pops up. I tried layering another field (documents::document name) on top of the join::documentID field, but this did not work. I did not expect it to because there are more documents in the document table than are "seen" in the join table.
Does my question even make sense at this point, or do I neeed to provide more details about my set-up? I had looked at the file you uploaded earlier, but I will study it better later this afternoon.
I really do appreciate your help.
The demo file shows how to select an existing document from a drop down list or pop up menu and the above script shows how to add a new document record. Keep in mind that the new document record will be completely blank except for the ID number until you enter some data so it will appear blank in the drop down. You must either include fields, such as a document title field from documents in the portal so that you can enter this data for just added/linked document records, or you script should use one method or another to collect that data from you when the script executes. (It can use show custom dialog with input fields to get a document name from you, it can switch to the document layout for you to enter data for the new document....)
Bottom line: you select from a value list to link a character record to an existing document, but run a script and enter data to add and link the character record to a new document record.
Thanks again. I think I am closer to "getting my mind right," but I am still not all the way there. I see the problem I had with my earlier analysis. If a join table had done what I was thinking, every character would be linked to each and every document - clearly not what I need.
I understand (I think) the scripted add button, but I am going to delay adding that button until I understand one more basic issue.
I have added 7 characters and 5 documents to my dummy database, and I have tried to set up my portal to the join table as you did in your "basic setup." In the portal on my character table I have a drop down list for Join::document id and next to id I have a text box for Documents:: document name. My problem is that my drop down list is blank. I know (I think) that I have to add a scripted button to add additional documents via this portal, but I thought I could choose from the existing documents via this dropdown list. Indeed, on your basic setup, the dropdown box shows the character id number and character name.
Having talked novices through what seemed like basic computer issues in the distant and more recent past, I really do apreciate your patience in trying to help me.
You should be able to select existing documents from the drop down. You'll need to look at what you have specified for the value list for this drop down to see the list of existing documents. What options have you specified in Manage | Value lists for your drop down list?
I had join::documentid. After I read your post, I changed it to documents::documentID, and that seems to work. I will keep at it. I may be back with more questions later in the week, but I will be out for at least a day now. I have it working, but I want ton think about it and make sure my understanding of the why and the how are correct.