Jennifer Mason

In this post I would like to give an example of a scenario within SharePoint 2010. Since this is just a sample post and since I want to keep it to a minimal size, I am planning on skimming over the details and not providing step by step instructions for the entire example. Instead, I will assume some of the basics and then go into detail on only the specifics. This particular post is based on a scenario we have internally, but I am certain this scenario can apply to many different examples as well.

 

Scenario: Time Sheet Entry

Use Cases / Components of the Solution:

• A project list that shows the total hours assigned to the project as well as the hours used to date and the remaining hours. The only users that can add items to this list are account managers. Everyone can read this list.
• A timesheet where any user can enter the time they have billed against the client. Once the entry is submitted it should automatically update the remaining project hours. Once the timesheet has been updated the item should become read only since no other changes should be made.

Solution:

• Create a permissions restricted Projects lists where only certain users can add content but everyone can read the content.
• Create a timesheet list where users can enter time used for a project. This list will contain lookups for the project and a total hours billed entry.
• A custom workflow will be created for the timesheet list that will
  • Update the Hours Used to Date
  • Mark the current item as read only

 

Steps for building the solution:

1. Create the lists and assign permissions as needed.
2. Create a custom workflow for the timesheet list that will update the Projects list.

 

From within the list, use the Ribbon to Create a Workflow in SharePoint Designer

 

SharePoint Designer will open and allow you to create a new workflow. You will be prompted to enter a name and description for this workflow.

Once we select ok, we will be redirected to the screen that allows us to build the workflow rules. By default you will see a step block called Step 1. Since our workflow will be modifying a list that only certain users have access to, we will need to instead use an impersonation step. This means that the workflow will run in the context of the workflow author and not the current user. This means that since the author has full control, we will be able to modify any list and set permissions on items (which is not something that the current user will have permissions to do). We can add the impersonation step to the workflow and then delete the default Step 1.

 

Once you complete these two steps, you should have something similar to what I have below:

 

Now, we will need to build the logic within our workflow. Listed below is a screenshot of the logic for this example:

 

When you complete the workflow, your screen should look like the screenshot above. I think there is value in walking through the creation of each of these items, so we will spend the rest of the blog working through them. The first thing we are going to do is to compare the Submit as Final field to ensure that the value is yes. This means that in order for the workflow to start, the user editing the timesheet entry must first select that the entry is ready to be submitted. This step is accomplished using the "If Current Item Field Equals" condition. Once you had the condition added you can simply click on the blue text to enter the parameters.

 

 

Next, we want to capture the current number of hours used from the projects list. To do this we will use the "Set Workflow Variable" action.

 

When you select the workflow variable entry, select to create a new workflow variable and name it "Hours Used to Date". Select the Value entry to configure the lookup to the projects list. The screenshot below explains the logic that is being used for this example.

 

Next, we will want to use the action "Do Calculation" to add the variable we just created with the current hours in the project. The results will get stored in a new variable, which we are calling "Updated Hours to Date".

 

Now, we will need to take this new total and update the value in the projects list. To do this we use the "Update List Item" action. We will need to configure this to update the list item that matches the corresponding project list entry. This will be very similar to the configuration that we had above.

Here is a screenshot of the configuration screen for the "this list" option.

Our final steps are to remove the contribute permissions and then add the read permissions for the site members. We will use the "Remove List Permissions" and "Add List Permission" actions.

 

The steps to add the read permissions are essentially the same as removing the permissions. Once we have this step completed, our workflow is ready to go. The screenshots below show the before and after for both the projects list and the timesheet list. I am running the example using an account that is a member within my site collection and therefore once the workflow is completed, I will only have read permissions on the item.

 

Project List Before:

 

 

New Timesheet Entry:

 

 

Workflow status of complete:

 

Timesheet list item is now read only:

 

 

And finally, the projects list has been updated with the new total:

 

 

This post is just an example of how you could get started to do some of the various configurations of having one list dynamically update the values in another list. Hopefully you found it helpful and are able to apply it to many scenarios other than the one listed in this post.