4 Replies Latest reply on Feb 26, 2010 9:36 AM by philmodjunk

    Another relationship issue and portals/lists



      Another relationship issue and portals/lists


      After hours of poring over this forum, I'm starting to see it as a type of soap opera, all about complicated relationships.


      I'm well along on the project I've been working on after a lot of help from folks here, including a complete restructuring of my tables.  The database is not about numbers, it's about tracking issues.


      Three main tables: Activity, Issue, and Communications.  Each Activity can have many Issues, and both Activities and Issues can have many forms of Communications.  All work fine.



      Activity Table                Issue Table                                     Communications(1)                            Communications(2)


      ActivityID_PK               ActivityID_PK::ActivityID_FK              ActivityID_PK::ActivityID_FK                IssueID_PK::IssueID_FK



      Each Activity record AND each Issue record have fields for ApprovedBy, which can only be entered by logging on as a Manager and is auto-entered via a button for approval.  Each time a record is changed by a User, the ApprovedBy field is automatically reset to empty and a Manager must then approve it again.  Once a Manager determines that an Activity or Issue is finally closed, then the record can be locked to prevent further changes.


      My problem is with the penultimate layout I'm trying to create that will load automatically each time a Manager logs on and provide a layout with a list/portal of all Activity records and Issue records needing approval.  It would have a button to take the Manager to the specific record for review, modification, and approval and then a button to take the Manager back to the layout to select the next record needing review/approval.


      There's also a fourth table that I call Admin and which I created to host the menus and help pages.  I thought that if I set AdminID_PK::AdminID_FK in the Activity table it would then allow a layout created on the Admin table to "see" all the items in the Activity table and through that to the Issue table.  This way I could create two portals on the layout, one for Activities and one for Issues, to display those needing approval.  I would then refine through scripts what is displayed in the portals to just those requiring approval.  Alternately, it could be a list/report, but that can get complicated as not all Activity records necessarily have Issue records.


      However, the portals will not work.  Creating a layout based on the Admin table and then creating a portal "sees" both the Activity table and Issue table, but when I go into browse mode there's nothing there.


      Any recommendations or guidance?







        • 1. Re: Another relationship issue and portals/lists

          I'd check your fields used in your relationships to make sure they have the values you expect. What you describe should work.


          Here's a demo of what you describe you can compare to your file: http://www.4shared.com/file/229687894/8eec74b5/MultiRelPortals.html

          • 2. Re: Another relationship issue and portals/lists

            I'd check your fields used in your relationships to make sure they have the values you expect.


            Frustrating -- nothing.


            I did compare the file to the example you provided.  To me, they look exactly the same.


            I created a new table (Test) and replaced the Admin table.  No change.


            I can't figure out what the fields itself could do with it.  I tried putting almost all the fields in the portal, and none show up in any way.  Everything is completely blank.



            • 3. Re: Another relationship issue and portals/lists

              Well, I think I see the problem.


              I did not add the AdminID_FK field to the Activity table until after a number of records were created.  Thus, the AdminID_FK field is not populated and when I create a layout based on the Admin table, it does not see the records in the Activity table.


              I manually populated the AdminID_FK fields in the Activity table, and I can see them now through the Admin table.


              But, that means that I must enter new Activity records through the Admin table?



              • 4. Re: Another relationship issue and portals/lists

                You could do it that way, but it's not the only option.


                I'm assuming here that each activity record should be assigned to a an administrator. Make the AdminID_FK field a required field and format it with a 2 column value list where Column 1 is the administrator ID and column 2 is the administrator's name. Then your users will be selecting the appropriate administrator each time they create a new activity record.