Hi folks,

I'm having a serious problem grasping how Filemaker deals with Finds in
related fields and portals. I've searched a bit in the NG and not found
a solution.

I'm using FM 7 and have a situation as follows:

Contacts table
Fields: ContactID, name & address fields, CurrentEvent (Global)

EventAttendance table
Fields: ContactID, EventID, CameToEvent (yes/no)

Events table
Fields: EventID, EventName

....basically the database tracks names & addresses of people who came
to different events. Each event has its own EventID, and the
EventAttendance table links people to events.

I have two relationships set up from the Contacts table to the
EventAttendance table. One is called CurrentEvent. It links ContactID =
ContactID and CurrentEvent = EventID.

The other is called NotCurrentEvent. It links ContactID = ContactID and
CurrentEvent <> EventID.

When I put the related field CurrentEvent::CameToEvent on my main
layout (which uses the Contacts table) it displays perfectly. It shows
the CameToEvent value in the EventAttendance table only for the current
event. When I enter data in that box, Filemaker creates a related
record in the EventAttendance table with the correct EventID. The user
doesn't even know he/she is entering data in a related table. This is
all great.

The problem:
When I try to perform a Find using that field, it finds ALL people who
have a value in CameToEvent, including people who came to past events
but not the current one. In other words Filemaker does not seem to care
what relationship I am using, it just does the find on the table
itself. Suddenly the illusion of "I'm in one table" is broken and the
user has no idea why people are showing up who shouldn't.

This is rather frustrating! Part of the reason I tried this approach
was to get around the problem I always experience doing Finds in
portals, i.e. that portal finds are treated as "OR" rather than "AND"
finds as customary in FM.

I want the user to be able to do a find in the CameToEvent field using
the CurrentEvent relationship so that, by typing into one field, they
can find who will come to _just the current event_.

I can probably do this using a calculated field in the main table, but
I am planning to expand the EventAttendance table with more fields. It
seems very cumbersome to create new fields twice, once in the related
table and once to be able to do Finds on them. (Moreover as far as I
can see, this would not solve the related problem of wanting to do
Finds on the [one-to-many] NotCurrentEvent relationship without
including records from the current event.) I'm hoping there's a simpler
way, and I'd be much obliged to anyone who could help.

Thank you,

Matthew Furrow
mfurrow at mail dot md

In article <1161660005.854797.301260 (AT) i3g2000cwc (DOT) googlegroups.com>,
caradhras (AT) my-deja (DOT) com wrote:

It's a bit difficult to follwo, but I think you'll find that you're
misreading the Find's results.

If you try to Find on CurrentEvent::CameToEvent in the Contacts table,
then Filemaker will go away and find all the Contacts that have a
related record with that date ... BUT, what you see on screen is the
Contact's record and their FIRST related event record's date (what
"first" actually is depends on how the relationship is sorting related
records), which may or may not actually be the same one you were
looking for. These people did still attend that event, it just isn't
the first in the related table.

What you should really do is perform the Find in the EventAttendance
table. In that Table you can have a "reverse" Relationship that links
back to the Contacts table to retrieve the person's details (name,
etc.) and another one to the Events table to retrieve the Event details
(date, etc.).


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)