adding a constant to a relationship or building a relationship dynamically
I have 3 tables. if you visualise them, on the relationship view, in a line the two outer tables are contacts and projects.
The middle table is simply a link between the two it has two fields Contact ID and Project ID.
Contact table is related to the middle table by Contact ID and Project table is related to the middle table by Project ID
A contact can be related to many projects and a project can be related to many contacts.
I have a portal on my projects view that shows the related contacts and a portal on my contacts view that show the related projects.
I want to use conditional formatting in the portals to highlight the contact name of the project manager or the project name if a contact is the project manager.
I thought I should be able to add a field called project manager to the middle table and add a 1 or 0 to indicate if the contact was the project manger.
however I cannot work out how to make this work. If I am in the projects table view I want to say something like "if project manager = 1 " but of course as the only relationship between project and the middle table is Project ID this will always be true for any contact in the portal.
I can't build a relationship between the project and the middle table that has both Project ID and Contact ID as the project table does not have a contact ID.
The only way I can think of that may work is to duplicate the middle table twice and put them on the out side of the projects and contacts table in the relationships view
the relationship for the project manager in the projects table view woulls then be:
PROJECT TABLE --project ID---MIDDLE TABLE--contact ID--CONTACT TABLE--contact ID--MIDDLE TABLE
Even thought I think this should work I have had many times when I would like to have been able to use Lookup(Relationship AND value) ie build the relationship dynamically.
Does anyone have any ideas on this?