Find record w/ conditions met in the same child record

[Thomas Berg]

I'll use 2 files to demonstrate my question.

The first file is Animal. It has 4 fields.
Id (serial number)
ZooId (number)
Name (text)
Food (text)

The second file is Zoo. It has 1 field.
ZooId (serial number)

Zoo also has a relationship, Animal. I bet you can guess what it is!
ZooId = Animal::ZooId

There is one record in Zoo. It has two related records in Animal. The
values for Name and Food are
dog bone
cat tuna

Zoo has a layout with a portal based on Animal in it. Animal::Name and
Animal::Food are displayed in the portal. If I go into find mode and enter
"dog" and "bone" in the first row of the portal (the only row available in
find mode), I hit the record. That's good. If I enter "dog" and "tuna", I
still hit the record. That's a reasonable way for things to work, but it's
not what I need right now. I only want records where the same child record
matches all the criteria.

I have 2 ideas, neither of which I like.

One is to add a calc field to Animal. NameFood = Animal & "_" & Food, and
do my find on that. The find will be scripted, so I'm not worried about
this being weird for the user. But in the real database that I'm working
with, it's a lot more than two fields involved, and a concatenation would go
over FM's 60 meaningful character limit.

The other is to do the find in the child file, then loop through the found
set, creating a find request (using ZooId) in the parent for each record.
I'm not confident about FM's performance of a find with, say, 300 find
requests.

Does anyone have another approach? Or is my second idea not as unpleasant
as I think it is?

Thanks

Thomas Berg

[Howard Schlossberg]

You should take a look at the TechInfo article at
[url]http://www.filemaker.com/ti/105843.html[/url]

Thomas Berg wrote:

> I'll use 2 files to demonstrate my question.
>
> The first file is Animal. It has 4 fields.
> Id (serial number)
> ZooId (number)
> Name (text)
> Food (text)
>
> The second file is Zoo. It has 1 field.
> ZooId (serial number)
>
> Zoo also has a relationship, Animal. I bet you can guess what it is!
> ZooId = Animal::ZooId
>
> There is one record in Zoo. It has two related records in Animal. The
> values for Name and Food are
> dog bone
> cat tuna
>
> Zoo has a layout with a portal based on Animal in it. Animal::Name and
> Animal::Food are displayed in the portal. If I go into find mode and enter
> "dog" and "bone" in the first row of the portal (the only row available in
> find mode), I hit the record. That's good. If I enter "dog" and "tuna", I
> still hit the record. That's a reasonable way for things to work, but it's
> not what I need right now. I only want records where the same child record
> matches all the criteria.
>
> I have 2 ideas, neither of which I like.
>
> One is to add a calc field to Animal. NameFood = Animal & "_" & Food, and
> do my find on that. The find will be scripted, so I'm not worried about
> this being weird for the user. But in the real database that I'm working
> with, it's a lot more than two fields involved, and a concatenation would go
> over FM's 60 meaningful character limit.
>
> The other is to do the find in the child file, then loop through the found
> set, creating a find request (using ZooId) in the parent for each record.
> I'm not confident about FM's performance of a find with, say, 300 find
> requests.
>
> Does anyone have another approach? Or is my second idea not as unpleasant
> as I think it is?
>
> Thanks
>
> Thomas Berg
>

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California
Associate Member, FileMaker Solutions Alliance

[Marc-Andre Paiement]

"Thomas Berg" <tbSPAMISBADerg@insight-system.co.jp> wrote in message
news:bn2b11$fm6$1@nn-tk103.ocn.ad.jp...

> I'll use 2 files to demonstrate my question.
>
> The first file is Animal. It has 4 fields.
> Id (serial number)
> ZooId (number)
> Name (text)
> Food (text)
>
> The second file is Zoo. It has 1 field.
> ZooId (serial number)
>
> Zoo also has a relationship, Animal. I bet you can guess what it is!
> ZooId = Animal::ZooId
>
> There is one record in Zoo. It has two related records in Animal. The
> values for Name and Food are
> dog bone
> cat tuna
>
> Zoo has a layout with a portal based on Animal in it. Animal::Name and
> Animal::Food are displayed in the portal. If I go into find mode and

enter

> "dog" and "bone" in the first row of the portal (the only row available in
> find mode), I hit the record. That's good. If I enter "dog" and "tuna",

I

> still hit the record. That's a reasonable way for things to work, but

it's

> not what I need right now. I only want records where the same child

record

> matches all the criteria.
>
> I have 2 ideas, neither of which I like.
>
> One is to add a calc field to Animal. NameFood = Animal & "_" & Food, and
> do my find on that. The find will be scripted, so I'm not worried about
> this being weird for the user. But in the real database that I'm working
> with, it's a lot more than two fields involved, and a concatenation would

go

> over FM's 60 meaningful character limit.
>
> The other is to do the find in the child file, then loop through the found
> set, creating a find request (using ZooId) in the parent for each record.
> I'm not confident about FM's performance of a find with, say, 300 find
> requests.
>
> Does anyone have another approach? Or is my second idea not as unpleasant
> as I think it is?
>
> Thanks
>
> Thomas Berg
>

I've never tried a find with 300 requests, but if it is indeed too slow,
perhaps you could use a multi-line key key instead. When looping through the
found set, set a global text field to itself & "(line break)" & the current
record's ZooID. This way, you won't need a second find in the zoo file, just
go to the related records from the global multi_line key field (of course,
you have to set a relationship for this).

Just my 2 cents :)

Marc-Andre Paiement