How to Use SharePoint List Workflows to Track Statistics
So recently some of my cohorts and I discovered a fun and quick baseball dart game. The rules of the game are fairly straightforward. There are nine innings (or more if needed due to a tie). In each inning, each player gets to throw three darts at that inning’s number on the board. Whoever racks up the most points by the end of the game wins.
Since the game is quick (averaging under five minutes), we decided to start our own 162 game league. So, how does this relate to SharePoint 2010 List Workflows?
The answer is that SharePoint doesn’t have to be all business and no play. It’s a handy tool that can be used to track dart league statistics and standings too!
Before getting started building my league tracking in SharePoint, I came up with some general requirements:
- Ability to track league standings
- Ability to track individual performance
- Ability to track league games
- Minimize data entry
Based on these requirements, I would like to simply enter the results of a game and automate the rest of the data entry:
1. League Standings
This is the standings list. The only other important thing to note is that the “Player” column is unique so there cannot be more than one item with the same player name. Also, note that I’m using calculated fields to populate a couple of these fields.
2. Individual Performance
This list will be used with different views and filters to track each player’s game performance against each other player.
3. League Games
This list will be using the workflow that I’m showing you today.
How to create the SharePoint 2010 List Workflow:
There is a lot of information required to create new items in those lists (15 required fields total to be exact). I don’t want to be adding all of that data in manually. Not to mention the fact that if all of that had to be updated manually, then there is a lot more room for error.
This is where the list workflow comes into play. I want to add as little information as possible and automate the rest. Since I only want to add the least amount of necessary information, I’m going to create a workflow on the “League Games” list that will automatically create and edit items in the other two lists whenever I add a new item to the “League Games” list.
So let’s select the “Edit List” option to open the list in SharePoint Designer 2010.
Once I’ve opened the site in SharePoint Designer, I’m going to go to “Lists and Libraries” and select the League Games list. After I’ve selected the list, I need to create the workflow.
It will ask for a name and a description (optional). I’ve named this workflow “Update League Lists”. Now I need to make this workflow that I’ve created be able to do something.
There are a lot of different options available here, but I’ll just give a brief overview of the features that I’ll be using.
- Action: Fairly self-explanatory. Actions can do a lot of different things, but in this example I’ll mostly be creating and updating list items.
- Condition: Performs an action if some condition is met.
- Step: This is just a container to group actions and conditions together. These actions and conditions will be completed before moving to the next step in the workflow
- Local Variables: These are variables that can only be created and used within the workflow itself.
So there are three overall steps that I want to perform in this workflow. First, I want to create some local variables that will make the workflow easier to maintain and follow. Then I want to update my other two lists. Now let’s create some variables by selecting the Local Variables icon at the top of the screen:
I’ve created two local variables that will hold the winning and losing player’s list ID from the standings list. I’ve also created some variables to store some player stats that will need to be re-calculated.
Now that I have created these local variables, I need to set them to the necessary values. In Step 1, I am going to use the “Set Workflow Variable” action:
First, I need to select the workflow variable that needs to be set by clicking the “Workflow Variable” link. Then I need to set the value by clicking the “Value” link and then the “Define Workflow Lookup” button:
- Data source: This will be the list or workflow variable that that contains some value that you need to retrieve.
- Field from source: If you selected a list for the “Data source” then this will be the column you want to retrieve the data from. Otherwise, if you want the data source to be a workflow variable then you would just select the variable here.
In the above example, I need to set my local variable, winningPlayerStandingsID, to the list ID of that player in the League Standings list. So when I select the list, the dialog box changes a bit so that I can find the specific list item that I need:
- Field: This is the field from the data source that we’re comparing against.
- Value: This is the value that we want to find in the above field.
Basically, what this is going to do is look at the “League Standings” list and get the list ID of the “Player” from that list who has the same name as the “Winner” of the Current Item. (The current item is the list item that triggered the workflow. In this case, “current item” is my League Games list item that I have manually created.) I’ll do the same process to set the losingPlayerStangingsID to the ID of the “Player” who has the same name as the “Loser” in the current item.
Now that we have the ID variables set locally, it’s time to set some of the other local variables that will need to be re-calculated. Here I am using the “Do Calculation” action to set the winnerNumOfWins variable by calculating the number of wins the winner currently has and then adding one:
Then I repeat this process for the other variables that I need to set.
Next, it’s time to create Step 2. In this step, let’s create the new list items in the “Individual Performance” list with the “Create list item” action:
Here I am creating the list item for the winner. I’ll do the same thing for the losing player, but I’ll switch the player/opponent, win/loss, and runs scored and against.
Once I’ve created both of these actions, then it’s on to Step 3 to update the “League Standings” list using the “Update List Item” action (this is where all of the calculations that I assigned to those variables in Step 1 will come in handy):
Here I am updating the winner’s item in the “League Standings” list. The only things that will need to change for the winner in this list are the number of wins, runs scored, and runs against. Thankfully we had variables all ready to go do perform this action.
Next, I need to update the losing player (using the other variables and updating the Loss column instead of the Win column!). At the end, here is what my workflow looks like:
Next, I go to the workflow’s settings in Designer to check the “Start workflow automatically when an item is created” option since I want this to get triggered whenever we’ve finished another game.
Then it’s time to save and publish the workflow so I can test it!
Now it’s time to see this workflow in action:
After hitting save and confirming that the workflow has run, the other lists have been automatically updated: