Re: FIND across multiple tables in FM 7.03

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

.

Relevant Pages

  • Re: FIND across multiple tables in FM 7.03
    ... > That said, what I have is a table for Exams, and a table for Surgery. ... > 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)
  • Re: Calculated Value will not store in Table
    ... >So, if we have Customers, and Invoices tables, a left ... So, our main table Customers ... >tblBgroup (booking group) for example may, ... >The tables that MUST have a child records can also ...
    (microsoft.public.access.forms)
  • Re: Word merge pulls from wrong table column
    ... If you need that text from the other table, then base your form on a query, ... So, if we have Customers, and Invoices tables, a left join would give us: ... Only include rows where the joined fields from both tables are equal ...
    (microsoft.public.access.forms)
  • Re: Word merge pulls from wrong table column
    ... If you need that text from the other table, then base your form on a query, ... So, if we have Customers, and Invoices tables, a left join would give us: ... Only include rows where the joined fields from both tables are equal ...
    (microsoft.public.access.forms)