Finding related records in a dynamic relationship

[d94dsj]

Hi all,

I would just like some confirmation that the following can't (or maybe shouldn't) be done.

I have a portal which I can filter dynamically by setting a variable. This portal is based on a dynamic relationship with the "left" key being an unstored calculation which depends on the primary key and the variable - and the "right" key is a stored calculation, a concatenation of that files' primary key and another field.

Now, when I try to find records within the portal, I get no found records, no matter what search criteria I enter.

However, if I use a relationsship with the left key being a *stored* calculation, the find works. Now, why is that?

I thought that, this was maybe because in find mode, FileMaker does not have access to calculated fields. But it works for the relationship based on the stored calculation.

Isn't it possible to search portals based on "dynamic" relations?

I know that finding through portals can be quite slow, but sometimes it's just practical.

Thanks for any input,

Daniel

[Kurt Knippel]

Wait...what EXACTLY are you trying to do?

Are you in find mode? Entering find criteria into a portal?

Do you know what kind of result this will produce?

So I ask again, what EXACTLY are you trying to accomplish?

[Kurt Knippel]

Searching in a portal is actually a pretty funny thing, since your results are NOT records from the related file. You get all the records in the main file in which at least one of thier related records matched the search criteria.

However doing this in a dynamic portal will NEVER work correctly do to portals work. It is generally best NOT to do any searches via a portal, but to either search the parent or child file directly.

[d94dsj]

Thanks for your input guys, but...

>>Fitch

You are wrong. Portal searches work just fine in most cases. It's true that the found set of records will be records from the "Parent" file, but FM has to use the related fields to find out what records in the "Parents" file to show.

>>CaptKurt

OK, you say it won't work. I believe you, but I want some "rationale". I'm an engineer. I need to know the technical details...;-) Is there anyone out there who knows more about the inner workings of FileMaker good enough to explain the behaviour in this case? It's always good to know the "inner workings" of FileMaker, you never know when the knowledge comes to use!

Thanks,

Daniel

[Kurt Knippel]

It is because you are in "Find" mode, while the portals are designed to display records in browse mode. This is especially true of relationships based upon unstored calcs, which do not react in the same manner in Find Mode.

Plus you are going about this in what is essentially the SLOWEST possible way to do searches. Relationships based upon stored and indexed fields seems to react OK while in Find Mode, although searching is still slower than other methods.

Searching the child records directly and going to a found set of thier parents is one way to accomplish this MUCH faster.

Without knowing what you are INTENDING to accomplish I cannont really give any more specific directions.

[d94dsj]

>>CaptKurt

I enter find mode and enter find criteria into any field of the portal row. Like searching for "*" would display some records.

But I get the message "No records match your search criteria" (translated from Swedish).

I would of course like to find the records I give search criteria for! So I can find the same records I see in the portal in Browse mode.

Is that a detailed enough explanation?

Thanks for any help,

Daniel

[Fitch]

Daniel, I didn't say you can't do a search in a portal. I said it will only work if the search returns a match based on a stored value (which you already know from experience), and that that seemed like logical behavior to me. Sorry I can't think of a better answer at the moment.

[Fitch]

If you think about, it's logical. Portal values are not stored in the current file (unless it's a self-join). So portal searches only work on values that are stored in the current file. It makes sense... even though I can't explain it very well.

[d94dsj]

>>Fitch

Sorry if I misundestood you, I *do* appreciate your input!

But I still don't think it's logical. That's because FileMaker is able to search related fields, which also are not stored. So there's a bit of a contradiction there. Also, I think that FileMaker could tell me that I wouldn't be able to search the fields instead of just not returning any records!

>>CaptKurt

Thanks for your input. You ask what is my intention. In this case I'm designing a general interface for a quite big solution with about 30 related files. In every file (except for "support" files and some "line items" files), I have an area at the top of every layout, with buttons to perform common tasks. These are always displayed, independent of file or layout (almost true).

Some of the tasks are "New record", "Find", "Show all", "Sort", "Omit record(s)", "Reports" and "Calendar".

I also have a tabbed interface for each file. In some tabs I have a portal displaying records from a "child" file. Because I want the user interface to be as general as possible, I want the user to be able to perform a "Find" wherever it is most natural for the user. So, if the user is in a tab containg a portal and wishes to find all records in the "parent" file that are related to certain records in a "child" file, he/she could perform the find in the portal. I know that it is slow, but if it's adequately fast, the user should be able to perform the find. But, as we have discussed, the find does not work when the portal is based on an unstored calculation. At the moment I'm displaying a message to the user that tells them that this particular layout is not searchable and that they should go to another layout to perform the search.

I guess I have to live with this. But it would be nice to have some reference information about the "inner workings" of FileMaker. But, being an FSA Associate, I still don't think that FileMaker will supply that kind of information. Does anyone know of such resources?

One of the most technically advanced (yippie!) articles which gives some clues as to how FileMaker works when performing certain script steps is the "Going to Related Records from a Found Set in FileMaker Pro: A Discussion of Alternative Tecniques" by Debi Fuchs of Aptworks Consulting. It is downloadable from www.aptworks.com and I can really recommend it!

/Daniel

[bbaliner]

Daniel,

I agree with the fact that "inner workings" info would be very helpful. For every other database that I've worked with there are books and help on such topics, example would be "Inside the SQL Server 7.0" for MS SQL Server.

I will put this on the File Maker wish list. If anyone knows of any such resource for File Maker, please let me know.

[BobWeaver]

Sorry, useful technical details about Filemaker are more closely guarded than the access codes to the nuclear arsenal. Somewhere in the Filemaker Inc. management hierarchy, there is a Jack Nicholson-like bureaucrat saying "You can't handle the truth." Unfortunately, we are just dumb insignificant developers, too stupid to understand anything.

[Steven H. Blackwell]

Just what info is it that you fellows are looking for that you believe that FMI has hidden from you.

Old Advance Man

[Kurt Knippel]

quote:

---

Originally posted by DanielS:

So, if the user is in a tab containg a portal and wishes to find all records in the "parent" file that are related to certain records in a "child" file, he/she could perform the find in the portal.

---

Ok, so why is this based upon an unstored calculation?

If the intention is for searching purposes, doing so on the general data entry screens is usually to worst way to do it. I ALWAYS make specific find screens, and if I do make general data screens available for finds, I make sure that the users know that it is as it is and I do not do anything special. That is what I make dedicated find screens/dialogs for.

I would suggest that you do the same. While you may believe that users will want to search on any possible field, there are usually a very small subset that they will only ever work with. Those are what should be on your find screens and you should make every effort to make those finds as quick as possible.

[d94dsj]

>>CaptKurt

It's based on an unstored calculation becuase the filter I use depends upon a global which is part of the "left" key. Then it becomes unstored.

For the rest of your post:

I agree with you on some points, but not all.

In my particular situation, I have 30+ related files with a minimum of 2 user-entry layouts in about 20 of them (one "Detail" and one "List" layout). This is part of a generic user interface that has been designed for the solution. Part of every user-entry layout is identical throughout all files.

The users are quite knowledgable in FileMaker and used to creating finds - that said, I still script everything and do not let them enter find mode manually.

We do not create specific find screen because of 2 things:

* User interface design - When people are used to enter a specific piece of information in a certain field that's located in a particular area of the layout, they also tend to look in that area to enter find criteria when finding records. If I had a separate find layout with the fields on diffent locations, they do not recognize where the fields to search in are, and it takes longer time to find it.

* Time-savings. Designing good looking find layouts for 40 layouts takes quite a while. Since the "find" button is found in the top part of each layout, it should work for most layouts per default.

Now, this approach has it's downsides. For example, when entering data in key fields, you have to mask the underlying key field with a calculated field to display proper data to the user.

It would be good if finds always could take place where they perform the fastest, but that might be hard to implement in my situation becuase of the general approach for the user interface design. Sometimes when a user clicks my "Find" button, I tell them to perform the find from another place (or the dialog asks them if they want to go there).

Daniel