Please help this long term Access and SQL Server programmer learning
FileMaker.

I have table One which is the master table. It has a one to many
relationship to table Two. Call field X the key field in table One.
In table Two I want to find the value of field A for the greatest value
in field B which is constrained by a value of field X.

For example, lets say I have a table of research studies with StudyID
as the key field. I have a table of revisions with a revision date and
field X which may or may not have an entry. For StudyID =1 I want to
be able to find the latest non-blank field X.

I know how to easily get the latest revision date but getting field X
has me stumped. I don't know how to write a calculated field in the
master table to get this.

Thanks,

Paul Norris
UCSF

In article <1129313573.090387.318170 (AT) g14g2000cwa (DOT) googlegroups.com>,
pnorris (AT) uorg (DOT) ucsf.edu says...
Assuming I understood your post, your schema resembles:

TableOne_Studies {StudyID}
TableTwo_Revisions {StudyID, Date, X}

Related on StudyID.

And you want to find the related record in table 2 where X is non-blank,
and the date is maximal??

Unfortunately I don't beleive you can define a relationships directly on
'blankness'. (At least you couldn't in previous versions.)

So you need to define a calculation isXset, as: not isEmpty(X), which
will be 0 if X is blank and 1 otherwise. (stored calc and indexed)

Then define a global calc (e.g. globalcalc1) in the mainfile and set it
to equal: 1

Then define a relationship on:

studyid = studyid
globalcalc1 = isXset

and set it to sort by date (descending)

The first criteria establishes the basic relationship
The second filters out all the blank X's
The 3rd puts the related record with the highest date first.

regards,
Dave

Perfect. Thank you.