FIND only related child results 4

[marty1452]

I have a related database that limits the child files to only related files of the parent file. When I do a FIND in the child file, the results return any file that qualify to the FIND, not just related files.

How can I perform a relational FIND?

The find is being performed in the child file. The relation begins with a login to identify the valid user. Then I want that user to be the only user to see their files.

Thanks

Bob

 

[JeanW]

Make your search trough the relationship(s).

You have to make a script for each relationship you want to make a search on.

HTH

JW

 

[marty1452]

I've fooled with this and I'm missing something. I thought if the field came to the layout through a self related relationship based on a global constant, I should always be in the files I need. It doesn't seem to work that way.

I have a global field to hold the ID of the user. I have a relationship from Global User ID | f_User ID stored in the files I want to access. I have a Show All script based on this relationship to return the user to all related files after a find and it works great to return only related files. Why can't I use this same relationship to pull in my fields through to a list view where the FIND will take place. If I do a FIND and the field is in a portal as described above, wouldn't this be a related find? It still returns invalid files.

Could someone give me a script example or ??? to make this work??

 

[karelvanek]

Marty1452,

if I properly understand, you are confused that when you perform find in a related field in a portal, the result records (found in the master file) contain the related records with proper values, but also show all the related records in the portal.

If this is the problem, there's nothing to do, it is common behaviour of related databases. To get just related records meeting search requests you have to perform find in the related database (ie. in a form created in related database).

Am I wrong? karel.vanekATgavlas.cz

 

[karelvanek]

Just a little note:

I've noticed you spoke about a self-related relationship. There is only one database then, but the behaviour is still the same.

 

[JeanW]

There is to much confusion about performing Find thru portal. In part, this is due to the lack of explicit explanation in FM documentation but also on missing testing on user side.
However I'll try to clarify the concept.
Most frequent common mistake:
"When I perform search in portal (or on relational field) I'm looking for children records that satisfy the criteria in find"

Absolutely wrong.
You are searching for parent records (if any) whose children records satisfy the criteria of find.
Remember that whatever search you are performing, the domain of result found set is CURRENT file.
In other words the found set is the subset of record set of the file where the search is performed (and not of the related file which might be the owner of some of the fields included in the search).
If you are asking yourselves why, then think of following scenario:
You need to perform search on different fields, each (or some) of them coming from the relationship from different file.
So the question would be:
The found set from which file should be presented to you?
The only correct answer is : FROM CURRENT FILE
And that is how the find on related fields, with or without portal is implemented in FileMaker.
Now, there are some issues, that will be addressed later, that create even greater confusion among users.
Namely, it is "AND find thru portal", in other words performing find on different related fields coming from same or different files give "strange" results.
But first, let's finish with general aspects of "relational" find.

1) 1-1 relationship

You have mandatory 1-1 relationship between two entities (tables, files) when for every record in parent file exists one and only one record in the child file.
You have optional 1-1 relationship between two entities (tables, files) when for every record in parent file exists no more than one record in the child file.

The relational find in this case depends on "deletion" rules applied:

"When deleting parent record delete also child record"

This is the ONLY case when performing search in related fields in master file would give the same result as in case the same search has been executed in child file.
Obviously only the found set of PARENT file will change.
Remember, even if only related fields are involved in search, the CHILD file doesn't know anything about your search in PARENT.

" When deleting parent record nullify migrated parentID in child record"

In this case performing find in PARENT could give different (minor) number of records in found set than performing the same find in CHILD file.

In case of 1-1 relationship there is no different behavior when performing an AND find on related fields.

2) 1-many relationship

As in case of 1-1 relationship we distingue mandatory and optional 1-many relationship.

You have mandatory 1-many relationship between two entities (tables, files) when for every record in parent file exists at least one record in the child file.
An example for mandatory 1-many could be the relationship between Invoice an InvoiceItems.
There is no Invoice without items on it!
On the other side the relationship between Customer and invoice is an optional mandatory 1-many relationship.
In any given time one customer could have 0 or more Invoices!

Performing find thru portal will always give the "UNEXPECTED" result when setting more than one related fields.
Let's forget just for a moment such behavior and take a look on on-field-portal search.
When performing find on only one related field thru portal (or simply on isolated related field without a portal),you are searching for every PARENT record that has at least one CHILD record that satisfy the criteria of find.
Of course, the PARENT record(s) if any produced by such find could have many other CHILD records that doesn't satisfy the original criteria, but that will be DISPLAYED in portal since the relationship itself doesn't know anything about the find you have just performed.
If the related field was outside of portal the confusion could arise from fact that, in this case, data from the first child record (that might not satisfy the criteria of find) will be displayed.

Finally, if you perform relational find (thru portal or not) on more than one related fields, in other words an AND find, you will get every PARENT record (if any) with CHILD records that have ALL requested values but not necessarily in SAME record.
If you find this "UNEXPECTED", than you are still thinking that you were searching for CHILD records.
Again, remember that your relationship knows only that every PARENT record could have many CHILD records and nothing else about contents of individual related records.
So, indeed it was an AND find on PARENT record set.
If you are not convinced yet, than for ex, in case you are searching on 3 different related fields, create 3 Find Request.
In the first request one set the first related field, in the second request set the second related field and in third request set the third related field.
Now, after performing find, you'll get every PARENT record whose CHILD records satisfy any of the above request and that is an OR find, again on PARENT record set.

 

[nsmaby]

Thank you for asking this question, Marty1452, and for your thorough and clarifying answer, JeanW.

I have a further Question for JeanW (or anyone else who can help):

I have a non-mandatory 1 to many relationship between 2 databases. In my case, we have a set of subjects who may or may not have had procedures performed on them. The PARENT database is a demographic database with general subject information (indexed by subject ID). The CHILD database is a set of procedures with subject ID, the date of the procedure, the type of procedure, and procedure classification. The only current relationship I have between the 2 databases is subject ID = ::subject ID. Within the PARENT database, I can use this relationship along with a portal to display a list of all of the procedures which have been performed on a particular subject. This is all fine.

What becomes difficult is doing particular finds (which I think JeanW gets close to fully explaining):

So, let's suppose that there are two procedures (in reality there are about 25-30 different procedures): Leg strength test and Arm strength test. Let's further suppose that there are 2 classifications of procedures: Right side, and Left Side. So a typical entry in the child database might look as follows:

Subject ID Date Procedure Procedure Class
---------- ------ --------- ---------------
TomJones 9/1/03 ArmStrengthTest Right

There is no limit on the number of procedures that can be listed under the SubjectID: TomJones in the CHILD database (he could even have the same procedure on the same side on the same date more than once).

Now, this is when I get into the problem that JeanW describes: I want to find a list of Subjects who have had both a Right sided ArmStrengthTest and a Right sided LegStrengthTest. So, I want to perform this find through a portal in my PARENT database. But, when I do an "AND" find through the portal (filling in Right for the Procedure Class and ArmStrengthTest for the Procedure), the found set will include subjects who have had any right sided procedure along with any sided ArmStrengthTest; i.e. the find through the portal does not seem to require that all of the values in the find occur within one CHILD record. JeanW suggests that I should instead be performing my find within the CHILD database, but I disagree - I don't want the list of procedures that satisfy my criteria, I want the list of subjects (because I would then like to do a constrained find - searching only within the found list of subjects - to limit my results to those that also had another particular procedure).

The only way that I can think of to solve this problem is to come up with a "combination" or "findkey" field in the CHILD database the would concatenate data into one field so the above example entry might become (in a "findkey" field):

FindKey
-------
R_AST_9103

Where:
R = Right (Procedure classification)
AST = ArmStrengthTest (Procedure)
9103 = Date

But this seems to be a bit of a Kluge - there must be a better way? Am I structuring my relational databases incorrectly? Any help is appreciated.

Thank You,

Niels

 

[Schlogg]

Finding Parents whose children have particular attributes -
On a Parent layout, put the child fields -
Child::Attrib1 and Child::Attrib2
No need for portals.
Just treat these fields as you would a field in the Parent.
You will find the parents with both attributes.

Cheers,
Paul J.

 

[nsmaby]

Hi Paul,

Thanks for your help, but I seem to get the same result regardless of whether I use a portal or not. The problem is, let's suppose I want to find all parents whose children have had a particular child with 2 attributes (say find all parents who have had right handed girls as children). If I do what you say, I will infact find all of the parents who have had right handed girls, but I will also find parents who have had more than one child - at least one of which is a girl and at least one of which is right handed. I.e. I would find the parents of a right handed boy and a left handed girl, when I only wanted parents of a right handed girl - this is because they are separate attributes in the child database and each parent can have more than one entry....