Thursday, 11 September 2014

Filtering a SharePoint List View by Document Approval status

Just a quick one on creating SharePoint list views that filter results based on a workflow status column.

Scenario: SharePoint 2013, Nintex Workflow 2013, Document Library with a workflow attached (that runs on the documents).

The workflow status is recorded in the Document Approval column (static name, Document). 

The values of this column can be retrieved using PowerShell. In this example, I'm using CSOM to access the field values, by getting the field, and looking at the SchemaXml property:

$SourceWebUrl = "http://some.site.com/sites/fud"            
$SourceListName = "Project Documents";            
$account = Read-Host -Prompt "Enter the account to use to query pages";            
$password =  Read-Host -Prompt "Enter the password to use to query pages" -AsSecureString
$credentials = New-Object System.Management.Automation.PsCredential($Account,$Password);

Add-Type -Path "C:\Temp\Microsoft.SharePoint.Client.dll";            
Add-Type -Path "C:\Temp\Microsoft.SharePoint.Client.Runtime.dll";            
            
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SourceWebUrl)            
$ctx.Credentials = $credentials            
$w = $ctx.Web            
$ctx.Load($w)            
$l = $w.Lists.GetByTitle($SourceListName)            
$ctx.Load($l)            
$fields = $l.Fields            
$ctx.Load($fields)            
$ctx.ExecuteQuery()            
            
#Get the document approval field and check the SchemaXxml property            
$da = $fields.GetByInternalNameOrTitle("Document Approval")            
$ctx.Load($da)            
$ctx.ExecuteQuery()            
$da.SchemaXml

The values are:

<Field DisplayName="Document Approval" Type="WorkflowStatus" Required="FALSE" ID="{e7cfcdf7-6990-4a20-835c-83d64fbaf87a}" SourceID="{a777c58e-b89b-4f82-8c08-36721dd8ceeb}" StaticName="Document" Name="Document" ColName="nvarchar16" RowOrdinal="0" Version="154" WorkflowStatusURL="_layouts/15/WrkStat.aspx" ReadOnly="TRUE">
    <CHOICES>
        <CHOICE>Starting</CHOICE>
        <CHOICE>Failed on Start</CHOICE>
        <CHOICE>In Progress</CHOICE>
        <CHOICE>Error Occurred</CHOICE>
        <CHOICE>Canceled</CHOICE>
        <CHOICE>Completed</CHOICE>
        <CHOICE>Failed on Start (retrying)</CHOICE>
        <CHOICE>Error Occurred (retrying)</CHOICE>     
    </CHOICES>
</Field>

To use these values in a List View, open your list view (or create a new list view) in SharePoint Designer. Then create a CAML query that filters on this field, using the (zero based) index of the field values to specify the field value to filter on. The field type needs to be Integer. 

For example, the following CAML query filters all documents that the current user has authored, that have a workflow status of Starting or In Progress;


<Query>
    <Where>
        <And>
            <Eq>
                <FieldRef Name="Author"/>
                <Value Type="Integer">
                    <UserID Type="Integer"/>
                </Value>
            </Eq>
            <Or>
                <Eq>
                    <FieldRef Name="Document"/>
                    <Value Type="Integer">0</Value>
                </Eq>
                <Eq>
                    <FieldRef Name="Document"/>
                    <Value Type="Integer">2</Value>
                </Eq>
            </Or>
        </And>
    </Where>
</Query>