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.
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.
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.
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!
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!
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.