4 Replies Latest reply on Oct 24, 2012 8:57 AM by philmodjunk

    Key field -- adding new numbers when I imported the first set from an Excel database



      Key field -- adding new numbers when I imported the first set from an Excel database


           I have imported membership and event attendance data from an Excel spreadsheet and now that I'm set up in FM Pro, I'm trying to add new events, abandoning the old Excel spreadsheet. I have three related tables: Members (with MemberIDs, names, affiliations, etc.), Events (with EventIDs, topics, etc) and Event Attendance (with MemberIDs, member names, EventIDs, etc., to show who was at which event). The Event Attendance table links the Members table with the Events table.

           I can't figure out how to do a couple of things:

           1) When I add a new event, FM automatically gives it an EventID that is much higher than the highest one already in the database. As the original field was drawing from an Excel spreadsheet, I can't figure out how to turn it into a key field with an auto-enter serial number that just builds off the last number in the database. Can anyone tell me how to do that?

           2) I can't figure out the easiest way to enter attendees at recent events. If I try to add them into the Event Attendance table using just their names, it does not automatically pull their MemberIDs but creates new MemberIDs for them. How can I tell FM to pull the MemberID that is already in the database?

           Thanks very much for any advice. I know this is Filemaker Pro and I'm not remotely Pro.


        • 1. Re: Key field -- adding new numbers when I imported the first set from an Excel database

               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.

          • 2. Re: Key field -- adding new numbers when I imported the first set from an Excel 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.

            • 3. Re: Key field -- adding new numbers when I imported the first set from an Excel database

                   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?

              • 4. Re: Key field -- adding new numbers when I imported the first set from an Excel database

                     What you describe sounds like you have this:

                     Member----<Event Attendees>-------Events

                     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