I can't see your Relationship Graph (maybe post that one, too; and a JPEG or PNG would be nice…), but from your description it seems what you actually need is a new relationship from Client to Projects, which works via a global that you set by selecting the Proposal_pk from the Proposal portal to match it with the Proposal_fk in Projects (that was a mouthful, sorry). What you're doing at the moment is working within the normalized structure and messing with the primary keys of the actual Proposal records; you don't want to do that!
So on the tab in the second screenshot, a simple selection portal showing all proposals of this client is enough (you already have a Proposal tab to work on the details, you don't need to rehash those here). Put a button on the proposal name to grab the ID and set the global (and don't display/use the Proposal ID in the portal! Name and date should be sufficient to identify the desired proposal.)
Client (your UI TO) ----> Projects_forClient (new Projects TO)
click in Proposal Portal to set:
gSelectedProposal_ID = Proposal_fk
gives you all Projects for the selected Proposal, or lets you create new ones. You simply circumvent the Proposals TO (which in its current forms shows all Client proposals) and use a utility key to filter directly from Clients on Projects.
Hope this helps.
Thanks so much for your reply - I got pulled off the project for a bit and I'm just coming back to my issue. I'm going to dig into your suggestion now and test....
I've attached a snip of the DB, it's fairly extensive but I included the piece that I believe pertains to this issue. Although my portal data is being controlled from my current relationships, it does not appear to be changing my primary key in proposals as you suggest. It's PK is an auto-generated ID number and it's altering the ProjectName, not the ProjectID.
I don't believe I'm able to solve this with your suggestion because I want to display all projects under a client, for all different proposals on the project tab. I need to see the proposal name for specific projects in a list, as well as have the ability to add new ones in the same location (e.g., select the proposal by name from a drop-down of all proposals for that client, then add a corresponding project under that specific proposal).
Or am I completely misunderstanding your suggestion?
OK, it's been two weeks, a bit difficult to get back into track; I just realized that when you say
select the proposal to relate it to I see all the possible proposals for the client (this is as it should be.) However, when I select the proposal name it fills a
Where – I mean in which fields – do you work with those value lists you created? Or do you set a flag in a related proposal record? Your ERD only shows natural tables, there are no filtered UI tables or selection globals; that's what I mean when I say "you work within the natural structure". When a new project is created, how does proposal_fk know where to pull it's value from?
Try this example and see if it get's a bit clearer. You could use the same machanism on another to select a project and show its surveys; or you could combine everything in the Client - Proposals - Projects - Surveys hierarchy on one layout/tab. But don't forget that you need a new global field for each entity you want to temporarily select.
testDatarunner.fp7.zip 9.4 K