6 Replies Latest reply on Sep 24, 2009 11:14 AM by philmodjunk

    Tracking members at events



      Tracking members at events




      Working for the first time with FileMaker, would love to have someone set it up for us but we can't afford it, so we're doing it on our own, please be patient and use lay terminology!


      We have a main database where we input our members information, name, address, etc.  With each person's membership i'd like to track which events they've attended and which additional fundraisers they've participated in.  Here's where the problem exists.


      We ask everyone at our events to sign in, then we create an excel sheet for each attendance list.  I'm able to import the list into FM but what i'd like the program to do is search the names on theses lists and imput in the main members database if they were found at that event. 


      A lot of times we have non-members attend the events but i'd like to keep their names in there to keep track of them.


      Is there anyway to create a portal (if that's the appropriate thing to use) to have FM identify first and last names and therefore add the event name they attended? 


      I hope I explained it correctly, please let me know if you need additional information.


      Thank you in advanced.

        • 1. Re: Tracking members at events

          First, a key question: Why the excel spread sheet?


          You can set up all your attendance tracking within filemaker, then you don't have to do any importing in the first place. Simply enter the data directly into a filemaker layout that lists each person.

          • 2. Re: Tracking members at events

            Good question,


            We're currently setting up FM and have never used it before.  In the past we've always entered them into excel so my job now is to import the files and find what events/fundraisers the members have been to in the past. 


            By the way, I bought FM 10 and am running on a windows XP.

            • 3. Re: Tracking members at events

              OK, the crucial next step is correctly designing the tables and relationship in your database. Once you've done that, you should be able to import data from your excel spreadsheet. I suggest you prepare for investing the time in learning more about filemaker databases. There are tutorials and books available. By all means, keep posting questions here, but if you study up on your own, you'll better understand the concepts you'll find discussed in this forum.


              I don't expect you to be able to follow the rest of this post until you learn a bit more about filemaker but here's a general outline of what I think you will need to do:


              You'll need at least three tables: Members, Events, Attendance


              Define fields such as name, address, etc in members so you can record data on each member (1 record = 1 member)

              Define the fields needed in Events to describe each event. (1 record = 1 event)

              Attendance is then a special type of table called a "join" table where one record records the presence of a given member at a given event.


              You'll need relationships that link these three tables. Something like

              Members :: MemberID = Attendance :: MemberID

              Events :: EventID = Attendance :: EventID


              The problem you'll have is that your existing spreadsheets aren't likely to have the needed EventID and MemberID numbers you need to make this work. How clean is the data in your spreadsheets? If the names are entered perfectly every time with no typos, you could use Member and Event names in the above relationships to get things in place to start and then you can update the imported data with serial numbers and then switch over to the serial number based relationships.

              • 4. Re: Tracking members at events
                   OK great.  I feel like i've been researching the heck out of this and only finding options but never knowing which one to use (portal, join tables, if, calculation etc) i've needed to be pointed in the right direction!  Thank you, i'll try your suggestions now!
                • 5. Re: Tracking members at events

                  OK so i've created tables MEMBERSHIP, ATTENDANCE (self-join), and EVENTS


                  The relationships look as such (I have a photo of it but I can't seem to put it in here):


                  MEMBERSHIP             ATTENDANCE             EVENTS

                  First Name     =         First Name

                  Last Name     =         Last Name

                  Member ID     =         Member ID

                                                    Event ID         =     Event ID

                  Event Name   =         Event Name     =     Event Name


                  First off, is this correct?  I need to have the first and last names in ATTENDANCE recognized from MEMBERSHIP thus supplying their member ID.  Then in MEMBERSHIP I need to have the Event name displayed (proving that they're in ATTENDANCE for that EVENT). 


                  ... and now I need more coffee.  This is mind-boggling!

                  • 6. Re: Tracking members at events

                    Remove the membership::eventname field and its relationship link to attendance. You don't need it and it may keep your database from functioning correctly.

                    Then delete the relationship that use memberId and eventID for now. You'll want them eventually, but you'll need to use the name based relationships during initial imports of your existing data. After you've got all the data imported and cleaned up, you'll remove the name based relational links and then put the ID field based links in their place.

                    Add an address field to membership and attendance and add the fields to the relationship linking Membership to Attendance


                    When you double click the relationship line, you should see:


                    First Name = First Name AND

                    Last Name = Last Name AND

                    Address = Address


                    The address field will help distinguish between two people with the same first and last name.


                    The big challenge here is going to be importing the data. First, you'll need to examine your data and see how "clean" it is when it comes to names.


                    Do you see a lot of typographical errors in the first name, last name and event name columns?

                    Do you see two or more individuals with the same first and last names?


                    If there are few errors, you may be able to import the spread sheet data into your tables and then look for and fix the small percentage of records that fail to match records in another. If there are a lot of typos, you may need to spend considerable time cleaning up the spreadsheet data either before or after import.


                    Then you may be ready to import data. Here's a method for loading the Membership table with membership data from multiple spreadsheets.


                    Define a text field in Membership and set it to auto-enter the following calculation:

                    First Name & Last Name & Address  /* address will help distinguish between different people of same name */

                    Give it a validation: Unique Value; Validate always.


                    Now, starting with your most recent spreadsheet, use import records to import from your spreadsheets into your membership table.

                    Select your membership layout and select Import Records.

                    Select your file and map the spreadsheet columns to your membership fields. Don't worry about the event data in the spreadsheet. You'll import data from this same file into events and Attendance later.

                    Import the records, making sure you enable the auto-enter options by selecting that checkbox.


                    How it works: The validataion rule we established will prevent importing a record if the combination of first, last and address fields has previously been entered. Those subsequent, duplicate rows will be skipped. I'm suggesting you import the most recent data first so that most recent contact data (other than name and address) will be what is imported and older obsolete data will be filtered.