9 Replies Latest reply on Dec 4, 2014 7:17 AM by davehob

    Two tables, one portal

    davehob

      I find myself needing to display data from 2 tables in the same portal, which means, I think, that I have a data structure problem. The application has People (PPL), Interactions (INT) and Attendances (ATT) in 3 separate tables. I need to show a listing of all of a Person's activity (i.e. all INT and ATT in date order - i.e. a "case history"), so, I need all INT and ATT records to be shown in the same portal.

       

      Is there a clever way to pull it all together? I'm wondering about a virtual list, but probably the better bet would be to re-structure the data, with all ATT and INT records in an "Activity" table?

       

      Any thoughts or suggestions much appreciated.

       

      Dave.

        • 1. Re: Two tables, one portal
          Patrick.Briggs

          How do you need the information displayed? What is the context for your portal? What TO are you wanting to view this from? What does a sample row in this portal look like?

           

           

          I assume since you are suggesting the activity table that it does not currently exist?

          • 2. Re: Two tables, one portal
            erolst

            Dave Hobson wrote:

            Is there a clever way to pull it all together? I'm wondering about a virtual list, but probably the better bet would be to re-structure the data, with all ATT and INT records in an "Activity" table?

             

            How similar are Attendances and Interactions in your solution; what distinguishes them?

            • 3. Re: Two tables, one portal
              mikebeargie

              but probably the better bet would be to re-structure the data, with all ATT and INT records in an "Activity" table?

              Looks like you might have answered your own question.

               

              Should they be radically different in structure, virtual list is a good way to go for reporting terms. Even if a few fields are null in one record type and vice/versa, the easiest thing to do is to just keep it in one table with a differentiator flag.

              • 4. Re: Two tables, one portal
                Stephen Huston

                If the 2 tables are really much different, and you need to view different types of fields for each, how about side-by-side portals so both are displayed at once with newest at top. Rows won't line up chronologically very often, but such a view would both be easily understandable as chronology.

                 

                Putting them into a single table would probabaly still be better, and you could 13's visibility/hideWhen to determine which fields appeared in the portal for each type!

                • 5. Re: Two tables, one portal
                  davehob

                  Thanks, everyone, for the replies. 

                   

                  Patrick - the context from which I need to view the data is the PPL table - so, for a given person, I need a list of all their Attendances and Interactions, in date order.  I'm currently doing thios in 2 separate portals - see screenshot below.  The data shown is just the data and title of the "activity", with details in a popover.

                   

                  Erolst - the 2 types of record are similar, in that they each have a date, and a title.  What distinguishes them more is that "Attendances" is actually a join table, between People and Programmes.  I guess there's no reason why "Interactions" couldn't sit in the same table, just with a blank foreign key to Programmes?

                   

                  Stephen - thanks for the suggestion, re. side-by-side portals.  As you can see, that's what I've got currently, but the user particularly needs it all in the same list.  I can see why, because what they're looking for is patterns of progression - e.g. person comes for an interview, then attends a group, then gets a phone call, etc...

                   

                  So I think I'm heading for a single table.

                   

                  Dave.

                   

                  person_dtls.jpg

                  • 6. Re: Two tables, one portal
                    erolst

                    Dave Hobson wrote:

                    I guess there's no reason why "Interactions" couldn't sit in the same table, just with a blank foreign key to Programmes?

                     

                    I guess your guess is right. Good luck.

                    • 7. Re: Two tables, one portal
                      Patrick.Briggs

                      I would merge your Interactions and Activities and include an indicator that designates it's type.

                      • 8. Re: Two tables, one portal
                        davehob

                        Thanks Erolst.

                        • 9. Re: Two tables, one portal
                          davehob

                          Thanks Patrick.  It'll be a bit of an upheaval, but probably worth it! It'll actually simplify the structure in the long run.