I have two lists. A Task list (custom list, also works as a time sheet) and a Project list.
Example Task list:
Proj.no. (lookup from Project list), PlannedHourTask (numbers) and HoursWorkedTask (calculation from sublist). I also have a hidden column that is a copie of WorkedHoursTask (to be able to archive the tasks with hours worked).
Example Project list:
I want all hours worked (hours from task list) to be summarized in the project list under the right project no. Does anybody have any suggestion on solution?
Depending on how frequent the total worked hours need to be updated, you could go for one of the following:
Keep in mind that calculated columns may prove tricky to access in some of the workflow actions.
I've tried to create a site workflow but don't get it to work. Can you give me a detailed description of how to set it up? I'm new to nintex and these kinds of workflows.
In the (scheduled) site workflow, you start with collecting all item ID of the Project list items using a Query List (if required you can filter on active projects only), and store the result in a collection wf var (e.g. collProjects).
Next you have a for each loop using collProjects, storing the current item in idCurrentProject,wf var. For this you get the Project no (Set Variable or Query List, eg txtProjectNo). This you use in a Query List on the Task list, filtering on Project No = txtProjectNo, and store the found hours in the collection collProjectHours. Set a wf var numSum to 0 and use a Foreach on this collection to add the current value to numSum. Next you can store the numSum value in the current Project list item.
Is this short description enough to get you going?
Thank you. I have a couple of questions since I don't get it to work. What kind of variable is idCurrentProject? I have stored it as a List Item ID. Is that correct? And I have problem with "For this you get the Project no (Set Variable or Query List, eg txtProjectNo)". What are the steps? I don't get it to work and I think this step is were I have done wrong. And just to be on the safe side, numSum is variable type: number. Right?
idCurrentProject is indeed of wf var type List Item ID; I often put the type in lowercase at the start of the workflow variable.
And numSum as such is indeed of type Number.
When you loop through each found project, you only have its list item ID. Because you want to lookup all associated project hours, you need to first get the project's Project No, for which you can user either Set Variable (to set txtProjectNo with a List lookup in Project List where ID equals idCurrentProject and retrieve Project No) or Query List (nearly the same config as the Set Variable). With the resulting txtProjectNo you can lookup the hours in your Tasks list.