Here is my situation:

In Database A, I have a database of students (Field: Full Name). For
each student, I indicate in another field (Field: Status), whether the
student is "Active" or "Not Active" (I made a value list with these two
custom values to choose from).

In Database B, I would like to be able to assign students to a test
using a related student field in the form of a check box set. I made
another field (Field: Students), whose values come from a value list
based on the "Full Name" field from Database A. All of the values show
up in the checkbox set. However, I only want to display the students
from Database A who have "Active" as their Status. Is there a way of
doing this?

Thanks
-Frank

Yes.

Create a second instance of the table in the relationships graph and relate
it to your table in the usual way. Add the "Status=Active" criteria to this
second relationship. Now base your value list on this second table instance
and you'll see just active students.

Bill

<FrankieNap (AT) gmail (DOT) com> wrote

Just to make sure that I got this right, I should make a second
instance of Database A, rerelate the two fields to the corresponding
ones in Database B, and then set this criteria? How do I set the
criteria "Status=Active"? Thanks a bunch Bill

What version of FileMaker are you using?

Bill

<FrankieNap (AT) gmail (DOT) com> wrote

I'm using Filemaker 8

Frank,

Ok. It's best to start using "table" because database is a little ambiguous,
in earlier versions of FileMaker they were pretty much synonymous, but in
FileMaker 7 and later it generally means the whole file, with multiple
tables.

Ok, let's start with where you're at.

You have two tables in your database:

Students
Last Name
Status

Tests
Students

You've set up two value lists:
StatusType (Active, Not Active)
StudentList (based on Last Name in Students table)

I re-read your original post and I see that you could have set this up
without defining any relationships at all. FileMaker will use any field in
any table as the basis for a "generic" value list. However, you want a value
list that is "filtered" to show only the active students... and this will
require a relationship.

Just looking at the fields you've described, there's no obvious connection
between the two tables as yet. You did not have to relate "Students" to
"Last Name" in order to set up the value list for Students. (And, if you
did, it wouldn't be very useful.) A key question is whether you've related
these tables already or not. Let's assume you have not.

Create a new field, TestAudience, in Tests. If all tests are only for active
students, then you may set this field as a calculation (text result) =
"Active" otherwise you can set it up the same as Status in Students, using
the StatusType value list and auto-entering a value of "Active" for new
records.

Once the TestAudience field is created, you can relate Students::Status to
Tests::TestAudience. Just click on the one field, and drag it to the other.
A line will appear to connect them. An equals sign will appear in the label
for the line.

A relationship is like a "view" into another file, or a "filter." In this
case, When TestAudience in Tests is "Active" then that record will be
"connected" or "related" to ONLY the records in Students that have "Active"
in the Status field. [And, if it is possible to have a test that only for
"Not Active" students, then a "Not Active" in Test::TestAudience will be
connected only to the records that have "Not Active" in Students::Status.]

The important thing is you've got a line between these two tables and can
now use a value list "filtered" by them. Here's how to use it:

1) Go into layout mode and right-click the checkbox field you already set
up.
2) Choose Field/Control--> Setup. The Field/Control Setup dialog appears.
3) Go into the popup for Display values from: and choose "Define Value
Lists...." The Define Value Lists dialog appears.
4) Click "New...." The Edit Value List dialog appears.
5) Select "Use Values from Field:." The Specify Field dialog box appears.
a) Choose the Students table from the drop-down menu.
b) Select the Full Name field.

This is very similar to what you did before. Except now comes the
different part.

c) Select "Include only related values starting from..."
d) Once you click that radio button, the drop down menu is active. Choose
"Tests."

"Tests" looks and smells like a table, but in this situation it is really
a "table occurrence" that defines the end point of a relationship -- the one
you defined by connecting the two status fields. In other words, it won't
use all the student names, just the ones that meet the relationship criteria
of Students::Status = Tests::TestAudience.

6) Click OK four times to confirm all these choices and return to layout
mode.

7) Go to Browse mode. If you used a calculation for TestAudience and have
existing records, you should see the effects immediately. Otherwise you'll
need to fill in TestAudience with "Active" to see the results: The Students
check box list is now filled only with students who are Active.

Relationships are a little confusing at first, and this is probably not the
textbook example of the first relationship you would create. But hopefully
it gives you a good idea of what they can do.

Bill

<FrankieNap (AT) gmail (DOT) com> wrote