3 Replies Latest reply on Feb 19, 2015 7:35 PM by MacDevGuy

    Tracking attendance at multiple events over time

    FlorenceMiller

      Title

      Tracking attendance at multiple events over time

      Post

           I'm new to Filemaker Pro. So far I've managed to create a database for the membership of a professional network I manage, giving each member a MemberID key.

           I have an Excel spreadsheet indicating which members attended our many events over the years. I cannot for the life of me figure out how to get this information into my database. I've figured out that this is a many to many relationship (each member has been to multiple events; each event is attended by multiple people). I've given each event an EventID key, with the idea that somehow I can create a join table. (I'm including a screen shot of that EventID and MemberID table in Excel.)

           But that's as far as I can get. I'm sure I'm missing something really obvious; I just can't figure out it with my limited knowledge of Filemaker Pro, and the Missing Manual I'm using doesn't give me enough info.

           Thanks for any guidance.

      Screen_Shot_2012-09-20_at_2.15.30_PM.png

        • 1. Re: Tracking attendance at multiple events over time
          davidanders

               Google "event join attendance site:forums.filemaker.com"      Threads on these forums
          https://www.google.com/search?q=event+join+attendance+site%3Aforums.filemaker.com

               LINK#
          http://forums.filemaker.com/posts/404122a451?page=1

               Very informative thread with deSaint providing great help and demo databases for download that evolve over time

          • 2. Re: Tracking attendance at multiple events over time
            philmodjunk

                 You need three tables:

                 Members----<Member_Event>------Events

                 Members::MemberID = Member_Event::MemberID
                 Event::EventID = Member_Event::EventID

                 The structure of the data in your spreadsheet presents a problem for getting your data in the correct place.

                 For Events, you can import this spreadsheet once and import just the first column into your events table.

                 For members, you can either import this spreadsheet once for each column of IDs or you can import it once into a temporary table and use a script to work with each column of data in the temporary table to build the table of members. You can set a Unique values, validate always validation rule on your Members::MemberID field and this will automatically filter out duplicate IDs during the import.

                 Forr Member_Event, you can do much the same, either import once for each column of member ID's or use the temp table approach. Using the one import for each column of members, you'd always map the first column to EventID, but map the MemberID field to a different column in the spreadsheet each time.

                 Once you have the data imported, see either DavidAnder's links and/or this demo file for ideas on how to work with the many to many relationship:  https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                 It's strictly a fortunate coincidence that this demo file happens to be set up to match contacts to events.

            • 3. Re: Tracking attendance at multiple events over time
              MacDevGuy

              Phil, 
              I need to do something similar but by year only. I just want to know the year someone came on a trip.

              So using your example then I am guessing I would do:
              Members----<Member_Years>-----Years

              I want to track camping trips which usually occur over the summer by the year(s) someone has attended the campouts. My challenge is having something that allows me to print letters/labels to "all campers who came and camped in 2012" (i.e.).