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>

Write Once Fields in SharePoint: A Simple Workaround.

Introduction

Recently in a forum, someone had a requirement for making fields on a list read only once an item was added. Out of the box, this isn't possible.

There is a way to achieve a similar result though, using PowerShell to edit the field (or by using CAML properties when declaring the field). This solution doesn't actually set the field for a list item as read only; it just removes the field from the UI (User Interface) so it can't be edited once it's set.

The Desired Result

1. A user can add a new list item, and view all the field.

2. A user can view the list, and see all the fields.

3. The user can edit the list item, but they can only edit a limited number of fields.

How the Solution Works

The solution works by setting boolean values that determine if the field is rendered in the add, view and edit forms. By setting the ShowInNewForm or ShowInEditForm property to false, the user has no way of editing the field (other than to use the DataGrid View, which can optionally be disabled).

The PowerShell Based Solution

The following PowerShell performs the following actions:

• Removes the "Title", "Item", "Found By" and "DateItemFound" fields from the Edit Form, so that they can't be changed once a list item has been created. 
• Removes the "AdministrativeNotes" field from the New Form.
• Disables the DataGrid view

$web = Get-SPWeb "http://devmy131"            
$list = $web.Lists["Lost Property"]            
$field = $list.Fields["Title"]            
$field.ShowInEditForm = $false;            
$field.Update();            
$field = $list.Fields["Item"]            
$field.ShowInEditForm = $false;            
$field.Update();            
$field = $list.Fields["FoundBy"]            
$field.ShowInEditForm = $false;            
$field.Update();            
$field = $list.Fields["DateItemFound"]            
$field.ShowInEditForm = $false;            
$field.Update();            
$field = $list.Fields["AdministrativeNotes"]            
$field.ShowInNewForm = $false;            
$field.Update();            
$list.DisableGridEditing = $true;            
$list.Update();            
$web.Dispose();

The CAML Based Solution

The following CAML can be used in a List Definition (in the Schema.xml file) to set the same properties on the fields. 

<Field ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Type="Text" Name="Title" DisplayName="Title" Required="TRUE" StaticName="Title" ShowInEditForm="FALSE" />

<Field Type="Text" DisplayName="Item" Required="TRUE" ID="{940a19c1-1dd6-4035-9107-32d159d77623}" StaticName="Item" Name="Item" ShowInEditForm="FALSE"/>

<Field Type="User" DisplayName="FoundBy" List="UserInfo" Required="TRUE" ShowField="ImnName" UserSelectionMode="PeopleOnly" UserSelectionScope="0" ID="{56142494-fe1c-48d1-89ac-5ae0901db5e5}" StaticName="FoundBy" Name="FoundBy" ShowInEditForm="FALSE"/>

<Field Type="DateTime" DisplayName="DateItemFound" Required="TRUE" Format="DateTime" FriendlyDisplayFormat="Relative" ID="{7f1ec3a8-ba55-42dd-90ca-47daae6b6cfa}" StaticName="DateItemFound" Name="DateItemFound" ShowInEditForm="FALSE"><Default>[today]</Default></Field>

<Field Type="Note" DisplayName="AdministrativeNotes" Required="FALSE" NumLines="6" RichText="TRUE" RichTextMode="FullHtml" ID="{5c5446a7-85d7-4d34-b883-f41b46968751}" StaticName="Notes" Name="Notes" RestrictedMode="TRUE" AppendOnly="TRUE" ShowInNewForm="FALSE"/>