3 Replies Latest reply on Apr 11, 2014 11:30 AM by philmodjunk

    help creating records in related tables, pls

    FranziskaBlome

      Title

      help creating records in related tables, pls

      Post

           Hello, I'm stuck with a many-to-many relationship database (thank you PhilModJunk for getting me on the right track earlier!).

           I have a "contacts" table, an "events" table and a "roles" table joining the two. My contacts are playing different roles at certain events. Using portals I can see events records in my contacts layout and vice versa. So far so good.

           Now I fail at setting these portals up to create records in the other table. In the "edit relationship" dialogue box, I selected "allow creation of records in this table..." on the join table side of the equation. Now I can EITHER create roles in the portal on the events layout OR I can create roles in the portal on the contacts layout, NOT BOTH (?).

           What's worse, I can't create contact records from within the events layout or vice versa, events records from within the contacts layout. If I create a record in roles, can't that automatically add information or a record in either events or contacts. I am completely lost. Does my question make sense? If I want to add a new event and list inside this new event record who contributed to this event in what role, I want that info to go straight into my contacts layout. Is that even possible??

           Sorry for this very novice (not to call it stupid) question, but I'd really really appreciate your help. THANKS!

            

            

      contacts_events.png

        • 1. Re: help creating records in related tables, pls
          philmodjunk
               

                    Now I can EITHER create roles in the portal on the events layout OR I can create roles in the portal on the contacts layout, NOT BOTH (?).

               I don't understand your question. What do you mean by "Both"? Role is a field in the join table and you are using a portal to the join table to assign roles, correct? Can you provide an example of what you want to do, but can't? (Portals to the join table are the most frequently used method, but they aren't the only option you can use.)

               

                    I can't create contact records from within the events layout or vice versa,

               The problem with creating a new contact record from events is that you need TWO new records, the new record in Contacts and a new record in the join table that links this new contact to the current event record and vice versa. While this IS possible by adding a field from Contacts to the events record and entering data into it instead of selecting a contacts ID (allow creation of records must be enabled for contacts in the join to contacts relationship), it's generally a better idea to add a button that a) takes the user to a contacts layout (perhaps in a new window opened for this purpose), b) creates a new contacts record from the data the user enters and c) uses the contactID of the new record to create a new record in the join table that links the new contact to current event record. I would put a button on your layout that peforms a script to accomplish this task.

               Take a look at the buttons with + signs found on the layouts in this demo file: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

               This is in an older file format so users that have FileMaker 12 or 13 will need to use Open from FileMaker's File menu to open this file and produce a copy converted to the newer file format.

          • 2. Re: help creating records in related tables, pls
            FranziskaBlome

                 Wow, thanks PhilModJunk for articulating what I'm trying to achieve.

                 The solution you're suggesting, to take user to the contacts layout in a new window and enter data to be linked to join table is brilliant! Alas, the execution is above my skills. I need to watch more Lynda tutorials on scripting, as - I have to admit - so far I've only created buttons that do just one task. I suppose part of the script should be a find function to see if that first and last name already exist in the contacts database - no clue how to specify just that in a script.

                 As a not very elegant alternative, I ought to just keep it simple and instruct the user to check in the contacts layout if a certain name already exists, then enter it if it doesn't. After that they add a contact, they can then enter that contact's involvement/role in a particular event either in the join "role" table or in the events table. It's more steps for them, but I'm afraid I'm trying to do more than I am skilled to do. It's just frustrating to see (from your sample file (thanks for sharing that!) and from starter solutions) how much more elegant/easier for the user this could be.

                  

                  

            • 3. Re: help creating records in related tables, pls
              philmodjunk

                   If you define a text field with an auto-enter calculation that combines them: FirstNameField & " " & lastNameField, you can set a unique values validation on this field to trap instances where the same first and last name is used a second time.

                   But be careful, it's quite easy to get two different contacts that happen to have exactly the same first and last names.