Re: FIND across multiple tables in FM 7.03

In article <BF3403E0.589B5%trashadd@xxxxxxxxxxx>,
"David Robins, MD" <trashadd@xxxxxxxxxxx> wrote:

> 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)
>

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

>
> On 8/25/05 12:48 AM, in article
> MPG.1d7717a07a890224989cae@xxxxxxxxxxxxxxxxxxxxxxxxx, "42"
> <nospam@xxxxxxxxxx> wrote:
>
> > In article <BF32A518.58645%trashadd@xxxxxxxxxxx>, trashadd@xxxxxxxxxxx
> > says...
> >> 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.
> >
> > 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

--
For email, remove invalid.
.

Relevant Pages

  • Re: FIND across multiple tables in FM 7.03
    ... Surgery fields over to the Exam layout as related fields? ... > customers to invoices on id, and then put the related invoice total ...
    (comp.databases.filemaker)
  • Re: FIND across multiple tables in FM 7.03
    ... > fields must be placed on the 'parent' layout. ... > customers to invoices on id, and then put the related invoice total ... (You can use a portal to display multiple related records). ...
    (comp.databases.filemaker)
  • rebirth of blocks?
    ... \ such line separates blocks in a group ... a block is a set of words of the same sort, ... And yes, I did not have to give it to any customers, it was ... It would kill all information saved in the layout, ...
    (comp.lang.forth)
  • Re: Buying blind
    ... old son was keen to aquire something to run on "our" model railway. ... As the layout is a small dockside setting, we had established that nothing larger than a six coupled tank engine would be apropriate, so we were down to deciding between a Hornby BR liveried Pug or one of the new 08s. ... Since my return to model railways in the past few years I have found a disturbing number of shopkeepers who hold their customers in complete contempt. ... Should I just be more thick skinned and accept that customer service is no longer part of the job? ...
    (uk.rec.models.rail)
  • Re: Layout - set to display a specified group only.
    ... INVOICES table. ... When I look at this layout as a list I get all the ... I know I could do this through a script but then I would always ... Create a Global field. ...
    (comp.databases.filemaker)