I am just not sure how to make connection between three tables. The relationship already exists, it's just that the task resource assignment is not automatically assigned to the related project of the related task. Any help would be appreciated.
------------------------------ Mike Nouri ------------------------------
Do you need the Projects table and the Task Resource Assignment tables to be directly related or are you just looking to pass information via lookups and summary fields down from Projects to Task Resource Assignments and then back up?
If that is the case if the three tables are related so that Projects have Tasks and Tasks have Task Resources you can pass a lookup field from Projects down to Tasks via that relationship and then pass the lookup field down again from Task 1 to Task Resource Assignments via the relationship between those two tables. That way your information is pouring down from the top by passing through the two relationships, and you can do the same back via Summary fields by summarizing data up from TRA to Tasks and then from Tasks to Projects. That way you aren't needing a whole other relationship just to move data around.
I am a co-worker of Mike's and we are still running into an issue. Our overall issue is that [Project ID] from the project table is not showing up for some records on the Task Resource Assignments Table. I considered using the pass-down method, however the [Task - Project ID] is not linking to the Project record. This is a good back up, but not ideal since the users will need to use extra steps to navigate to the Project record.
There is actually already an existing relationship between the Task Resource Assignments table and the Projects table, and some of the Task Resource Assignments are displaying the Project ID through that relationship. A few of them are blank, but still show the correct [Task - Project ID] (hence the graphic showing the indirect relationship). This is where I am getting lost. Why does the relationship only work sometimes? How do we get the unrelated Task Resource Assignments to relate to the Project?
Any insight would be greatly appreciated!
------------------------------ Sarah Shimizu ------------------------------
I have seen that happen before with set up where a grandparent table (Projects) is also related directly to the grandchild table (Task Resource Assignments). This most often happens when a user adds a grandchild record directly from the child table in the middle and they do not manually select to fill in the related Project field. Unless you have edited the auto generated button (Most likely named Add Task Resource Assignments) that creates a grandchild record from the child table so that it also passes down the value to the Project ID field then it won't auto fill in, so when users add a Task Resource Assignment from Tasks if they forget to pick the right Project record it will not come down automatically. Do you know if it is possible if that is what is happening in your case? That can be fixed by updating your button to not just pass down the right Related Task from the Task table to the Related Task field in Task Resource Assignments but also passing down the Related Project field from Tasks to the Related Task field on Task Resource Assignments table.
