How can I run a FIND acros multiple tables? The member ID#'s are the link
between the tables.

Seems that when I switch tables to add a FIND request in another table, it
drops the other FINDs.

In article <BF32A518.58645%trashadd (AT) bigfoot (DOT) com>, trashadd (AT) bigfoot (DOT) com
says...
No disrespect intended either way but I'm not sure if your are a
database newbie, or a sql-guy trying to figure out filemaker.

I'm going to assume the latter...

All find criteria must entered within a single window, (and in FM7
against layouts for a single table occurence -- FM6 down only has one
table per file/window).

You *can* enter criteria on related files, but those 'child record'
fields must be placed on the 'parent' layout.

If you're a sql guy trying to do something along the lines of:

select customer.name, customer.address
from customers, invoices
where
customers.id=invoices.id and
invoice.total>100 and
customer.city=new york

then what you need is to ensure that a relationship is defined from
customers to invoices on id, and then put the related invoice total
field on a layout for customers. (drag a field onto the layout, and
you'll see a list of the fields in the current table occurence, at the
top you'll see a drop down list that lets you select related, (and even
unrelated tables), and the list will populate with fields from those
tables. In thise case you'd select inventory, and then total from the
list of fields.

So...

Then create a new find:
set the related invoice::total field to >100
set the city field to new york
perform find
...voila

The found set will be customers in new york who have a related invoice
with total>100.

The field itself in browse mode will display data from the first related
record. (You can use a portal to display multiple related records). But
in find mode, the found set will include any records in the parent file
that would match the criteria according to the join specified in the
relationship. (e.g. typically a simple parent.field = child.field)... in
other words the search for >100 in invoices will search all related
invoices for each customer record with city=new york and return customer
records that match both criteria.

I hope that helps...if you need more, please respond with more detail
about what exactly you are trying to accomplish.

-regards,
Dave

In article <MPG.1d7717a07a890224989cae (AT) shawnews (DOT) vf.shawcable.net>,
42 <nospam (AT) nospam (DOT) com> wrote:

What Dave says is true. I will add that it is possible to script finds
that use multiple tables, as long as they all are related to the Table
Occurrence that is the context of the script. In such multi-criteria
scripted finds, the related fields do not all have to be in the same
layout.

Bill

--
For email, remove invalid.

Actually, I'm not an sql-guy, (but not really a newbie - been using FM since
version 1.0.

That said, what I have is a table for Exams, and a table for Surgery. They
are related by the medical record number (I'm using it as an electronic
medical record.)

I was trying to do a manual FIND (not in a script) of patients in a certain
age range, with a certain eyeglass power (both in the Exam table, and combie
it with a Surgery data field, in the other table. And yes, once you switch
wndows, the FIND goes away.

So for some occasional queries, then, it looks like I would have to pull the
Surgery fields over to the Exam layout as related fields? (in FM 7.03)



On 8/25/05 12:48 AM, in article
MPG.1d7717a07a890224989cae (AT) shaw....shawcable.net, "42"
<nospam (AT) nospam (DOT) com> wrote:

In article <BF3403E0.589B5%trashadd (AT) bigfoot (DOT) com>,
"David Robins, MD" <trashadd (AT) bigfoot (DOT) com> wrote:

Yes, that is true. You can set up a layout in the context of, say, the
Surgery table, and put the desired fields from the Surgery and related
Exam tables in that layout so that you can do the search.

I assume you have either many exam records to one surgery record, or
vice versa. You would then use a portal to display fields from multiple
related records in the layout of the one master record. You can do a
Find in the portal, which will return records satisfying the Find
criteria in any of the related records.

Bill Collins

--
For email, remove invalid.

David Robins, MD wrote:

Yes,
If you don't want to put all the Surgery fields in your Exam data entry
layout then simply create an Exam find layout which has all the Surgery
fields on it.
You could then have a button on the Exam data entry layout which runs a
script that goes to the find layout,
pauses for you to enter data,
finds the records,
and returns to the Exam layout.