project hours worked to be sum of tasks hours worked.

Question

i have a sharepoint list called project . this list has column called hours worked. Then i also have a list called tasks. this list also has a column called hours worked. the task list also has a lookup field where we select project ID from project list.

Thus for each project we can have many tasks.

now tasks list items are created by individual users and i have to create such a mechanism that the hours worked in project list must always be the sum of hours worked in tasks of that project.

How can I achieve this.

Answer 1

You need to SLAM the lists.

Or query all tasks that reference to project with such ID and then loop those tasks, summing up the hours.

Answer 2

You need to put ItemAdded, ItemUpdated and ItemDeleting (with ItemDeleted you wouldn't be able to know what project the task belonged to) event receivers on tasks, and all of them will be calling the same function.

The function will get the project list item from lookup in the task. You then do a CAML query on tasks, to get all the tasks for that project, using the ID of the project and LookupId attribute, like this: <FieldRef Name='int_field_name' LookupId='TRUE' /><Value Type='Lookup'>value</Value>

Do your calculation on them, and write the result into the project list item with SystemUpdate(false). Bam!