2 Replies Latest reply on Mar 17, 2016 12:51 PM by BMyers

    Accessing related portal data in the same layout?

    tkessler45

      I have two tables. The first is "Entries" and the second is "Labels". There can be more than one label per entry, so I created a one-to-many relationship from Entries to Labels by a Label ID field (unique, numerical serial auto-entry, etc.).

       

      I have another table that relates to Entries, and through a layout for this table am showing the listing of entries through a portal to the Entries table. This works as expected.

       

      What I would like to do is select an entry row in the portal, and then have another portal on the same layout show the Labels associated with the given Entry record. Is there a way to do this, especially one that doesnt involve scripting of global fields and other details? I can try to grab the Label ID from the selected/active Entry portal row (if possible--havent explored this yet) and store this in a global field that relates to the Labels table for the secondary lookup, but would prefer something simpler if it can be done (ie, not requiring a global field).

        • 1. Re: Accessing related portal data in the same layout?
          easyaspi

          Setting a global field is the simplest method I know of.

          If you haven't done it before, here it is in a nutshell...

           

          Create a global field _gLabelID in the Entries table.

          Create a relationship (Lets call it Entries_Labels|gLabelID) from Entries to Labels where _gLabelID = LabelID.

          Put the desired fields from Entries_Labels|gLabelID on the layout.

           

          Assuming your current relationship to Labels is called Entries_Labels...

          Put a button in the portal that does this:

          Set Field [_gLabelID ; Entries_Labels::LabelID]

           

          There are lots of ways to format the button in the portal, depending on your use case.

          If none of the fields in the portal are editable, then the button can be transparent and be set to the same size as the portal row.

          Send it behind the fields so it doesn't cover the data when it highlights.

           

          You can also give the user an indication of which row is selected by setting the Conditional Formatting of the button.

          Set the color to something you like based on this calculation:

          Entries_Labels::LabelID = _gLabelID

           

          Mark

          • 2. Re: Accessing related portal data in the same layout?
            BMyers

            I'm still pretty new to FMP (I'm an exec and not a dev, so I'm learning over time).  The use-a-global-for-a-selector technique has been very useful.  I'd suggest learning it as it will be a great addition to your personal toolkit.  It's really pretty easy once you get the hang of it.  You want a simple method?  This is the simple method.

             

            It's really just two steps:

             

            1.  Click on an item from the first portal (Entries) and a script takes the Record ID of that item and puts it in a global field.

            2.  The second portal (Labels) displays all the records related to the chosen Entry by filtering on this global selector field.

             

            That's really all there is to it.  Create a global field, and use a one-line script.

             

            Details:

            1.  A script takes the Record ID of a chosen item from the first portal (Entries) and puts it in a global field.

              - The script is merely a SetField step, that copies the Record ID of the one you are on and copies it to the global field.

              - Since you have Entries related to Labels, you probably have a foreign key field in Labels called FK_Entries or similar.

              - Your script line would be something like: SetField [SELECTOR::g_SelectedEntry ; Entries::PK_Entries ]

              - In English: In the table Selector, erase whatever is in the g_SelectedEntry field and replace it with the

                primary key number for the Entry I've clicked on.

              - The script is triggered by a OnObjectEnter script trigger attached to a portal row in your Entries portal.  Clicking

                on a portal row of a list then copies the Record ID of that Entry to the global placeholder field over in the

                Selector table.  We'll use that below.  In the meantime, note:

                     + A global field is personal to the specific user (so it won't affect other users).

                     + The app can access the global field from anywhere in the app, so it can be used elsewhere too.

                     + The global field is in a separate table, so this prevents record locking.

                     + It appears that the experts set up one table for all these selector-type global fields.

             

            2.  The second step is filtering the second portal to display only the records related to the chosen Entries record.  I have

            found two ways to do this:

              - Filtered portal: Create a Labels portal, and set the filter to be the g_SelectedEntry field.

              - Filtered relationship: This is a little more involved.

                     + Connect the g_SelectedEntry field from your Selector table to the FK_Entries field of the Labels table

                     + Make a Labels portal from the Labels table and the only records to show will be those related to the Entry you choose

                     + On your relationship graph you might find it best to create another Labels Table Occurrence to use to connect to

                        the Entries table to Labels.  What we're talking about here is a special filtered Labels portal.  You will likely

                        use the main Labels table for other things, so you might need a new TO.

             

            Pros and cons of the two methods:

            A filtered portal is easier and simpler, but it is a bit slower.  This depends upon on the number of records, of course.  The filtered relationship is faster, but the relationship graph is messier.  I've just come to accept that FMP is going to have a complex relationship graph and I need to get over it.

             

            My thoughts on the technique:

            Overall this becomes a really easy technique: one script with only one line; one global field; one new TO for the portal.

             

            I like how the global also keeps my place.  I can go to other layouts but when I come back it will display the record I was on before.

             

            Resources:

            Seedcode did a good video on the selector concept, and so did Todd Geist.  I found them to be confusing at first because they were solving a much more involved specific problem, how to make a new record input screen that can be accessed from any table or any layout.  This basic technique got a bit lost in the details of their higher-level design concepts.

             

            Matt Petrowsky did a video on this selector technique that was more understandable.  The section from minute 20 to 30 was the most useful for me.

            https://www.youtube.com/watch?v=ml-WF7qfMB0

             

            I hope this helps!

              - Brian