9 Replies Latest reply on Oct 18, 2011 8:55 AM by ClayHarrison

    Using portals to display a list of records from the same file

    ClayHarrison

      Title

      Using portals to display a list of records from the same file

      Post

      I am new to FM11.  I used FM5 quite a bit serveral years ago and many things have changed in the program.  I am still trying to get my head around using table relationships that relate to the same table.  I have a table that tracks project leads for marketing.  I have one layout that displays all of the information for a single lead.  I was wanting to create a portal on a different tab that would list all of the project leads meeting a certain criteria (such as "Hot Lead" in the field Lead Status).

      I setup a new calculation field in the table called Hot List Key and defined it as ="Hot Lead".  I then created a table relationship with the main table "Leads" and a new table occurrence called "Hot List" and defined it as "Lead Status" = "Hot List Key".  I then put the fields I wanted to display in the portal from this new table occurence.  The problem is that is lists all of the projects and does not filter them to only show "Hot Leads".  I am sure I don't have this right, but I can't see the problem.  Any help would be greatly appreciated.

        • 1. Re: Using portals to display a list of records from the same file
          philmodjunk

          I am still trying to get my head around using table relationships that relate to the same table.

          Perhaps this tutorial on Table Occurrences can help:  Tutorial: What are Table Occurrences?

          I was wanting to create a portal on a different tab that would list all of the project leads meeting a certain criteria (such as "Hot Lead" in the field Lead Status).

          What version of FileMaker do you have? If you have version 11, a portal filter expression would be simpler to set up instead of an additional relationship. Using what you have already set up, How did you define the field "hot list key"? Is this a field of type calculation or some other type? Stored, unstored or global storage?


          • 2. Re: Using portals to display a list of records from the same file
            ClayHarrison

             I have version 11.  I will read about the portal filter.  I had never used a portal to show a list of records out of the same file the layout is based upon.  The field "hot list key" is setup as a calculation field and the calculation is stored and indexed, not a global field.

            • 3. Re: Using portals to display a list of records from the same file
              philmodjunk

              And what is its return type? (Should return text and this is controlled by a drop down in the specify calculation dialog.)

              Is Lead Status a field of type text?

              • 4. Re: Using portals to display a list of records from the same file
                ClayHarrison

                 It's return type is set to text and the Lead Status field is a text type.

                • 5. Re: Using portals to display a list of records from the same file
                  philmodjunk

                  So you have this relationship in Manage | Database | Relationships?

                  ProjectLeads::Hot Lead = ProjectLeads 2::Lead Status

                  Project leads 2 is created by selecting ProjectLeads and clicking the duplicate button (button with two green plus signs). You can double click the new table occurrence this creates to give it a more descriptive name.

                  If this is what you have set up, then you can add a layout to ProjectLeads 2 on your ProjectLeads based layout and it should list all records where Lead Status = "Hot Lead".

                  Using the Portal Filter approach, you can use:

                  ProjectLeads::anyfield X ProjectLeads 2::anyfield

                  and then use this portal expression:

                  Project Leads 2::Lead Status = "Hot Lead"

                  This eliminates the need to add a special calculation field just to get the portal to work.

                  If you have more than one status value in lead status, you can even use a reference to a global field in place of "hot lead" and then set up the global field with a value list and a triggered script so that selecting different values in the global field produces a different lists of records in the filtered portal. This can also be set up at the relationship level (that's how we had to do this prior to version 11) instead of using a portal filtering expression.

                  • 6. Re: Using portals to display a list of records from the same file
                    ClayHarrison

                     I had actually set it up backwards from what you described.

                    ProjectLeads::Lead Status = ProjectLeads 2::Hot List Key

                    I reversed it and it worked perfect.  I see know where my logic was flawed.

                    Thanks a bunch.  I will try the other approach as well to make sure I understand it.

                    • 7. Re: Using portals to display a list of records from the same file
                      ClayHarrison

                       I have a followup on this issue.  I am now trying to display a set of records in a new portal that will show a range of values.  Given the example before, how would I structure the relationship if I wanted to display all of the records that were equal to "Dead Lead" or "Dead project" or "Lost to Competition".

                      • 8. Re: Using portals to display a list of records from the same file
                        philmodjunk

                        With a relationship, you can set up the constant calculation as: List ( "Dead Lead" ; "Dead project" ; "Lost to Competition" )

                        and link that to the lead status field in your relationship. The returns separating each value in the matching field sets up a match that matches to any one of the listed values.

                        With a portal filter, you just update the portal filter expression:

                        Project Leads 2::Lead Status = "Dead Lead" or Project Leads 2::Lead Status = "Dead project" or Project Leads 2::Lead Status = "Lost to Competition"

                        • 9. Re: Using portals to display a list of records from the same file
                          ClayHarrison

                           I did use the portal filter method with success, but the sorting in addition to the filtering on the portal seems to slow things down.  I only have around 1500 records, but a dialog pops up saying it is sorting with a status bar and it takes 15 secs or so to complete.  I worked this out after I made the post last night.  I was wanting to try and get it working with the relationship since that will make the reports easier as well.

                          I will give the first method you listed a shot.  Not familiar with the List function, but I will readup.  Thanks.