Hi,
We are working on a set of related databases. One database contains
People records, and each person has a distinct Personal ID. Another
database contains numbered codes unique to each person. There are
multiple codes per person, and the record for each code contains that
person's Personal ID. All the codes are important, but only one is
considered the "best," per person.

In the People database, we are trying to display only the best code
for that person. Right now there is a direct link to the Codes
database (related by Personal ID), and a calculation field in People
displays a single record from Codes.

An example: There are three codes for a particular person. In the
calculated field in People, only one code appears, and it's not the
one we want. Does the database *see* the other two records, but
chooses not to display them? How does the field decide which of the
three records to display?

We have tried sorting the records within the Codes database, and we
have tried modifying the codes to figure out Filemaker's selection
criteria, but there is no apparent difference. Creating a portal in
People displays all three codes for that Personal ID (which we can
sort), but we're not sure how to extract the code from only the first
record in the portal.

Suggestions? Any help is greatly appreciated.

You're limiting the display to one of many related Codes. FM is going to display the first one that satisfies the calc.

On the surface, it sounds to me like you need to create more than one relationship, based on multiple criteria.

What is the calculation?

Matt

On 03/09/2007 13:31:20 rocketo (AT) gmail (DOT) com wrote:

There isn't one, right now. A unit code within a range of
0202000-0208000 is considered most important, followed by other codes.
The problem is right now it can't tell the difference between them, so
I can't *set up* a calculation. How can I get records in a database to
see each other?

On Mar 9, 4:07 pm, Matt Wills <I... (AT) witz (DOT) end> wrote:

There isn't one, right now. A unit code within a range of
0202000-0208000 is considered most important, followed by other codes.
The problem is right now it can't tell the difference between them, so
I can't *set up* a calculation. How can I get records in a database to
see each other?

On Mar 9, 4:07 pm, Matt Wills <I... (AT) witz (DOT) end> wrote:

Take a look at "Creating Relationships" in Help.

Matt

On 03/12/2007 11:40:16 rocketo (AT) gmail (DOT) com wrote:

On Mar 9, 12:31 pm, rock... (AT) gmail (DOT) com wrote:
How can you tell which code is the 'best'? Is there a checkfield in
Codes for best? If not, create a field for determining which code is
'best', say CodeSort and assign each code a value of 1,2, or 3 (or
just '1') but then you'll need to sort in the next step by
"Descending"). Then in Relationship dialog (in Define Database)
choose the option to sort records in the Code table based on the
CodeSort field.


G