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?