InfoPath and SharePoint Web Services: GetListItems

I’ve been working on an InfoPath form (which has been driving me crazy to say the least).  I needed a collection of list items for the current user so I started by creating a data connection to my list, displaying it in a repeating table, and hiding what I didn’t want to see.  (I know, sloppy).  It worked, but my client uses IE8 and it would throw JavaScript errors if it had too pull to many list items.  (IE10 and Chrome worked fine).  The form would eventually load, but that was no good.

I then tried using web services, specified lists.asmx, and attempted to use the GetListItems operation.  No matter what I did, I got the following error message:

The SOAP response indicates that an error occurred on the server:
Microsoft.SharePoint.SoapServer.SoapServerException: Exception of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.
Element of parameter query is missing or invalid.0x82000000

I dug around and it appears to be a common issue but I haven’t found an alternative suggestion so I gave REST a try.  
Start by selecting “From REST Web Service” 
Next, enter the URL followed by “/_vti_bin/listdata.svc/” (you should be able to add your filters here but in my case, I need it to be a little flexible so we’ll do that in a bit).

Now, you can access all of the data but like I said earlier, I needed to filter the data so here’s how I did it by using Rules.
Create a new action based rule.  The action that we’ll need is “Change REST URL”
We’ll need a function to change our URL.  I started by using concat.  There’s an existing SharePointSiteURL() function that will return the URL of site where the form resides.  That function gets passed into the concat first, and fill in the rest of the concat function as needed.  In the image below, my field names aren’t showing up, but they’re there.
The last thing we’ll need it to query the data again.  Just create another rule. Select “Query for Data” in the list of actions, and choose your REST Web Service.

MOSS Site Info with PowerShell

I started working with a team on a large migration project a few months ago.  The client requested that we approach the migration site by site instead of upgrading the whole environment all at once.  The first phase of the project involved migrating STS to SharePoint 2010 (yes folks, I said STS… as in V1).  Phase two involves upgrading their MOSS environment to 2010 and that’s the focus of this post.  We’re trying to approach phase two in a similar fashion while keeping an eye on streamlining our processes. 

Overview of our Approach
Since we’re approaching this project site by site, communication is VERY important.  I won’t bore you with the details so here’s the gist of the process.

1. We kick off a workflow which tosses our item into a Review state where we gather information about our sites (how big is the site, are there workflows or InfoPath forms, are there any other customization, etc).  **This is where PowerShell came in**
2. We contact the owner to schedule some time for the upgrade since their site will experience some downtime.
3. We perform the upgrade (with DocAve)
4. We verify the upgrade
5. We wait for site owner sign off

Enough of the Back Story Already!
The script is long and includes an options menu, error handling and logging so I’ll just show the important parts.

We needed a list of all sites, the site owners, site size, and other information.  In order to use the object model, we need to load the SharePoint assembly.

[void][System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint”)

Get an instance of the needed SPWeb:
$site = New-Object Microsoft.SharePoint.SPSite($rootURL)
$web = $site.OpenWeb();

Get a list of people in the owners group:
    $owners = $null
    foreach ($u in $web.AssociatedOwnerGroup.Users)
    {
        $owners += $u.LoginName + ‘;’
    }
   
Next, I needed storage information for each site.   For more info on the following method, visit StorageManagementInformation

Get list storage information:
$listDataTable = New-Object System.Data.DataTable
$listDataTable = $s.StorageManagementInformation(1, “Increasing”, “Size”, “100000”)

Get document storage information:
$docLibDataTable = New-Object System.Data.DataTable
$docLibDataTable = $s.StorageManagementInformation(2, “Increasing”, “Size”, “100000”)

Get a total of list and library storage usage:
The previous lines created a data table that contained storage usage for each list and library.  Now we loop through each line and add up the numbers.  Since the data table contains usage for all lists and libraries in the site collection, we’ll look for just the ones that are relevant to the current web by using the web’s ID.

$siteSize = 0

foreach($row in $docLibDataTable.Rows)
{
     if (([Guid]$row.WebGuid) -eq ([GUID]$web.ID))
     {
           $siteSize += $row.Size
     }
}
       
foreach($row in $listDataTable.Rows)
{
     if (([Guid]$row.WebGuid) -eq ([GUID]$web.ID))
    {
           $siteSize += $row.Size
    }
}

Find running workflows and if an InfoPath form exists:
$availableWorkflows = $null
$containsInfoPathForms = $false
   
    foreach ($list in $web.Lists)
    {
        #if one infopath form is found on the site, there’s no need to search again
        if ($containsInfoPathForms -eq $false)
        {          
            $containsInfoPathForms = ($list.BaseType -eq “DocumentLibrary” -and $list.BaseTemplate -eq “XMLForm”)
        }
       
        foreach ($association in $list.WorkflowAssociations)
        {
            $availableWorkflows += $association.Name + ‘;’
        }
    }

 
Output the results to a CSV:
First, I needed a global variable to store the results since method that gathers all of the data is called recursively.

$global:outputToCsv = @()

Next, I create a new object to store the data for the current web and append those results to my global variable.

$output = New-Object PSObject
$output | Add-Member -MemberType NoteProperty -Name “Title” -Value $web.URL
$output | Add-Member -MemberType NoteProperty -Name Size -Value ($siteSize/1MB)
$output | Add-Member -MemberType NoteProperty -Name “Sub Sites” -Value $web.Webs.Count
$output | Add-Member -MemberType NoteProperty -Name “Infopath Forms” -Value $containsInfoPathForms

$output | Add-Member -MemberType NoteProperty -Name Owners -Value $owners
$output | Add-Member -MemberType NoteProperty -Name “Site Template” -Value $web.WebTemplate

$global:outputToCsv += $output
 
Finally, when all the data has been collected, I export it to a csv:

$global:outputToCsv | Export-Csv (“c:\scripts\OneSiteCollection.csv”) -NoTypeInformation