8 Replies Latest reply on Feb 8, 2016 7:22 AM by beverly

    Portals w/ Multiple Table Occurances

      Is there a way to create a portal that shows records from a few different table occurrences in the same portal?

        • 1. Re: Portals w/ Multiple Table Occurances
          jbrown

          Hi.

          A portal is a 'window' into another table occurence,showing related records. The portal can be based on only one occurence at a time. However, you can include fields from other related Table Occurrences–children TOs of the portal-based TO.

          For example, if I have a INvoices / Invoice Line Items / Products scenario, I can be on the Invoices layout, have a portal into the invoice line items table, and include the product name from the Products TO in the portal. As long as the Products is related to the Invoice Line Items table, this will work.

           

          I'm not aware of any way to show different records from different TOs.

          • 2. Re: Portals w/ Multiple Table Occurances
            Mike_Mitchell

            Investigate the Virtual List technique. You can merge data from different tables and display it by creating a list of return delimited values, that can come from wherever you want.

             

            Also, I'm assuming these TOs are not related to each other. You can show data from any related TOs in a portal (although the result may not be what you want, depending on the relational structure).

            • 3. Re: Portals w/ Multiple Table Occurances
              jbrown

              Arg. I should have thought of this. I wrote a blog post about it: using the virtual list technique to combine many different TOs into a report, which is an amazing but direct use of the technique.

              • 4. Re: Portals w/ Multiple Table Occurances

                Nice one, thanks guys!

                 

                It's quite a simple relationship - the intention is to show a portal on the Core.ADUser layout which has just the one portal showing each asset belonging to the user regardless of if the TO is from Computers or Mobile Phones

                TO.PNG

                • 5. Re: Portals w/ Multiple Table Occurances
                  Mike_Mitchell

                  You have a few choices:

                   

                  1) Change your data model and put all the assets (regardless of type) into a single table. Differentiate with a field. (It's usually a poor practice to have a table for each "type" of something; you wind up having to create a new table just because a new type of object is being tracked.)

                   

                  2) Create a join table between users and assets, and put the keys for the three tables in it. (This is an option if the two asset types are so radically different from each other that combining the tables is impractical, which sometimes happens.) Then, each record in the join table represents a connection between a user and an asset, and you can just show the portal from that TO. Example:

                   

                  User >--< Asset >--< Phone

                                          >--< Computer

                   

                  3) Go with the Virtual List or other reporting technique.

                  • 6. Re: Portals w/ Multiple Table Occurances
                    beverly

                    #1 may be impossible (or at least improbable), as the screen shot shows, some of the tables are from SQL (the dbo.___ was the clue in the name - although these are T.O.s so I would rename them on the graph to make them more useful elsewhere within FM)

                     

                    #2 is a technique I often use. Multiple foreign keys in a Join table and you have a Portal from which to display several things.

                     

                    #3 is an easy way to "temporarily" gather data and very useful!

                     

                    doing suggestions #2 or #3 may depend on what else you need to do with the information.

                    beverly

                    • 7. Re: Portals w/ Multiple Table Occurances
                      Mike_Mitchell

                      Well, I couldn't really see the table structure very well (although the "dbo" bit may have been a clue). I was just trying to get the principle across.

                      • 8. Re: Portals w/ Multiple Table Occurances
                        beverly

                        It was a good suggestion, if the user has access to manage the external data source (most people do not). As SQL DBA, that's the kind of thing I did - OK, I designed it correctly the first time. LOL

                         

                        However, we don't have the luxury of changing these kinds of sources, especially if they are used with other apps (such as web published). But it was a great suggestion none-the-less, Mike!