Find Parent records by Child criteria?

[kassetra]

I have a Parent db where each record can have more than one related record in my Child db. I would like to be able to find Parent records by criteria in the Child db.

For example, let's say my child database has these three fields:

Hair Color

Eye Color

Birth Month

And that each record in the Parent database, may have many related records in the Child database.

Sounds simple, except that I never know what the criteria will be. One minute my users may need to find the Parent db records that have related child records with: Red Hair, Blue Eyes, born in September *AND* Blonde Hair, Green Eyes, born in December -- and in the next minute they may need to find Parent records that have related child records with: Brown Hair, Brown Eyes *AND* Blonde Hair, Brown Eyes, but *NOT* Brown Hair, Brown Eyes born in August.

I have a third "Find" database that allows my users to input each set of criteria into a portal (so that the last example of Brown Hair / Blonde Hair children would have three records -- one for each criteria)... that then goes out and "finds" the child criteria...but finding parent records (by ID number) that meets all of that criteria is rough.

I have some clumsy scripts that go through and "clean up" after what is essentially an "OR" find in the Child database, and then translates the unique Parent record ID numbers to a global -- which then finds the Parent records in the Parent db. But it doesn't work well -- especially when I never know how many *AND* or *NOT* operators my users will need (the clean up scripts usually bork on me when I have *both* an *AND* and a *NOT* in my criteria).

I've tried running the first criteria as a regular find, and then extending or constraining the remaining criteria -- but that doesn't work if, for example, your first find is all Brown Haired children, and then you want to constrain your records to only include Brown and Blonde hair. So I can extend the found set to include Blonde haired children... but then how do I constrain the find to only Parent ID numbers that have *BOTH* Blonde and Brown? The "!" operator doesn't work using the "Constrain" option... I keep trying it though...

If anyone has a fresh perspective on how to make this find for my users, I would be most appreciative. I've been stuck on this project for over a month. I'm sure there has to be a more elegant solution, I'm just running out of time to think of it on my own.

[Ugo DI LUCA]

I'd take the risk again to put "Finds" on the side here.

You can handle one search string at the time involving a Multikey,

as a calculation in the related file, along the lines of

Hair Color&"

[kassetra]

Hmmm. Maybe I can adapt that with my "clean up" scripts to find what I need.

Those calculations work on a single record, and if I was looking for all of the red haired-green eyed children and all of the blonde haired-blue eyed children, that would be marvelous! The catch is that I want to find all of the red haired-green eyed children *AND* the blonde haired-blue eyed children that come from a *single* parent. (each parent that has both: at least one red haired-green eyed child *AND* at least one blonde haired-blue eyed child -- it gets worse when I start to throw the *NOT* operators in there as well).

That's where I have my problem. Maybe by condensing the criteria though, as you have suggested, my "clean up" scripts that run for an "AND" or a "NOT" logical operator would work better. I'll chew on that thought for a while and see if it makes this work better.

Thank you for your ideas!

[kassetra]

I think I have it -- after thinking about condensing the search into a calculated field....I finally thought of something straight forward -- I need to do each subsequent find inside the previous found set -- a subfind. Your calculated fields idea reminded me that I could "save" the Parent ID's from the previous found set (which is what I'm actually searching for -- the Parents!)

If I want to find parents that have both, Red Haired children and Blonde Haired children --

First I do a find for the Red Haired children. I put all of the found Parent ID's into a global field (loop...set field, etc.).

Then I do a find for the Blonde Haired children -- Now, I compare their Parent ID's to the global field. If there is a matching Parent ID -- I put an "X" after the matching Parent ID in the global field.

After I finish checking for the last record, I "clean out" the global field of any Parent ID that *doesn't* end in an "X".

If Blonde Haired children was my last criteria, I use the global field to find all of the Parent records in the Parent db -- if it wasn't, I do a new find and compare Parent ID's again. (the "NOT" operator would have the opposite effect -- if there is a match, put an "X" after it, and then *remove* all of the "X" fields!)

I'll have to see how efficient this idea is....

[Ugo DI LUCA]

OK, this will work I think.

You don't need to loop through related records to set the ID's.

You can do this through the ValueListItems() to set the content of the global to the one of the VLI,

or

Use a Copy All Records step to "paste" the IDs as a MultilineKey (this would work after a classic find also).

From what you said, you might be interrested to have a look at 2 sample files :

[kassetra]

Oh yeah, Copy All Records! Forgot about that step...

The way I was thinking of works... and it works every time, no matter how many criteria... because each time new sets of criteria are added, it becomes a subfind of the current found set...

I can find Parents that have Red Haired, Green Eyed, Primary School age, born in September children *AND* that also have Blonde Haired, Brown Eyed, Secondary School age, born in April children but *NOT* Blonde Haired, Brown Eyed, Secondary School age, born in April, that have a twin....and on and on it goes... when all I really want to find are the *Parents* of said children -- not the children themselves.

And with the third database keeping track of the find criteria -- I can allow my users to "save" their find criteria (especially for very complex finds).

It *can* be a tad slow over the network, but this is a local developer copy and not the server production copy... so it should be better when I move it into production....

That being said, I'd like to see other ways of maybe handling this information (more elegantly, hopefully), so yeah, I'd like to see that clone!

Thanks again!

[Ugo DI LUCA]

Good to know you solved it. Not sure I understood how though. Relationships or Find steps ?

"I have a third "Find" database that allows my users to input each set of criteria into a portal (so that the last example of Brown Hair / Blonde Hair children would have three records -- one for each criteria)... that then goes out and "finds" the child criteria...but finding parent records (by ID number) that meets all of that criteria is rough. "

OK, the multi-criteria search file I was talking about also perform GTRR from a third "Search File".

Basically, it sets each criterias in a row, and associate or not other criterias.

Then, a compound Multikey is created in a script that loops through these records, and progressively trigger a SetField(global, global & VLI) or a looping Substitute (global, g_ID) in case of "Omit" command.

A value list is set to a Pop-up menu for each criterias in the portal.

Rather than having one record per find, holding shift and clicking in the field allows to select multiple criterias for each field in case you need it.

The script will parse the content of this Multiline key into separate multikeys.

It's sure tedious. Slow ? I have a progress bar just for it, but the results should be quicker than a find step.

Give me your mail adress (mine shows up in Profile) so that I can send you the files when they're over. Not the quicker job I have to do though.

[Ugo DI LUCA]

Hi,

Check an adaptation of my previous answer, which doesn't use the Third Find File I mentionned though.

MultiCriterias Related Finds

HTH