Using FMP 7 on Mac OS X (10.3.9).

I have various tables, including "contacts," "activities," and
"attendance." I would like to create a form that would let me enter
attendance records thusly:

1. enter a single activity ID (one time only)

2. select a contact ID from a drop-down

3. once I select a contact ID, automatically provide another drop-down
to select another attendee from the contacts table

4. for each record I enter, also provide a checkbox for specifying
whether the attendee is a facilitator

The idea is that I can select the event, then select multiple
attendees, and specify who the facilitators were.

I'm not sure what information about the tables might be needed, but the
attendance table contains three fields: contactID (auto-populated in my
current form), eventID (auto-populated in my current form), and
facilitator (checkbox (yes) in my current form).

I'm a FMP newbie and I don't even know what to google for to figure
this out!

Thanks much,

Todd

In article <1166470081.945082.240080 (AT) 48g2000cwx (DOT) googlegroups.com>,
"DonTodd" <dontodd (AT) gmail (DOT) com> wrote:

In order to record attendance like this, you need a many-to-many
relationship of contacts to events, as each contact will attend many
events, and each event will have many attendees. To do this you need an
intermediate Join table, which I will call Attendance. The attendance
table will have separate records for each assignment of a contact to an
event.

The Contact table will have fields for the data peculiar to each
contact: Name, address, phone, etc, and of course a unique identifier
kpContactID, which I would make a computer-generated serial number than
can not be modified by the user.

The Event table will have fields for data peculiar to the event, such as
name, date, time, place, etc, and of course a unique serial-number
identifier kpEventID.

The Attendance table will have fields for kfContactID and kfEventID, and
a field for assigned function of the person at the event, plus other
fields as desired for data peculiar to the assignment of one contact to
one event.

The relationship structure would be as follows:

Contact::kpContactID = Attendance::kfContactID

Event::kpEventID = Attendance::kfEventID

(the notation Contact::kpContactID means the field kpContactID of the
table Contact, or more precisely, the Table Occurrence Contact)

I would allow creation of Attendance records by way of both
relationships, and put portals to Attendance in both the Contact and
Event layouts.

In the portal in the Contact table I would show the fields
Attendance::kfEventID, Event::EventName, Attendance::AssignedFunction
and any others desired.

I would format the field Attendance::kfEventID in that portal as a
drop-down list, with values taken from fields of the Event table, first
field Event::kpEventID, second field Event::EventName, sort by second
field, show second field only.

That way, I can create an assignment of a contact to an event by
clicking in the kfEventID field fo the portal, selecting the appropriate
event form the list, and then going to the Assigned Function field and
assigning the function (facilitator, etc). You might want a value list
of Functions for te Assigned Function field.

You could create a similar portal in the Event layout, but the fields in
this portal would pertain to the contacts, not to the events. You could
have a corresonding value list for ContactID as was done in the other
portal for EventID.

See the on-screen FileMaker help file for details on how to create
relationships, layouts, portals and value lists.

--
For email, change <fake> to <earthlink>
Bill Collins

Bill,

Thanks for your thorough response. Responses inline.

Bill wrote:

Yup, if I wasn't clear, this is what my current attendance table does,
though these are not auto-populated as I wrote before. The table has
three fields: contactID, eventID, and role.

Sounds good, I'm using an ID pulled from our institutional database.

Sounds good, more or less what I have.

Exactly.

If I understand this correctly, this is how I have it set up in the
Define > Database > Relationships part. I clicked and dragged from
Contacts::ContactID to Attendance::ContactID, and likewise for the
Activity table and ID (I called mine activity instead of event).

OK, this is the part I get lost. I need to read more on creating these
portals and formatting them. But from what you're saying, this will
make it "human-readable" since I'd include both the EventID *and*
EventName.

Will do, thanks again!

Todd