2 Replies Latest reply on Jan 21, 2014 2:24 PM by MichelleMedeiros

    How to autopopulate field in join table

    MichelleMedeiros

      Title

      How to autopopulate field in join table

      Post

      My database background

           I've used databases i.e. added fields and designed queries for dBase and MS Access.

           This is my first experience designing a database. So far I've spent a week fumbling around with the design of a database in FileMaker.

           While there are posts in the forum that seemed to answer my question, I didn't understand the responses.

            

      My Design Objective : Record which activities are attended by which contacts.

           Many activities and Many contacts are joined through a table called Who's involved.

           Many activities calculates an Activity ID = Title & Date & Time

           Many Contacts calculates a Contact ID = First Name & Last Name

            

      My challenges:

           An Activity ID is calculated in Tutu's House Activities I would like the Activity ID to magically appear in the Who's Involved join table. The magic could happen once a month. Can this be done? How do I make it so?

            

           After the Who's involved table is autopopulated with Activity IDs for the month, the Activity ID will recur  in the Who's Involved Table because multiple Contact IDs will be associated with a unique Activity ID. Which is the best way to enter the recurring Activity ID: lookup value or copy/paste?

            

      Screen_Shot_2014-01-21_at_10.08.38_AM.png

        • 1. Re: How to autopopulate field in join table
          philmodjunk
               

                    Many activities calculates an Activity ID = Title & Date & Time

               

                    Many Contacts calculates a Contact ID = First Name & Last Name

               You would be much better off using an auto-entered serial number or auto-entered Get (UUID) for your two ID fields. The calculated ID's that you are using can create problems for your database that are avoided by using one of these options for an auto-entered ID.

               

                    An Activity ID is calculated in Tutu's House Activities I would like the Activity ID to magically appear in the Who's Involved join table. The magic could happen once a month. Can this be done? How do I make it so?

               For a many to many relationship like this, it makes no sense to do that. Creating a record in Who's Involved with a specific Activity ID is not needed except for that specific group of contacts that will be involved in that activity. This can happen automatically as part of the process of selecting specific contacts for a given activity. You need not have it happen on a scheduled basis as this would populate the join table with many unused and disconnected join table records.

               The simplest way to manage a many to many relationship is to use a portal to the join table. For example, you can put a portal to Who's Involved on your Activities layout and then can use the Who's Involved::Contact ID field to select contacts for that activity. If you enable "allow creation of records via this relationship" for Who's Involved in the Activities to Who's Involved relationship, simply selecting a contact for this field will automatically copy over the Activity ID field's value and link the new join table record to the current Activity record.

               You can also turn this around and place a portal to Who's Involved on the Contacts layout and use the Who's Involved::Activity ID field to select activities for a given Contact record.

               Here's a demo file which coincidentally links Contacts to Events. You can examine it to see a working example of what I am describing as well as some examples of other tools for working with a many to many relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

               If you are using FileMaker 12 or newer, you can use Open from the File menu to open this file and produce a copy converted to the newer .fmp12 file format.

          • 2. Re: How to autopopulate field in join table
            MichelleMedeiros

                 Mahalo Phil. Your "simplest way" looks like a good fit.

                 I'll have to learn how to create portals etc.

                 Thanks for the guidance.