A READER ASKS: One Portal & Multiple Tables

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).

I am a new user of FileMaker, and I have FileMaker Pro 9.0 version 3. I am trying to figure out portals, and didn't quite get my answers on the FileMaker Help menu or its we bsite. I did a Google search, and your blog came up. So here I am, asking you my question:

Is there a way to set up a portal, with data from various tables combined? When I set up a portal, I get prompted to choose from only one of the tables in my database. I would like to see data from various tables listed together in the one portal. Is there a way to do this?

Specifically, here's an example that illustrates my situation:

I have a customer, let's call him John Smith. His name and contact info are stored in a database table. I give him an estimate, dated 10/1/05. I have a table for estimates. He agrees to the estimate and proceeds to do business with me. I create an invoice on 10/4/05. I have another table for invoices. He pays me a down payment on 10/6/05, followed by 3 monthly installments. Payments are stored in another table. John Smith is happy with my work, and comes back in '06, '07, and '08. So I give him another estimate, invoice, etc etc etc for each round of business I do with him. Repeat this process for a healthy-size clientele.

I want to be able to view a history of activities for John Smith. Thus, a portal that would show all estimates, invoices, etc attributed to John Smith; ie, a portal that can contain data from various tables.

Well, I’m going to have to give you the default answer of ... yes ... no ... maybe. Using direct FileMaker relationship design, the answer is no. A portal currently points to one and only one table.

Now lets talk about a join table. You can have your clients table point to one table and that table points to many tables. In your situation, you would have to find a way to populate the join table when new estimates or invoices are created. In the join table, you would have some calculation fields that would show details of the estimate or the invoice, depending on which join relationship is valid for that relationship. Those calculation fields would be the ones that you show in the portal on the clients layout. Then you could do interesting things like assign conditional formatting to the field to show estimates in a different color than invoice records.

It would take me about a half hour to show off this technique to you in a virtual one on one session. However, you might be able to craft this for yourself with some experimentation.
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.