Find in Portals

[djgogi]

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 in following article.

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.

Djukic Goran

Note: When relationship express some kind of dependency between two entities, the independent entity is called parent and dependent entity is called child. Otherwise the choice is arbitrary.

FindThruPortals.pdf.zip

[Leb i Sol]

Gorane pa ti si izgleda pravi programer.

Hvala za komentare!!!

...odakle si rodom?