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

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]

Script Name: MassUpdateSelectedField.ps1

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]

Script Name: GetMetadataFromFolderListParent.ps1

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]

Server script: CascadeAbandonedProjects.ps1

Workflow Script: WF_CascadeProjectCancellation.ps1

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.

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

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