You mean there aren't separate fields, as I had understood, for each characteristic, but, rather, 'CharName' is just one field?
If so, then apologies for having mis-posted, and follow Jason's suggestion. John Weinshel Datagrace Vashon Island, WA -----Original Message----- From: FileMaker Pro Discussions [mailto:[email protected]] On Behalf Of Jason L DeLooze Sent: Thursday, June 04, 2009 4:56 PM To: [email protected] Subject: Re: Recursive Searches with FMP10 Chuck, If I'm reading this correctly, a Person (Chuck Pelto) in the People table can have 1 or more records in the Characteristics table; that is, the "Chuck" People record can have a related Characteristics record with the values: CharName = "Hair Color" CharValue = "Blonde" and another related Characteristics record with the values: CharName = "Eye Color" CharValue = "Blue" On the other hand, you could have setup fields [HairColor and EyeColor, for example] in the People table and stored the CharValue [Blonde and Blue] in their respective fields. Of course, this second approach requires you to know a priori all the possible Characteristics of a Person, but the search would be simpler, more direct, and avoid a problem which I will mention below. ====== To solve the problem using your setup, you will need several copies of the Characteristics table, each related to the People TO via a PEOPLE::RecID = CHARACTERISTICS::LinkToPeople join. Let's be more specific ... In the RG, create 3 copies of the Characteristics TO, named CHAR1, CHAR2, and CHAR3. Join each of these TOs to the PEOPLE TO via PEOPLE::RecID = CHAR1::LinkToPeople PEOPLE::RecID = CHAR2::LinkToPeople PEOPLE::RecID = CHAR3::LinkToPeople On the PEOPLE layout, based on the PEOPLE TO above, appropriately place the fields CHAR1::CharName and CHAR1::CharValue CHAR2::CharName and CHAR2::CharValue CHAR3::CharName and CHAR3::CharValue as you see fit. To find People who are blond-haired, green-eyed and an IQ over 120, enter Find Mode and type "Hair color" into CHAR1::CharName; "Blonde" into CHAR1::CharValue ; "Eye color" into CHAR2::CharName; "Green" into CHAR2::CharValue ; "IQ" into CHAR3::CharName; ">120" into CHAR3::CharValue Perform Find and you will get everyone who meets this criteria ... except that the IQ characteristic is a numerical value stored in a text-type field. Therefore, the "IQ" search will match those with an IQ = 20 because "20" > "120" as text. Therefore, if all your Characteristics will be text values - or you don't plan on searching for a numerical range, such as "> 120", then this approach will be OK. But if this is not the case, then you should merge the various Characteristics into the People table (as attributes), setting the correct field-type (text, number, date, time, etc). ====== Hope this helps, Jason L. DeLooze Annapolis, MD On 6/4/09 at 12:08 PM -0600, Chuck Pelto wrote: > What is the best way to build a recursive search capability into an FMP10 Script? > > For example, you have two tables: People and Characteristics. > > People has fields: Name, RecID. > > Characteristics has fields: LinkToPeople, CharName, e.g., eye-color, hair-color, etc., and CharValue, e.g., for eye-color, blue, green, brown, gray. > > I'm looking for a way to do a search in Characteristics that would find all the People who are red-haired, gray-eyed and left-handed. > > Or, in another search, People who are blond-haired, green-eyed and an IQ over 120. > > Can this be done? > > Regards, > > Chuck
