cancel
Showing results for 
Search instead for 
Did you mean: 
amie_peterson
Nintex Newbie

Calculate Totals from one SharePoint List to another

Hi,

I have two lists. A Task list (custom list, also works as a time sheet) and a Project list.

Example Task 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:

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?

Labels: (2)
Tags (1)
Reply
5 Replies
jpmhuls
Nintex Newbie

Re: Calculate Totals from one SharePoint List to another

Depending on how frequent the total worked hours need to be updated, you could go for one of the following:

  1. Scheduled site workflow to run at night and going through project list items and lookup for each project the worked hours in the task list.
  2. List/re-usable workflow on the Tasks list which runs when worked hours is changed, triggering a site workflow which for the current Project item ID gets all tasks and sums the worked hours

Keep in mind that calculated columns may prove tricky to access in some of the workflow actions.

0 Kudos
Reply
amie_peterson
Nintex Newbie

Re: Calculate Totals from one SharePoint List to another

Hi,
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.

0 Kudos
Reply
jpmhuls
Nintex Newbie

Re: Calculate Totals from one SharePoint List to another

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?

0 Kudos
Reply
amie_peterson
Nintex Newbie

Re: Calculate Totals from one SharePoint List to another

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?

0 Kudos
Reply
jpmhuls
Nintex Newbie

Re: Calculate Totals from one SharePoint List to another

Hi Amie,

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.

0 Kudos
Reply