Dear All,

I am a newbie and have a tricky 2 "stage" find question.

How do I create a script that will find results in a table based upon the results of a Find in a different table?

Specifically:

1) Out of 10,000 shops, how many of these sell "apples"? These shops are in table <shops> each with a unique ID. "apples" are in the <fruit> field (I can do this one!)

2) Based on these X number of shops, I want to know who are the suppliers to these shops. These are in table <suppliers> and each supplier is linked to the shop through the unique shop ID.

Therefore, I want to create an extend find set from the <suppliers> table based upon the results of the find from the <shops> table. At different stages, I will be changing the <fruit> so I can find all the shops' suppliers for say, oranges, bananas etc.

I have created the 1st find based on the "Go To Related Records" function but am unable to use the "Extend Find" function thereafter in a script. I can do this manually, but right clicking the field then "Extend found set" takes a while.

Thank you very much in advance.

Aikiko

FMP 10Adv

Mac OSX

Out of 10,000 shops, how many of these sell "apples"? These shops are in table <shops> each with a unique ID. "apples" are in the <fruit> field

This is not a good starting point. There should be no Fruit field in the Shops table - unless a shop sells one fruit only.

each supplier is linked to the shop through the unique shop ID

Same thing: does a supplier work with only one shop?

Dear Comment,

Thanks for your input. To be honest, I've tried to simplify the nomenclature of the database by using fruit/shops/suppliers for my query, perhaps it's best that I return to the actual data I've used as I may have confused myself.

Many apologies. OK, let me begin again.

I have a laboratory test to determine if someone has disease A. All these people (N=10000+) who have a positive or negative test are listed in table A. I can FIND all of these people (with a unique study ID). Now I would like to use these results (people with a positive test result) and find out if the people they live with may also be infected. A complete registry of all people who were tested AND the people they live with are listed in table B.

Only people who were tested are given a unique study ID number. All people who live in the same household (including those tested) have the same household ID number. If it makes it any easier, all people in table B have a unique database number as well.

Hence my challenge, is to find people in table A, and use these results to find all "related" people in table B. I can do this manually, but "extend finding" in table B is extremely time consuming.

Thank you very much in advance once again and apologies for the confusion.

Aikiko

Ideally, you'd have the following structure:

Households -< Individuals -< Results >- Studies

Then you could start at a record in Studies and perform Go to Related Records [ from Households; Show related only; Match current record ] as step 1, and Go to Related Records [ from Individuals; Show related only; Match found set ] as step 2. This may not be very fast, but it is very simple.

Dear Comment,

Thanks for your reply.

I think that I have managed to do what you have mentioned above, and I'm already doing this manually. Simple, but not fast indeed. If I have 1000+ positive test results, my mouse will be in hyper-click mode!

But, I was wondering if this could also be done as a script?

I can make a little script, that can give me the GTRR in Households, but from here I don't know how to create an "extend found set" script based on a household number. As mentioned, I can do this manually, by control-clicking on each individual household number, then pressing "extend found set".

Any further suggestions would be much appreciated.

Aikiko

I'm afraid we are not on the same page. What I have suggested is a script - it cannot be performed manually.

A-ha! I understand that I was not following what you recommended. When I mean manually, I mean that I can accomplish my problem by doing it only manually, but it takes a long time to do. Many apologies.

However, I still cannot achieve the task to hand as you have instructed:

Households -< Individuals -< Results >- Studies

Then you could start at a record in Studies and perform Go to Related Records [ from Households; Show related only; Match current record ] as step 1, and Go to Related Records [ from Individuals; Show related only; Match found set ] as step 2.

My current structure is:

Households --- Results

Fields within Households are: household number, individual number

Fields within Results are: individual number, results

The individual number in the 2 tables are the same and linked.

Thus I can search for positive test results in the Results table e.g. 20 out of 1000 tests, and use GTRR to give me the corresponding list in the Household table (as you listed in step 1), but I do not know how to show related records to allow me to see all the people who live in the same household (as these 20 people). I can do this manually, by control-clicking on the household number then clicking "Extend found set", but it takes a while.

Thanks again

Aikiko

My current structure is: Households --- Results

I do not know how to show related records to allow me to see all the people who live in the same household

You need the individuals table, as Comment suggested earlier.

My current structure is:

Households --- Results

Fields within Households are: household number, individual number

If I understand correctly, your current structure is actually:

Individuals -< Results

If you don't have a table where each household is a unique record, you can still define a self-join of the Individuals table as:

Individuals::Household = Individuals 2::Household

and use GTRR [Match found set] to expand the current found set in Individuals to all related household members.

Oh by George......I've done it!

Thanks ever so much. I've been trying to do this for weeks.

And for the record it was just a 3 step script:

Perform find [insert criteria for positive laboratory results in Table A]

GTRR [show only related records; Match found set; From Table B]

GTRR [show only related records; Match found set; From Table B2]

The key was doing the self-join in Table B as you suggested.

And to think in other forums, people were suggesting setting variables, loops and Global IDs. Well I guess there are many ways to skin a cat.

V happy. Thank you ever so much once again! !!!! :jester:

And to think in other forums, people were suggesting …

There are no other forums.

I take it you were being ironic, but if not I was referring to:

http://forums.filemaker.com/posts/10e79a9d3f

Then again, it's probably the same forum on a different website.

Thanks again. Much appreciated.

No, that is the company's forum. I used to post there.