By the way, if I add the MemberID to the Event Attendance table, it does pull up that person's name and organisation. But I don't want to have to look up each event attendee's MemberID in order to put them in the database.
If you open Manage | Database | Fields, you can find the serial number field, double click it and then edit the next serial value setting should you desire to do so. But in terms of how your database works, this change is not necessary. It should work just fine even though your new events have ID numbers "much higher" than those that you imported.
What database design are you using? Is this a starter solution file? Have you modified the design? What version of FileMaker do you use? (FileMaker 12 starter solutions are quite different from ealier versions.)
If you are using a DB of your own design, you'll need to describe that design for us.
In general, you don't enter a name and have it look up an ID without some scripting to support the process. That's because names are not unique. You might easily have two or more people with the same name and then you need the ability to detect that and then present the user with a way to choose between identical names. Relationships are usually based on a serial number field that is unique to each individual. Simple fileMaker designs typically use a value list that presents the user with a list of names, but when they select a name, the corresponding ID number is entered into the field.
I have a demo file that uses an auto-complete enabled value list of names with a script to handle duplicates, that looks up ID numbers, but I need to know the basic design of your database first before I know how much effort is needed to adapt the methods used to your particular project.
Thanks, PhilModJunk. I'm not using a start solution file, this is my own 'design,' though it would probably be charitable to describe it like that. I was a total novice, read much of 'FileMaker Pro 11 the missing manual' and then cobbled something together.
I'm not quite sure how to describe my design, but I basically have three related tables: the Member table includes name, organisation, email address, notes and MemberID; the Events table includes event name, location, and EventID; and the Event Attendees table includes MemberID, EventID, and members' names and organisations. The Members and Events tables are both parent tables and the Event Attendees table is the child.
Does that make sense?
What you describe sounds like you have this:
Member::MemberID = Event Attendees::MemberID
Events::EventID = Event Attendees::EventID
If my notation is unfamiliar, see this thread: Common Forum Relationship and Field Notations Explained
The easiest way to add an event attendee to an event is to place a portal to Event Attendees on your Events layout. Fields from Member can be included in the portal row to provide member names and such.
The simple way to select an existing member record for this event is to:
Enable "allow creation of records via this relationship" for Event Attendees in the Event to Event Attendees relationship.
Add the Event Attendees::MemberID field to the portal row and format it as a drop down list or pop up menu.
Specify a value list for this field that lists MemberID values from the Member table as the first (primary) field and a member name field from the same table occurrence for the secondary field. You may need to set up a special full name field for this that combines first and last names. You can specify that the value list hide the primary field and/or sort values by the secondary field, but if you do, you should use a text field with an auto-calculation to combine first and last names so that you can put a "unique values" validation on this full name field as the value list will then drop out values for duplicate names.
Selecting a member from this value list will create a new related record in Event Attendees that is linked correctly to the current Event record and the selected member record.
You may find this demo file--which just so happens to use and Event Manager as its basis, useful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7