Search This Blog

Saturday, December 17, 2011

Sharepoint Part 9: Custom Project Systems on a Shoestring

Develop your own project systems using Free Tools, just add labor


Using what you've built - more free enhancements

In this final session, we will look at expanding your productivity and the benefits of using Sharepoint - now that you have a system in place. By now, you will have some live data in your system, and want to be able to do more with it. Read on for some powerful tools and methods to work with your Sharepoint data.

We will take a look at several examples of using Sharepoint with external tools as high-value add-on features:
- Timesheet integration
- One-click status reporting
- Forecasting/Capacity Planning
- Billing Audit

And then
- How to do it yourself!

Timesheet Integration with Sharepoint
Excel is a powerful and flexible tool. In our system, we designed a timesheet spreadsheet that allowed resources to track their time in a more traditional grid model (Project / Date  / Hours). However, they also included two dynamic queries linked to Sharepoint to ensure the latest information and newest projects are always available, as well as all time the resource has entered in the last 30 days, for cross-validation purposes


(Why a separate timesheet? Our clients needed the traditional form, with task hours by day. It is also useful for cross-checking.)

In our company, Sharepoint drives client billing – so this timesheet also provides validation that all time worked has been recorded for billing. When the resource opens the spreadsheet, they are challenged with their Sharepoint login – so that the spreadsheet can go and fetch the latest results from the two linked Lists on the live Sharepoint site.

Note: Although technically possible, we chose not to have the spreadsheet feed back time into Sharepoint – we wanted a single point of entry and no possible conflicts with manual vs timesheet importing. However, the validation feature in the spreadsheet to confirm you have recorded your time in Sharepoint (which is what will be invoiced) works very well.


The Project Assignment records are structured so that time can be entered incrementally, or as a lump of time at the end of the week, depending on the duration of the task and granularity needed for the end client.


Note: The time entry has an Effective Date (defaults to today).

Project billing info is populated by selecting the project drop-down & task type, which pulls data from the dynamic Projects query linked to Sharepoint using VLOOKUP.








Benefit: All project billing info centrally maintained = no billing typos on the timesheet or invoices to the customer!

Each resource can verify all Project Assignment time entered in Sharepoint, using the built-in “My Time Entry-Last 30 days” dynamic query linked to Sharepoint.




The timesheet also supports export for “end client” timesheet system import, when needed.
 

One-click Status Reporting
Using dynamically linked queries from Sharepoint based on a suitable view, you can create a spreadsheet that contains either raw or processed status on all of your project items - and all you need to do to refresh it is open the spreadsheet and login to Sharepoint!

And don't forget Autofilter in Excel - very handy when you want to distribute status to a variety of team members and stakeholders, and they want to quickly filter for themselves.

Forecasting/Capacity Planning
Because our system is used for active project tracking and new work, we have information in Sharepoint that is useful for not only showing actuals, but for forecasting by resource based on booked and potential new work (pipeline) within the calendar year.

We have produced a weekly forecast spreadsheet with per-resource and per-customer views, with quarterly roll-ups and unbooked pipeline, and pivot table analysis - all driven from a single linked query from Sharepoint.

Resource view - Quarterly/weekly





Customer view - Quarterly/monthly

Note that this solution does require some additional custom fields in order to drive the statistics, which are included in the output query. But it is an example of what you can do with a little ingenuity.

And like all of the other spreadsheets, just open, login and run a refresh macro - and you have a complete forecast/capacity planning update for the execs - in minutes, not hours or days.

Billing Audit

As we also do our invoicing from Sharepoint, we have all relevant time tracking data for invoicing, which can be used for reconciling invoices to raw time - which can be very useful for conduting billing audits with clients, if there is a question on some invoice items.

Of course, you can always lookup individual items - but it is very handy to be able to export the data for analysis in a form that can be shared with the client. In this spreadsheet, the raw source time entry items are all brought in - with project number, resource, task, date, "billing week#", hours and other information, which is then analyzed using pivot table, where the auditors can break up the information any way they like by changing columns and rows in the pivot table.



And again - the spreadsheet is reusable in future by simply opening it and logging in to Sharepoint to get the latest data.

How to do it yourself - working with Dynamically Linked Queries
In Sharepoint Lists you can use the “Export to Spreadsheet” function:
and then incorporate that dynamic query (*.IQY file) in a tab of your spreadsheet and then use the results on other tabs and in formulas.

Steps:
Open the *.IQY file.
Note: You will need to login to Sharepoint.
You will also be asked where you want to put the data from the linked query - into an existing workeheet, or a new workbook.
If you want to start a new spreadsheet, then choose "New Workbook", otherwise, specify an empty tab to put the data into. When you include the linked query in a spreadsheet, the entire range of cells is outlined in blue.

*Note that based on your query (view) definition that you exported from, the records pulled into Excel may differ each time you open the spreadsheet. For example, the view-query may look at the last 30 days of activity, or it may be a continually growing list.

You can then create a simple or sophisticated spreadsheet that uses the results of the linked data pulled into Excel from Sharepoint. Note that this is suitable for tabular data processing, including rollup calculations, VLOOKUP, pivot tables, etc.

Caution: As the range of cells in the dynamically linked query can change, any formulas that you based on the query data may suddenly become invalid, i.e. the last time you had 1000 rows, but this time you only have 850 - the lost "150" are treated as if you had deleted the cells with CTRL-X.

The workaround for this, however, is relatively simple. As long as you can be sure there will always be at least one record returned from Sharepoint, you can use an Excel macro to copy/paste the first row of formulas to all other rows.
Then all of your results will be consistent with no "breaks". Likewise, if your spreadsheet uses pivot tables, those need to be refreshed as well - but that can also be done by macro.

That's All Folks!

This is the end of this particular series - but not the end of the journey. Keep checking back for other Sharepoint articles from time to time - and for the other topics in my blog. 


Thanks for reading, I hope you found it helpful. This is the culmination of many (many) hours of scrounging the web and figuring out bits and pieces as I went along, from many different locations and postings (plus a few books and lots of experimenting). I hope this helps you get a quicker leg-up on developing your own systems.


And of course, if you need further specific help (or help to you build your own system), I can be contacted on a consulting basis if desired.

Cheers!
Gary Nelson

Go back to:
Part 1: The Need
Part 2: Designing the Solution
Part 3: Building the Structure - Sharepoint Lists
Part 4: Process Workflow Design
Part 5: Implementing Custom Workflows with Sharepoint Designer
Part 6: Advanced Workflows with PowerShell
Part 7: Hosting tips, Email & Notifications
Part 8: Doing it Yourself - Recommendations

Other Sharepoint Articles

For more information or questions, contact:


Gary Nelson, PMP
www.gazzaconsulting.com

Friday, December 16, 2011

Sharepoint Part 8: Custom Project Systems on a Shoestring

Develop your own project systems using Free Tools, just add labor


Doing it Yourself - Recommendations

If you don't know where you are going, any road will get you there. But it may not be where you want to end up.

Plan it out.
Figure out what you need to do (the high-level requirements). Brainstorm!
- What do you really need?
- What do you need a system to do for you?
- What problems/pain points are you trying to fix?
- What works with your current processes (manual or otherwise)?
- What doesn't work?
- What are the inputs?
- What are the outputs?
- What are your "gates", decision points and start/end stages?

Gather the ideas from your team, look at how your business processes work (or don't work), and put them together for discussion. Draw it out, use post-its, do your best to cover the various "what if's" that you know of or may happen.




Design the model
With the needs and wants identified, the inputs, outputs, key decision points etc - it is time to put pencil to paper. Mock it up. Draw the high level workflows and review with your team – make sure you do several passes to make sure you have covered all of your key needs.

Then break it down to the next level. Model all of the state transitions (a flow chart is suitable for this), and see if there are any holes. If you missed something, add it in, rework the model and make sure that it "works" for you.



It is important not to skip this step!

Evaluate
An important gate in your planning is determining if you actually do need a new system to help you do what needs going. Maybe you have decided you don't.

But if you do, is an off-the-shelf system close enough (and affordable enough) to do what you need? This might indeed be the case - but now that you have gone through the effort of figuring out what you really need, when you begin your evaluation of different products (vs doing it on on your own), you will be well prepared to make informed decisions. You will be better able to look past the pretty screens and glittering lights of the demo to see if a particlar package will do what you need.

Ideally, you will want to get a free trial copy (full featured, limited time period) to play with each system to really shake it out, and try modelling parts of your process workflow, and see how it works.

Do the same with Sharepoint - of course, WSS 3.0 is free with Windows Server 2005/2008, so it is just a matter of effort.

But if you do need to report on the decision to go with a particular product vs others, it is advisable to do a side by side trial - try modelling some easy bits and some hard bits in each before you pass final judgement.

And also decide if you have the people and skills to do it yourself - and maintain it. If so, great! If not, then grab some books, fire up Google and use my blog and others - or, consider a commercial alternative.

There are many benefits of doing it yourself - particularly the ability to design exactly what you want, but you just need to be prepared for it.

So you've decided to DIY - Now What?
Modularize & Prioritize
Break it down. Divide the overall solution by priority of business need. You will not need everything at once, but you should be able to determine "must have first", "must have next", "really nice to have", "nice to have", "maybe some day", etc.

Note: Document your processes and workflow logic as you go
- for user guides, and
- so you remember how you coded it!

Start with the basics. Get up and running with the essential components; don’t try to swallow the whole elephant at once. You may need to keep the “nice to haves” as paper/email/external processes until you have a chance to integrate them.

Add functionality as you need to, and as you grow. (Consider, plan then develop and test it in a contained, modular fashion). This is particularly important with Sharepoint - if you are planning to add on several pieces of functionality - I recommend you do it modularly.

If the pieces of functionality are independent - start work on one, finish it, test it and make sure it works before starting on the next item.

If some pieces are inter-dependent, develop them together and test the group of them before introducing any other functionality.

Establish your Backup / Recovery plan/mechanism from day one.
Network backup, online backup, a combination of both is fine - but do establish your backups from the very beginning. You will need, at a minimum:
- Regular server image backups (Windows or commercial products)
- Scheduled Sharepoint backups (SPBackup works very well for this) - at least daily
- Online backup service [optional]

Using SPBackup
Download the SPBackup package from Codeplex. Follow the instructions to install and use it, however here are some key points:

There will be two main files:
spbackup.ps1
spbackup.xml

The XML file is used to define the source and destination for the backup, how many versions to keep, and email notification settings.The settings below in green are recommended for "full backup" of everything. The yellow highlights are what you need to modify for your setup.

<?xml version="1.0" ?>
<confSPBackup>
   <site id="1">
 <catastrophicbackup>1</catastrophicbackup>
 <catastrophicmethod>full</catastrophicmethod>
 <sitecollectionbackup>1</sitecollectionbackup>
 <sitecollectionurl>http://mysharepointserver.com</sitecollectionurl>
 <hivebackup>1</hivebackup>
 <hive>C:\Program Files\Common Files\Microsoft Shared\web server extensions\12</hive>
 <iisbackup>1</iisbackup>
 <iisencryptopassword>MetaBackupPass1</iisencryptopassword>
 <smtpserver>SMTPSERVERNAME_OR_IP</smtpserver>
 <fromemail>FROM@EMAIL.ADDRESS</fromemail>
 <toemail>TO@EMAIL.ADDRESS</toemail>
 <backupdestination>C:\BACKUPS\Current_Sharepoint_Backup</backupdestination>
 <backupdestinationmaxkeepdays>2</backupdestinationmaxkeepdays>
   </site>
</confSPBackup>


*Note that SPBackup can backup to any folder, however some online backup services will not backup from particular networked drive configurations. In this case, use SPBackup to backup to a local drive and then have the online backup service pull that backup file for safe keeping.

Schedule it
You can automate your SPBackups by creating a .BAT file in the same folder as your SPBackup files. Call it something like "SPBackup.BAT" and it should contain the following line:

powershell -command "& './spbackup.ps1' 'spbackup.xml' "

Solution Summary

Cost
- 1 Windows desktop server with Windows Server 2008
- Sharepoint WSS 3.0 Software: free
- Sharepoint Add-ons: free
- DynDNS site name: free
- Email passthru: free
- Labor to implement solution
- Books (optional: Google is very useful)

Requisite Skills
- Windows basic administration concepts
- Basic programming skills & concepts

Learn as you go (use Google, books etc)
- Windows Server 2008 administration
- Firewall setup / port forwarding
- Sharepoint Administration (learn on the job)
- PowerShell

Optional (Tip: Google helps with everything)
- Excel
- SSL setup
- Javascript
- .NET programming

References/Resources
Online
- Windows Sharepoint Services 3.0 SP1: http://www.microsoft.com/downloads/en/details.aspx?FamilyId=D51730B5-48FC-4CA2-B454-8DC2CAF93951&displaylang=en
- Microsoft Office Sharepoint Designer 2007: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=baa3ad86-bfc1-4bd4-9812-d9e710d44f42&displaylang=en
- Codeplex: www.codeplex.com
- iLoveSharepoint: ilovesharepoint.codeplex.com- PowerShell 2.0 (Windows Server 2008 built-in-feature, enable through Server Admin)
- Google!

Suggested Books
- Windows Powershell Unleashed 2nd Ed, Tyson Kopczynski  Pete Handley / Marco Shaw
- Windows Sharepoint Services 3.0 (Microsoft Publishing)
- Office Sharepoint Designer 2007 (Microsoft Publishing)
- Many others are also available


Coming next:
Part 9: Using what you've built - more free enhancements

Go back to:
Part 1: The Need
Part 2: Designing the Solution
Part 3: Building the Structure - Sharepoint Lists
Part 4: Process Workflow Design
Part 5: Implementing Custom Workflows with Sharepoint Designer
Part 6: Advanced Workflows with PowerShell
Part 7: Hosting tips, Email & Notifications


For more information or questions, contact:


Gary Nelson, PMP
www.gazzaconsulting.com

Sunday, October 23, 2011

Sharepoint Part 7: Custom Project Systems on a Shoestring

Develop your own project systems using Free Tools, just add labor


In this session we continue the development of our custom Sharepoint solution, discussing hosting configuration options for Internet accessibility, and configuring Sharepoint for email notifications (and inbound email). We also discuss automated email notifications through custom workflows.



Hosting tips, Email & Notifications
Note: If you are only running your Sharepoint system inside your corporate network, this section may not be as helpful, as a lot of your work may have been done for you by your IT group.


However, if you are working with dispersed teams and are hosting Sharepoint for your team on the Internet, there will be a lot of useful information for you in this topic.


Hosting - making Sharepoint visible to your team
You may face a number of options and decisions in this area.
- Do you have domain registered?
- Do you have a static IP?
- Are you serving from your corporate network to the Internet?
- Are you hosting your server from a home office?


Note: If you are hosting the server from your corporate network, you will be best to work with your IT team on setting things up, as there will be security policies and other configuration requirements specific to your network.


So- as this is a discussion of developing systems cheaply, let's start with the simple stuff first, with the model of setting up the server to share to a dispersed team over the Internet. We will assume that you are starting with a single server, hosted on your small business or home office network. You will have an Internet router (Cable, DSL or Fibre) into your network, and you will need to have access to the administrator login on the router, or get the assistance of the person who does.

The steps we need to look at are:
- How you connect to the Internet
- Setting up Port Forwarding
- Updating IIS for your external Domain (or subdomain) name


1. Where am I? Static IP vs Dynamic IP
Static IP
A static IP address means that the address for your router never changes; the specific address is allocated to you by your internet service provider. Static IP's are usually included in your Business Internet Plan at no additional cost. If you are on a personal plan, discuss the option with your provider.


If you have a Domain registered for your business and already run a website, you will already have a Static IP. (Note that if you plan to host your website and sharepoint both, you will either need to run the Sharepoint site as a sub-site on your main server, or use a non-standard port like :81)


If you would like to register a domain and have a Static IP (or need a whole site), there are a number of domain registrars and hosting providers. There are many good articles on how to do this.


Important Note: Inside your network, the default addressing method will probably be DHCP. You will need to have a Fixed IP address for the server inside your network. You will need to do this in two parts: 
a) Figure out what the Dynamic IP address range is on your router - and stay away from addresses in that range.
b) Setup your networking configuration on your Sharepoint Server to use a Static IP instead of DHCP (automatically assign). You will need to change the Adapter settings under Network Connections
 Open the Properties for TCP/IPv4.  It should look something like this when you are done:


Dynamic IP
A Dynamic IP means that your provider uses DHCP to assign your router an IP address for a period of time (a "lease") and the next time your lease is renewed (often 1, 7 or 30 days), you may be assigned a new IP address.


You can still present your server to the world with a dynamic IP, however as the IP can change regularly, you need a service such as DynDNS Free  (free for up to two host names). The limitation here is that you can only choose a sub-domain from one of the DynDNS main domains, so your name would be something like "mysite.dyndns.net". But, free is free. You can always get your own personalized domain later (and a static IP).


If you use a dynamic name service like DynDNS, there will be additional configuration in your router so that it can broadcast your changed IP back to DynDNS, or you can install software on your host server to do the same. Note that the server/software version seems to be more reliable.



2. Port Forwarding

Now that you have that sorted out (router, and a name so people can find it), you then need to setup Port Forwarding through your router to the Sharepoint server inside your local network. Port forwarding "re-maps" the external port visible to the Internet to a port and server inside your network. Note that that the external port number can only be mapped once. You add a Port Forwarding rule from your Router Administration. Specific steps will vary based on system, but it will look something like this:
For Port 80 (HTTP, or default Webserver port), you will map that to the same port (unless you have defined a different port internally) on the static IP address for your internal server.



CAUTION: As you will be now exposing your internal server to the world, you want to make sure that it is properly maintained and fully patched to limit exposure to vulnerabilities that may be found in your web server (Windows Server with IIS in this case). You will also want to change "default" passwords on accounts on the server, turn off "Anonymous" login, etc. You will also want to make sure that either Remote Administration is turned OFF on your router (this is usually the default), or if you need to have remote access, use a strong password and possibly change the account name or setup a new admin account.

Tip: If someone does gain access to that server's OS (which will be quite unlikely, as you will be only exposing one port), they could access your internal network. You can increase protection of your network by placing that server in your "DMZ" (De-Militarized Zone), so that if someone does gain access to the server OS, they cannot access the rest of your network.


3. Configuring IIS for the external name
IIS will, be default, be setup with your internal server name. This will be different than your external domain (or subdomain) name, and for proper operation when accessing Sharepoint from the outside world, you need to tell IIS what it's external name is. Otherwise you will get binding errors when you try to change between certain types of pages.


On your Sharepoint server, open up the IIS Administration control panel. 
 Choose your website and click on Edit Bindings.
 Click on Add... to add the binding for your external domain (or subdomain) name.

Enter the name and click on OK. In order to make the changes effective you should also do an IISRESET from a Command (DOS) prompt window, or use stop/start in the IIS control panel.



Those are the basics for getting your site accessible to your dispersed team. Note that there are other things you can do like setup SSL, for more secure connections, but that is not discussed here. You can, however do it with a "self-registered" certificate, which will pop up a warning to your users every time they go to your site (not desireable), or you can apply for an official registration certificate - but for this you will need to have your own domain and static IP.



Email and Sharepoint - Outbound, and Inbound
Now that our system is setup, with lists, workflows working (etc), and the site is now visible to your team, we can setup the email functions for Sharepoint.

There are several steps you need to do in order to do this. You will need to turn on the SMTP service on your Windows Server (this is not enabled by default), and then you need to configure the SMTP server and then Sharepoint to use it. And of course - you need to make your workflows email out messages.


Turning on SMTP
The sequence may vary slightly depending on your version of Windows Server, but in 2008 you would go to the Server Manager control panel, and select Features. If you see "SMTP Server" listed as an active feature, you don't need to enable it. 
 However if you do not see it listed, you will need to click on Add Features.

You will be asked to reboot the server after enabling the feature.


Configuring SMTP
In order to turn on the SMTP function, you next need to go to the Administrative Services and select IIS Services 6.0 (not the newer IIS panel). Expand your server until you see "SMTP Virtual Server".
Right-click on "SMTP" and Start the service.


Setting up Sharepoint
Next, open up Sharepoint Central Administration. Click on the Operations Tab and click on "Outgoing Email Settings".
Specify the internal static IP address for your Sharepoint Server. Specify the email address you want to be sending "from" and the "reply to" address, then click OK.


Note that if you already have a business email server under your domain, you will not necessarily need the SMTP service locally on your Sharepoint server - you can use the company SMTP server instead.

Incoming Email
You do not need to turn on incoming email unless you want to support automatic addition of email messages/attachments into Sharepoint. This is an advanced feature not discussed in detail in this session. But if you do need to setup incoming email, there are a few basic steps (then you are on your own with the Sharepoint documentation).


Setup
The simplest way to set this up is to use the SMTP service on your Sharepoint server. Click on the Incoming Email Settings link.
Set "Enable sites on this server to receive email" to "yes".

Next, specify the external domain name for the display address.


Setting up Lists/Libraries to accept incoming email
The following is the basic setup for enabling incoming email into a list or document library.

In Sharepoint, log in as a Site Collection administrator, and open the List or document library that you want to receive incoming email. Then click on Settings > [List Settings] or [Document Library Settings]. You will see a screen like this:
Click on Incoming e-mail settings.
There are a number of options on what to do with attachments, and email security (who can email into the repository based on their login and email, etc), but to start with you need to turn on the ability to receive incoming email, and specify the unique email address that will serve as the inbox for this list/library. 

You can then test emailing to that email address and see items start showing up in the list. You can also get more advanced and setup iLoveSharepoint Event Receivers for processing incoming emails to do specific actions. Those are not covered here, but they use PowerShell scripting.

Advanced Email - Smart Hosting
This section applies specifically if you are using Dynamic DNS. If you are not using a registered domain with a static IP, there is the very real likelihood that you will operate fine, sending emails to your team for a month or two - and then you may find that suddenly your subdomain is blacklisted (they think you may be a spamming site). 


There is a workaround for this, however - you can use a Smart Host to relay your messages through their email server. This is a multiple-step process. We used GMail as our SmartHost, as they support "sending email as <other email address>", though this requires verification from the "owner" address when you set it up.


Step1: Create a new GMail account.

Step 2: In Gmail, under Mail Settings, click on "Accounts and Import". In the section titled "Send mail as:" click on "Add another email address as your own". Specify the email address that you want to be sending FROM your Sharepoint server, i.e. sharepoint@mysite.dyndns.net.


Step 3: An email will be sent to your Sharepoint server. However, you need to go to the SMTP inbox folder to retrieve it (i.e. under C:\inetpub\mailroot\...). Open the message file and then verify the account association by going to the link in the email.


Step 4: On your Sharepoint server, open up IIS 6.0 Manager again. Right click on SMTP and select Properties. Click on the Delivery tab.
Step 5: Click on Outbound Security. Choose basic authentication and then enter the GMail address and password here and click OK.

Step 6: Click on Outboud Connections. Make sure your settings look like this:


Step 7: Click on Advanced Delivery. Specify your fully qualified domain (subdomain) name, and the smarthost server (GMail).

That's it - you should be ready for emailing with no blacklisting!

Custom Workflows - Sending Email Notifications
When you create your custom workflows in Sharepoint Designer, one of the actions you can use is to Send an Email. You can choose who to email as a static account, or do lookups for user accounts based on a List Item (i.e. the owner of a task). In order to send an email, you will need three things:
1) Who to send it to
2) Subject line
3) Email body


If you want the email to contain variable subject text, i.e. include the name of the list item, you will need to setup a variable and then use that as the subject line.
In the action to send the email, you can specify the TO, CC, Subject and the email body.
 
 To specify specific accounts, or lookup users, click on the icon to the right.

You can use username fields in the list (or related lists) as the source of your TO and CC. You can include multiple addressees.

Finally, supply whatever email body that is appropriate, You can include image links, variables and field lookups in order to complete your message, as well as static boilerplate text. You can also include HTML links back to your site, as in the above example.



That's it for this session, I hope you found it useful!



Coming next:
Part 8: Doing it Yourself - Recommendations
Part 9: Using what you've built - more free enhancements


Go back to:
Part 1: The Need
Part 2: Designing the Solution
Part 3: Building the Structure - Sharepoint Lists
Part 4: Process Workflow Design
Part 5: Implementing Custom Workflows with Sharepoint Designer
Part 6: Advanced Workflows with PowerShell



For more information or questions, contact:


Gary Nelson, PMP
www.gazzaconsulting.com

Saturday, October 8, 2011

Sharepoint Part 6: Custom Project Systems on a Shoestring

Develop your own project systems using Free Tools, just add labor
At this point in the series, we have the basic elements of a status-based workflow structure in place. In this topic, we will be exploring the use of PowerShell with Sharepoint - both as external scripts to run on the server, as well as in custom workflows. We will cover the following topics as examples of using PowerShell to work with Sharepoint:

  • Dump any Sharepoint List [server script]
  • Conditional Mass-update of a List [server script]
  • Traverse folder structure to test or update values [server script]
  • Create folder structure in Document Library [workflow script]
  • Update multiple items in other list(s) [workflow script]

Advanced Workflows with PowerShell
Before we begin, you will need to have Sharepoint Designer PowerActivity by iLoveSharepoint installed on your Sharepoint server. This will allow you to run PowerShell scripts from within your Sharepoint custom workflows.


You may also find it handy to install Sharepoint Designer Actions, which provides the following actions in Sharepoint Designer:


  • Call a WebService (supports SOAP 1.1 and SOAP 1.2)
  • Query XML
  • Execute SQL (supports ADO Provider Model, returns a scalar value)
  • Get parent folder (returns the ID of the folder)
  • Create a site
  • Start workflow
  • Set role inheritance of an item
  • Clear role assignments of an item
  • Assign role to an item

Tip: I use the Start Workflow action to help simplify code maintenance.


Dump any Sharepoint List [server script]
Script name: DumpAnyList.ps1
This script shows the basic elements required to use PowerShell to query and manipulate Sharepoint data from a server-side script. We will describe each section of the script as we go through it.

Include Sharepoint .NET Assemblies
You must include the following text near the top of your script (server-sidePS1 scripts only, not in Workflows). This loads the Sharepoint object library definitions so we can work with them.


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Sharepoint")

Set the site URL
Because we will be using the site URL in many places in the script, it makes sense to define a a variable and set it once:

$siteUrl = "http://mysharepointsite.com"

Initiate the Site object
This populates a SPSite object variable with the object details for the Sharepoint site

$mySite=new-object Microsoft.Sharepoint.SPSite($siteUrl)

Initiate the Web object
The Web object will be used often in the Sharepoint PowerShell scripts.

$web = $mySite.openweb()

Specify the Named List
Specify list name as it shows in the URL. %20 will be needed for any spaces

$ListToDump = "Projects" 

For example, if you are in Sharepoint with the list open, the URL at the top of the screen will look something like the following:

http://mysharepointsite.com/Lists/Projects/AOpen%20Items%20Queue.aspx?View={916C686B-2607-473B-AC29-F825E9B6D185}&FilterField1=Customer&FilterValue1=xxxxxxxx

The highlighed section in yellow is the site base URL, the /Lists/ part indicates we are working with lists, and the Projects name highlighted indicates the list name in Sharepoint - this may not be the exact name as what you see, especially if there is punctuation in the visible list name, so always check the URL when you have the list open. The rest of the URL indicates the view and object name.

In the next example, you see that any spaces in a list name are replaced with %20 in standard web URL fashion.

http://mysharepointsite.com/Lists/Project%20Assignments/My%20Active%20Assignments.aspx?View={8F14253B-0168-40B5-B20E-678617939656}&FilterField1=LinkTitle&FilterValue1=Revision%20Repair%20Required


Fetch the List Object
We use the $web.GetList function with the URL and list name to open the list object.


$list = $web.GetList($siteUrl + "/Lists/" + $ListToDump)


Define query to work with List Contents
This initializes the variable as a SPQuery type object. 

$qry = new-object Microsoft.Sharepoint.SPQuery



Fetch the items in the list into a Lists collection object
This will return a collection of all items in the list, that we can then loop through.

$items = $web.Lists[$list].GetItems($qry)

Loop through the List, one Item at a time
The example below shows how to loop through the $items list object (in no prescribed order), outputting selected field values for each item in the list, including the key value from a lookup field into another list. 

Tip: You can reference field values in Sharepoint objects by specifying the object variable name, and then the field name in quotes, surrounded by square brackets, i.e.

$Listitem["Title"]

As we will see later, it will be necessary to be able to extract the lookup key values in order to work with other lists in the same script.


# Process the list
foreach ($Listitem in $items)
{
  # Only printing specific fields instead of full object dump
  $Listitem["Title"]
  $Listitem["ID"]


  # This is how to extract the ID field from a 
  # lookup reference to another list
  $XX = $Listitem["Status"] 
  # This returns a value of the format nn;Text
  $XX


  $Status_ID = $XX.SubString(0,$XX.IndexOf(";",0)) 
  # This is the FK ID (nn) into the other list
  $Status_ID


  # You can now use this numeric key field value for operations 
  # when working with multiple lists at one time.
  "--------------------------------------------------------"
  } # foreach


Note that this example is fairly simple, in that it does not do any conditional tests, it simply dumps out information from the named list. However this can be a very useful script when you want to quickly dump the contents of a list to see record keys, etc.

Note that from a coding practice, we are not using the obvious variable choice of "$item" to step through the list. That is because, as you see later on, the PowerActivity PowerShell script initializes several variables that we will use, including $web and $item. In order to write scripts that can first be tested on the server before being utilized in the workflow (which is hard to debug), we use some standard coding practices to make the code relatively transportable from the Server-side script into the workflow, with minimal changes.

Cleanup - Garbage Collection
Although less critical in server-side scripts, the objects should be disposed when each script completes. This becomes imperative in workflow-based PowerShell scripts, as the running process never exits and you can get memory leaks, which may crash the timer process, or make it unable to start workflows until you do an IIS Restart.


Each object used should be disposed, so for this example, our cleanup would look like:

##############################################################
# CLEANUP - GARBAGE COLLECTION
# All objects should be disposed to prevent memory leaks, especially for 
# workflow-based scripts
##############################################################
$siteUrl.dispose
$mySite.dispose
$web.dispose
$ListToDump.dispose
$list.dispose
$qry.dispose
$items.dispose
$Listitem.dispose

Conditional Mass-update of a Single List [server script]
This script uses the same basic structure and object variable definitions as the Dump List script. However, within the loop we will test for conditions, and if those conditions are met for the List Item, we will update it. We will use the condition test and a $doupdate flag to indicate that we should update the record, based on one or more of the tests.

In this example, we are populating "Prior" values from "current" field values if they are empty. This simulates what happens if you decide to track a field value and use it in workflow state-based decisions (i.e. is the value different from last time?). This script would then set the "initial state" so that events would not trigger unnecessarily the next time the List Item was edited.


[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Sharepoint")


$siteUrl = "http://mysharepointsite.com"
$mySite=new-object Microsoft.Sharepoint.SPSite($siteUrl)
$web = $mySite.openweb()




$ListToUpdate = "Projects"
$list = $web.GetList($siteUrl + "/Lists/" + $ListToUpdate)


"Started at: " 
date
# Define query object and get list of items to loop through
$qry = new-object Microsoft.Sharepoint.SPQuery
$items = $web.Lists[$list].GetItems($qry)


# Process the list
foreach ($item in $items)
{
  # Initialize flag for no update
  $doupdate = 0


  # Print field values for debugging
  $item["Title"]
  $item["Estimate-Administration"]
  $item["Estimate-Development"]
  $item["Estimate-Testing"]


# Test if there is a "Prior" field value, and if there is a 
# "current" field value but no "prior" value, then set the 
# Prior value to the current value. Repeat for several fields.


 if ( (($item["Estimate-Administration"] -ne $null) -and ($item["Prior Estimate-Administration"] -eq $null)))
   {
    # Set PRIOR to CURRENT value
    $item["Prior Estimate-Administration"] = $item["Estimate-Administration"]
    "Updating Prior Estimate-Administration"


    # Set flag to perform update
    $doupdate = 1
    }


 if ( (($item["Estimate-Development"] -ne $null) -and ($item["Prior Estimate-Development"] -eq $null)))
   {
    $item["Prior Estimate-Development"] = $item["Estimate-Development"]
    "Updating Prior Estimate-Development"


    $doupdate = 1
    }


 if ( (($item["Estimate-Testing"] -ne $null) -and ($item["Prior Estimate-Testing"] -eq $null)))
   {
    $item["Prior Estimate-Testing"] = $item["Estimate-Testing"]
    "Updating Prior Estimate-Testing"


    $doupdate = 1
    }
 if ( $doupdate -eq 1 )
    {
### DEBUG
# During the initial stages of development the following
# is commented out so that we can make sure we are identifying
# records to update properly.
# UN-comment when you are ready to do the actual update.
# $item.update()


"NEXT>>>>>>>>>>>>>>>>"
   }
  } # foreach
"Ended at: " 
date



Traverse folder structure to test and update values [server script]
This script uses a recursive technique to work through a nested structure such as a document library, or a list with sub-folders. This particular example copies specific field values from the parent (described in the script as "metadata") so that column filtering of those common values (i.e. customer ref#, client ref#, etc) will work as you drill down into the sub-folders.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Sharepoint")


############################################################
function TraverseListFolder ( $folder )
{
$web = $folder.ParentWeb
$qry = new-object Microsoft.Sharepoint.SPQuery
$qry.Folder = $folder
$items = $web.Lists[$folder.ParentListId].GetItems($qry)
# Process the list
foreach ($item in $items)
{
   if ($item["Project"] -eq $null)
   {
   # Get parent folder
if ($item.Folder -ne $null)
{
$itemP = $web.GetListItem($item.Folder.ParentFolder.ServerRelativeUrl)
}
if ($item.File -ne $null)
{
$myFile = $item.Web.GetFile($item.Url)
$itemP = $web.GetListItem($myFile.ParentFolder.ServerRelativeUrl)
}
 if ($itemP["Project"] -ne $null)
 { 
# Update metadata from parent
"Updating metadata for folder " +  $item["Name"] + " from parent folder " + $item.Folder

$item["Project"] = $itemP["Project"]
$item["Customer"] = $itemP["Customer"]
$item["Functional Area"] = $itemP["Functional Area"]
$item["Cust Ref#"] = $itemP["Cust Ref#"]
$item["Client Ref#"] = $itemP["Client Ref#"]
$item["For Product"] = $itemP["For Product"]

$item.Update()
 }
    }
if ($item.Folder -ne $null )
# Recursive call
TraverseListFolder ( $item.Folder )
}

  } # foreach

#Cleanup
$web.dispose

} # Function

############################################################
# MAIN
$siteUrl = "http://mysharepointsite.com"
$mySite=new-object Microsoft.Sharepoint.SPSite($siteUrl)

$web = $mySite.openweb()
$docShare = "Deliverables%20Repository"
$list = $web.GetList($siteUrl + "/" + $docShare)

"Started at: " 
date

TraverseListFolder ( $list.RootFolder )

"Started at: " 
date

#############################################################
# CLEANUP - GARBAGE COLLECTION
#############################################################
$siteUrl.dispose
$mySite.dispose
$web.dispose
$docShare.dispose
$list.dispose


Create folder structure in Document Library [workflow script]
This workflow creates a standard directory structure for a project. The trigger condition for this workflow is based on specific status code(s) being met in the Project, and that the folders have not already been created. This was coded in a separate workflow for maintenance simplicity, and so that it could be called manually if needed. This section shows code examples of working with PowerShell to create folders, which cannot be done from a normal workflow action. 

However, please note that the full solution is not described here, i.e. custom object type definitions etc. 

We will create a new workflow called "Create Root Folder for Project". We will use the custom "Execute PowerShell Script" action:
First we need to write the script. In this (older) example, we show that you can use the String Builder lookups for data elements; we could also have written it in more portable code as above, but this provides an example of a different method. Note that this method cannot be copied-and-pasted from Sharepoint Designer (thus the screen shots), so a more portable coding method is suggested. An example of this approach follows in the next section.

Note that the $web and $item variables already exist, as they have been pre-defined by the PowerActivity action. So we could have written the update code in a more portable style like this:

$folder["BaseName"] = $item["Title"]
$folder["Project"] = $item["ID"]
etc.

We will use fields from the current list item (project record) to populate fields in the Document Library. Note that we have already pre-defined the custom fields that we want in the Document Library, under a custom "Deliverable Folder" object type. 
The main folder is created, followed by the standard sub-folder structure in the following screen shots:
We are defining three sub-folders here, Specifications, Testing  
 and Development.
 Finally, we will hook the new workflow into the Project Update workflow, where we will use the custom "Start a Workflow" action call the "Create root folder for object" workflow when the Project enters the "In Development" status:
Note: The full solution for the document library folder/metadata inheritance also incorporates  PowerEventReceivers.  Only selected elements of the solution have been detailed here. 


Update multiple items in other list(s) [server script/workflow script]
These example scripts test for a specific status condition (i.e. project cancelled) in the main Project record(s), and then traverses several related lists (Project Assignments, Held Time), updating the status of dependent records in those lists to "cleanup/close-out" those records.

The server script and the workflow script are almost identical in logic, except that the server script loops through the Projects list, scanning all records, and the Workflow script starts with the specific Project record that has just been updated ($item). In order to develop the same logic (essentially copy-and-paste) for two versions, we will use the $item variable to represent the List Item, and $ProjectMainID to hold the ID field for this List Item.

Server-side script version "wrapper":
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Sharepoint")
$siteUrl = "http://mysharepointsite.com"
$mySite=new-object Microsoft.Sharepoint.SPSite($siteUrl)
$web = $mySite.openweb()
$Projects = "Projects"
$list = $web.GetList($siteUrl + "/Lists/" + $Projects)
$qry = new-object Microsoft.Sharepoint.SPQuery
$items = $web.Lists[$list].GetItems($qry)


# Project Assignments that are open will be closed
$ProjectAssignments = "Project%20Assignments"
$list2 = $web.GetList($siteUrl + "/Lists/" + $ProjectAssignments)
$qry2 = new-object Microsoft.Sharepoint.SPQuery
$list2Items = $web.Lists[$list2].GetItems($qry2)

#Held time that was unprocessed will be cancelled/denied
$HeldTime = "Held%20Time"
$list3 = $web.GetList($siteUrl + "/Lists/" + $HeldTime)
$qry3 = new-object Microsoft.Sharepoint.SPQuery
$list3Items = $web.Lists[$list3].GetItems($qry3)

# Process the list
foreach ($item in $items)
{  $ProjectMainID = $item["ID"]
... common logic
} # foreach

Workflow script header

$siteUrl = "http://mysharepointsite.com"
$ProjectMainID = $item["ID"]


# Project Assignments that are open will be closed
$ProjectAssignments = "Project%20Assignments"
$list2 = $web.GetList($siteUrl + "/Lists/" + $ProjectAssignments)
$qry2 = new-object Microsoft.Sharepoint.SPQuery
$list2Items = $web.Lists[$list2].GetItems($qry2)


#Held time that was unprocessed will be cancelled/denied
$HeldTime = "Held%20Time"
$list3 = $web.GetList($siteUrl + "/Lists/" + $HeldTime)
$qry3 = new-object Microsoft.Sharepoint.SPQuery
$list3Items = $web.Lists[$list3].GetItems($qry3)
... common logic


Common Logic Block

## CANCEL ASSIGNMENTS FIRST
# Process the list
foreach ($listItem in $listItems)
{
$doupdate = 0
$XX = $listItem["Project"]

$Project_ID = $XX.SubString(0,$XX.IndexOf(";",0))
#$Project = $ProjectList.GetItemByID($Project_ID)


 if ( ($listItem["Status"] -ne "Complete") -and ($listItem["Status"] -ne "Cancelled") -and ($listItem["Status"] -ne "QA Test Requested [Stage 3,5]") -and ($Project_ID -eq $ProjectMainID) )
{
    $listItem["Status"] = "Cancelled"
    $doupdate = 1
}
 if ( $doupdate -eq 1 )
    {
     $listItem.update()
    }
} # For Each


##############################################################
# Held Time
##############################################################
## CANCEL HELD TIME
# Process the list
foreach ($listItem in $listItems)
{
$doupdate = 0


$XX = $listItem["Project"]
$Project_ID = $XX.SubString(0,$XX.IndexOf(";",0))
#$Project = $ProjectList.GetItemByID($Project_ID)


 if ( ($Project_ID -eq $ProjectMainID) )
{
  if (( $listItem["Status"] -eq "Pending Overage Approval") )
   {
       $listItem["Status"] = "Overage Denied"
       $listItem["Processed"] = $true
       $listItem["Rejected Hours"] = $listItem["Hours"]
       $listItem["Workflow Message"] = "Denied due to Project status change to Abandoned"
       $listItem["Prior Status"] = "Overage Denied"


       $doupdate = 1
   }


  if (( $listItem["Status"] -eq "Pending Project Approval") )
   {
       $listItem["Status"] = "Project Cancelled"
       $listItem["Processed"] = $true
       $listItem["Rejected Hours"] = $listItem["Hours"]
       $listItem["Workflow Message"] = "Denied due to Project status change to Abandoned"
       $listItem["Prior Status"] = "Project Cancelled"
       
       $doupdate = 1
   }
}
 if ( $doupdate -eq 1 )
    {
     $listItem.update()
    }
} # For Each

(Update) Important Note on Working With Fields
In Powershell, although you can reference any field in the list in your script, you can only compare  retrieve values from "static" fields - that is, you cannot use calculation fields. PowerShell will not complain - but you will not get results in your script. This is because the .Net library for Sharepoint will not do the field calculation for you - that only happens inside the Sharepoint UI itself.


If you need to have access to a "calculated" field, you actually need to have two fields - the calculated field (usually hidden) and a "stored result" field, which must be updated from the calculated value in the last step of the "Update" workflow. Then you can use the "stored value" field in PowerShell - and also, incidentally, in View calculations in Sharepoint.


That's it for this session. I hope you found it helpful.


File Downloads
Here are the file downloads from this session. Note that the downloads are suffixed ".txt" to prevent any potential browser download issues with executable scripts. To run these on your server, they should be renamed to ".PS1" suffix first.






Coming next:
Part 7: Hosting tips, Email & Notifications
Part 8: Doing it Yourself - Recommendations
Part 9: Using what you've built - more free enhancements


Go back to:
Part 1: The Need
Part 2: Designing the Solution
Part 3: Building the Structure - Sharepoint Lists
Part 4: Process Workflow Design
Part 5: Implementing Custom Workflows with Sharepoint Designer


For more information or questions, contact:


Gary Nelson, PMP
www.gazzaconsulting.com