6 Replies Latest reply on Apr 14, 2016 1:47 PM by mjbenun

    Consolidated Notes Layout

    mjbenun

      I have a database with 3 main tables- I'll call them Table A, B & C for simplicity.  A is 1 to Many to B, which is 1 to Many to C.

       

      Each of the 3 tables has a Notes field which with a script that inserts Current Date and Username for the person entering a note into that field.

       

      I am trying to structure a layout that will show ALL recent notes on 1 page- from all 3 layouts.  I'm trying to do this from the context of Table A, since that is the highest level (parent) table.  Not sure how to do this to show ALL notes, however across all records/all tables.

       

      Any advice and how to go about this would be appreciated.

       

      Thanks

        • 1. Re: Consolidated Notes Layout
          erolst

          I suggest you consider a consolidated Notes table as a child table to A, B and C.

           

          This not only allows you to define fields to cleanly separate data (the actual note) from metadata (user, creation timestamp), and give each note its own record – instead than cramming them all into one field – but also lets you use this table for all future tables (almost) without adjustments.

           

          Caveat: Your tables should have primary keys that are unique across the entire database (e.g. UUIDs), not just within their own table (like simple auto-incrementing serial numbers).

          • 2. Re: Consolidated Notes Layout
            patricia

            Like exist stated, you should have a unique foreign key which is sent from A to the other tables. Create a one to one direct relationship from A to each table using that key.

             

            patricia

            • 3. Re: Consolidated Notes Layout
              rgordon

              Erolst I was also going to recommend a separate table also but then I thought maybe your favorite List function might work if all that is needed is to view the notes.  You could use a calc field that combines the A note, with a list of B notes and a list of C notes.   Then display the calc field on the layout.

              • 4. Re: Consolidated Notes Layout
                erolst

                patricia wrote:

                Like exist stated

                You may want to keep your auto-correct on a tighter leash …

                 

                And btw, what I suggested isn't really what you suggested – which is piping the grandparents' primary key all down the line to whatever ([great-]grand-)child tables there may be; this is viable to a point, but not really scalable.

                • 5. Re: Consolidated Notes Layout
                  mjbenun

                  Thanks for all the input, let me see if I can implement this.  I'll be back..

                   

                  Thanks again for the quick responses..

                  • 6. Re: Consolidated Notes Layout
                    mjbenun

                    Just wanted to let you know this worked beautifully.

                     

                    The Light Bulb went off when you suggested a separate Table for Notes

                    .

                    I linked it up with the other 3 tables as Child>Parent with a global Field that has the same value throughout all tables.

                    Then I put 3 portals on my layout, so I can see where the notes are originating from and I filtered the Portal to show only fields with data.  Thanks for the help!