6 Replies Latest reply on Oct 3, 2014 1:04 PM by Steve_2

    Show List of Found Records in Adjacent Portal



      Show List of Found Records in Adjacent Portal


      I'm trying to create a Search Panel layout, so that the user can specify any number or combination of items on the left side and, upon clicking Find, see a list of ALL the found records (recipes in this case) in the Portal on the right side. (The user can then select one of the found recipes in the Portal and go to an appropriate layout.) My problem is that when the search is performed, although say, 4 recipes are found, only 1 of them shows in the portal (but the count for 4 recipes shows, and oddly the buttons for a 2nd recipe appear). To make the Portal, I created a self-joining relationship with the "Recipes" table, using the unique "Recipe ID" number as the match field. I've attached a screenshot of the very rough undesigned Search Panel. (Note that the specifics shown on the left are of course for the first recipe in the 4 found ones.) Since I'm pretty much a novice at this stuff, please be patient in helping me understand the solution.


        • 1. Re: Show List of Found Records in Adjacent Portal

          Portals display what records are related to the current record. They do not display the records produced by performing a find. That is why you only see one record in the portal. The second blank row is there because "allow creation of records via this relationship" has been enabled for the portal's table occurrence in the underlying relationship set up in Manage | Database | Relationships. This is the "add row" where you can use the portal to add new records to the portal table that will be automatically linked to the layout table's current record.

          There are several options that can produce what you want:

          a) redesign your layout so that the fields currently on the left hand side are placed in a very large header layout part across the top of the screen. Set up the body of your layout for a List View of the found set produced by performing your find.

          b) Use a relationship that matches to all records (use X instead of = in the relationship) and a complex portal filter that uses the values of the left hand fields to control what records appear in the portal.

          c) Generate a list of the Primary Key values for all records in your found set and use it as the match field to the portal table's records. In FileMaker 13, this can be done wit a calculation field. In older versions, it's more complex a process and requires a script to build the list of IDs.

          d) In FileMaker 12 or later, the match field used to make your portal happen can be an ExecuteSQL driven calculation that produces the same list of Primary Key values as described in c)

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Show List of Found Records in Adjacent Portal

            Thankyou, Phil. I do have Filemaker 13, and your suggestion D sounds like it would be the logical way to proceed. Problem is, I have no understanding of ExecuteSQL (a browse through a Filemaker 13 SQL reference left me even more confused), and hence no idea how to proceed with your suggestion! Can you help further? (And in advance, I appreciate all your time on this.)

            • 3. Re: Show List of Found Records in Adjacent Portal

              I suggest then that you use c) as it's much simpler.

              Here's how to set up that approach.

              I will assume that you have a field that auto-enters either a serial number of Get ( UUID ) to uniquely identify each record in your table. I will refer to it as __pkPrimaryKey below:

              1) Your layout is based on a specific occurrence of a specific table defined in your database. The table occurrence name will appear next to "Table:" in the status area tool bar when you are in layout mode. This is often the same as the (data source) table name.

              2) Go to Manage | Database | Fields and add two new fields to your layout's table:

              sIDList: Define this as a field of type summary that produces a "list of" __pkPrimaryKey.
              cIDList: Define this as a field of type calculation with Text as its result type. It's calculation expression:


              3) Click over to Relationships and find the table occurrence box with the same name as that specified for your layout. I will refer to it here as having the name "LayoutTable". Click it to select and then click the duplicate key to create a duplicate table occurrence. Drag from cIDList in LayoutTable to __pkPrimaryKey in LayoutTable 2 to link the two in a relationship.

              4) Now go to your layout, enter layout mode. Remove your current portal and replace it with a portal to LayoutTable 2. Each time you perform a find on this layout, the portal should update to list each of the records that make up your found set.

              5) A button placed in the portal row can use Go To Related Records to take you to a specific record with a single mouse click.

              You may find this thread on scripted find examples a useful source of ways to improve on your basic design: Scripted Find Examples

              • 4. Re: Show List of Found Records in Adjacent Portal

                Phil, First, you're brilliant. I followed your well-explained steps and the new portal now shows exactly what I need. That's great! However, one small problem occurs. I've placed a button in the portal row to "Go To Related Records"; however, no matter which row I click the button on, I'm taken to the recipe shown in the top row. (Which is the active record following the find, and the one that shows up in the search criteria boxes to the left.) Any thought on that? Oh, did I mention you're brilliant?

                • 5. Re: Show List of Found Records in Adjacent Portal

                  It would help If I knew the exact options that you've specified for your GTRR.

                  Using the original terminology from my example, you might use:

                  Go To Related Record [Show only related records; From table: LayoutTable 2; Using layout: "NameALayoutHere" (LayoutTable)  ]

                  You can use <current layout> if you want to stay on the current layout. Or you can specify any layout in your file as long is it is based on an Tutorial: What are Table Occurrences? of LayoutTable.

                  There's also a "new window" option that would let you pop up this result in a new window if that is desirable.

                  • 6. Re: Show List of Found Records in Adjacent Portal

                    I think you just solved it for me. In the GTRR I had specified From table: LayoutTable. Your example showed me that I should have used From table: LayoutTable 2. Thanks. Again. (And again, brilliant.)