Show one specific related record in a one-to-many relationship

[Database Administrator]

This is possibly a stupid question from a newbie, so I apologize in
advance if that is the case. I've tried to read documentation for a
clear answer but haven't found any, and all the solutions that come to
mind are hacks.

Suppose I have a relatively simple one-to-many relationship between
two tables, People and Phones. Each record in People can have several
related records in Phones: People has an ID, Phones has an ID and a
People_ID to link to People. So, for example,

People has a record for a person with ID = 1
Phones has three records, IDs = 1, 2, 3, all of which have People_ID =
1. Let's say Phones has a "Type" field, so these three phones have the
types "Home", "Work" and "Cell" respectively.

I understand I can just add a Portal to a presentation of People, so I
can see that person's phone numbers easily; but (and here, finally,
comes my question) how can I have a single field in the presentation
that shows, for example, the related record that has "Cell" as its
type?

If I was using SQL I guess it would be a SELECT with the current
person's ID as People_ID and a hard-set "WHERE Type="Cell"", but how
do I do this in Filemaker elegantly?

Thanks in advance, and sorry again if this sounds retarded

[Database Administrator]

On 2008-11-13 11:34:30 -0800, Pedro Gómez-Esteban
said:

> This is possibly a stupid question from a newbie, so I apologize in
> advance if that is the case. I've tried to read documentation for a
> clear answer but haven't found any, and all the solutions that come to
> mind are hacks.
>
> Suppose I have a relatively simple one-to-many relationship between
> two tables, People and Phones. Each record in People can have several
> related records in Phones: People has an ID, Phones has an ID and a
> People_ID to link to People. So, for example,
>
> People has a record for a person with ID = 1
> Phones has three records, IDs = 1, 2, 3, all of which have People_ID =
> 1. Let's say Phones has a "Type" field, so these three phones have the
> types "Home", "Work" and "Cell" respectively.
>
> I understand I can just add a Portal to a presentation of People, so I
> can see that person's phone numbers easily; but (and here, finally,
> comes my question) how can I have a single field in the presentation
> that shows, for example, the related record that has "Cell" as its
> type?
>
> If I was using SQL I guess it would be a SELECT with the current
> person's ID as People_ID and a hard-set "WHERE Type="Cell"", but how
> do I do this in Filemaker elegantly?
>
> Thanks in advance, and sorry again if this sounds retarded

Not retarded at all, and it is something we have to do all the time.
Show all the invoices not paid yet. Show the to-do records with dates
later than today, etc.

In your case, I'd make several of what I call "flag" fields in the phone table.

fCell is a calc (can be auto-enter, or just a stored calc) that says

Case(Type = "Cell", 1, "")

Now, in your person file, make a "Constant" field, a calc which is 1
for each record.

Now, use the multi-predicate relationship capability of FM. Put a new
Table Occurence of the Phone table on the graph, and connect TWO
fields, not just the KeyPerson.

KeyPerson = KeyPerson and...
Constant = fCell

Now, you will see only the phone records marked Cell for that person.
Do the same with a flag field for fHome and fWork, and repeat the above
relationship for each.

Another option, but one which is not robust, is to always create your
Home, Work and Cell numbers in the same order. Then you can use the one
relationship between key fields and GetNthRecord to display what you
want. But if one number is missing or out of order, you'll get
unreliable results.
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA