Q: We're having trouble when we try to Find in two fields in a portal. The records we get have both the items we're searching for, but they don't necessarily match up in the portal row. Help!
A: If you're already experienced this portal find problem and don't care about the background, skip ahead to "- The Solution -" below.
Knowing how to get the most from Finds is the key to using your databases well. You may already know that finding records in a single field in a portal isn't particularly snappy, but it does work. As a test, I just performed a Find in a portal of a file I use every day. The related file holds 35,000 records and it took 10 seconds. That same find is much slower when performed over a network. Now try adding a 2nd field to the Find. Not only are the results inaccurate, they're abysmally slow - and they can even crash a network. Obviously, you need to do something else.
- What's a portal? -
Before I answer this month's question, some of you are probably wondering what the heck a portal is. (If that's the case, you may find this discussion beyond you. But you might want to plow ahead to see what you learn from it.) A portal is an object on a layout where items appear as a list (usually), and it often looks like what you see when you look at an invoice. (FileMaker also has the ability to create repeating fields which can look like a portal. You need to determine which animal you're dealing with before working with this issue. You might want to hire a professional to help you figure out what you have. I'm available :-) This discussion will only apply to portals.
- A Description Of The Problem -
Let's say you have a Conference Attendance portal with 2 columns; Conference Name and Session Name. You start a Find, click in the Conference Name field and choose "2005 Fall Festival" from the pop-up list. In the Session Name you choose "Voice Over IP," and continue the Find. After some length of time* you see the records, but there's something odd going on. Some records are from last year's 2004 Fall Festival showing "Voice Over IP." What's going on? And more importantly, how can you find the records you really want?
* (It takes longer to find items in a portal because fields that are otherwise indexed are not indexed when you access them via the portal. A temporary index needs to be built. The more records in file/table that the portal has to search, the longer it takes.)
- Why You Don't Get What You Expect -
This problem occurs exactly because fields are not indexed when they're sitting in the portal. (The actual reasons are technical and beyond the scope of this already somewhat complex discussion.) FileMaker first builds a temporary index on the Conference Name field to get a group of records for the 2005 Fall Festival. Once it has those records for you, it looks in the next column to build a temporary index and omit any records that do not have Voice Over IP in them. But it's not paying any attention to whether the two requests match up in the same row of the portal. Two fields in a portal are not indexed together.
- The Solution -
(First let me warn you that you'll need a moderate knowledge of FileMaker for this to make much sense. Here goes...) There are actually two solutions. The least preferred method involves making a calculated field which still ends up unidexed in the portal - and unnecessarily slow. So we'll spend our time with the recommended method.
Find the source file or table of the items in the portal. (In the case of FMP7 this may be a table in the same file.) Here's how; While you're looking at the layout with the portal on it, go to the View menu and choose Layout mode. Double-click the portal to bring up the Portal Setup dialog. There will be a pop-up list next to "show records from." Click on the pop-up list, pull all the way to the bottom and choose Define Relationships. Use this dialog to identify which file (or table) the portal comes from.
I'm assuming that you know a bit about FileMaker at this point, so I'll ask you to go to that file or table. Once you're there, go to a layout that shows the fields on which you're trying to perform your Find - or create a layout for finding. I recommend displaying the fields in a list rather than as a form. As soon as you're on the right layout, try performing the same Find. Assuming the fields are indexed, it should happen in a snap. If they haven't been indexed, they will be by the time you finish that first Find, and the index will stay with those fields for the next Find, making it very fast indeed.
Now that you've found the group of records you need, you can make data from the file or table where we started show up in the list. In versions of FMP before 7, create a relationship back to the file where you started using the same key fields. (It might be something like a Customer or Member file.) Once the relationship has been created, you can add fields from the other file right there in the list on the layout. In FMP7, if the table is in the same file, the relationship is already there. Just add the fields to your list.
Whenever you need to get this report, go to the same layout and search the records. Depending on how frequently you need to run the report, you could create a script to do most of it automatically at the click of a button. You'll get both speed and power. And isn't that what software should be all about?
- A Final Note -
When I first heard that fields in a portal are not indexed, I immediately went over to the source file/table to take a look. The fields WERE indexed. There's no use in trying to force the index to renew itself in any way. Whenever a field is in a portal, any index applied to it is ignored. Never mind what should be. Just figure out some other way of getting what you want and get on with your life. I did and I'm happier for it.
Of course, the main purpose of this newsletter is to remind you that I'm here to serve your FileMaker needs - development, support, and training.
If you can think of anyone you might refer me to, I offer a 5% "Thank You!" for the first 2 years of billing on referrals.