Forum Discussion

JenBlack's avatar
JenBlack
Qrew Member
10 months ago

Calculated field spanning records on multiple tables

I have two tables currently: Projects and Tasks (the Projects table being the parent). 

Only one person can be assigned to a project but a project can have multiple tasks. Each task also has an "Assigned To" field. Employees can be assigned to either the parent project or child task or both and not every project has a task.

I am trying to calculate a "total duration" across the projects and tasks table. I have a Total Duration field for each project record and a Total Duration field for each task record. I'm trying to figure out how to blend this data so I can calculate this across each team member assigned either a task or project. The following rules would apply:

If user is not equal to user in the Assigned To field on the project table and user is equal to user in the Assigned to field on the tasks table, then value = Total Task Duration; 

If user is equal to user in the Assigned To field on the projects table, then value = Total Project Duration.

If user is equal to user in the Assigned To field on both the projects table and the tasks table, then value = 0 (since they will already be getting credit for the duration associated with the project)

My inclination was some sort of skinny table with a pipeline that creates a record for each task and each project, including lookups to the Assigned To fields. Then we would use that table to calculate this duration total on these blended records.

Has anyone had to do something similar? Does my solution sound like the most efficient? 

If you made it this far, thanks for any thoughts you may have! 



------------------------------
Jen Black
------------------------------
No RepliesBe the first to reply