On 2011 Jan 25, at 21:26, Jason Bourque wrote:

> Hello,
> 
> This is driving me nuts. How do you hide portal records based upon a third
> table relationship?
> 
> Three tables
> 
> Table "Projects" is linked to table "Related Keys" which is linked to table
> "Tasks" This allow the projects to have many tasks.
> 
> What I want to happen.
> On a layout I have two portals. The first portal shows records from table
> "Tasks" through the relationship above. So tasks that belong to the project.
> 
> The second portal is a new table occurrence of "Tasks" with no
> relationships.
> I have a script that adds new "Tasks" to the "Project" by adding records to
> "Related Keys" I want those task to hide in this second portal. So I can
> easy find other tasks.
> 
> I tried to use combo key of both the Project Unique ID and Task Unique ID
> and an ifempty check to see if there where any records that matched in the
> Related Keys Table.
> This was triggered by a layout on enter trigger that set a global Table
> Variable which was concatenated in a field in the Tasks table. This didn't
> work.
> 
> Any ideas would be grateful, before I lose any more hair.


One question before we spend unnecessary time on this, Jason. The main reason you'd have a merge table (AKA a join table or a link table) between "Projects" and "Tasks" is to support a many-to-many relationship.

For example, consider "People" and "Addresses". A given person may have multiple addresses (home, work, summer, former, etc.), and a given address may be common to several people (mom, pop, junior, sis, etc.). Thus you need that intermediate table to record all the legal COMBINATIONS of people and address.

But that doesn't sound like the deal here. It sounds as if each task belongs to one and only one project. In that case you have a one-to-many relationship and can dispense with that intermediate table, which should make the rest of your life easier.

So, baseline question, CAN a given task belong to more than one project?