Re: FIND across multiple tables in FM 7.03
- From: Bill <bbcollins@xxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 25 Aug 2005 21:34:34 GMT
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
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.
.
- References:
- FIND across multiple tables in FM 7.03
- From: David Robins, MD
- FIND across multiple tables in FM 7.03
- Prev by Date: Re: Displaying age from birthdate
- Next by Date: Archive Record Fm6 v Fm7
- Previous by thread: FIND across multiple tables in FM 7.03
- Next by thread: Re: FIND across multiple tables in FM 7.03
- Index(es):
Relevant Pages
|
