CAML Query and the SharePoint List Views Filtering

I have a list which resembles a Tasks List with an additional column ‘TaskCreated’ – a DateTime column.

I wanted to query the list with the following filters :

1. Get all the items with status “Not Started”
2. Get all the items with status “In Progress”
3. Get all the items with status “Completed” AND the ‘Task Created’ is within the given number of days.
4. Get all the items with status ‘Deferred” AND the ‘TaskCreated’ is within the given number of days.

I used the following CAML query to fetch the results :

oQuery.Query = String.Format(
“<Where>” +
“<Or>” +
“<Or>” +
“<Eq>” +
“<FieldRef Name=’Status’ />” +
“<Value Type=’Choice’>Not Started</Value>” +
“</Eq>” +
“<Eq>” +
“<FieldRef Name=’Status’ />” +
“<Value Type=’Choice’>In Progress</Value>” +
“</Eq>” +
“</Or>” +
“<Or>” +
“<And>” +
“<Eq>” +
“<FieldRef Name=’Status’ />” +
“<Value Type=’Choice’>Completed</Value>” +
“</Eq>” +
“<Geq>” +
“<FieldRef Name=’TaskCreated’/>” +
“<Value Type=’DateTime’ StorageTZ=’TRUE’>{0}</Value>” +
“</Geq>” +
“</And>” +
“<And>” +
“<Eq>” +
“<FieldRef Name=’Status’ />” +
“<Value Type=’Choice’>Deferred</Value>” +
“</Eq>” +
“<Geq>” +
“<FieldRef Name=’TaskCreated’/>” +
“<Value Type=’DateTime’ StorageTZ=’TRUE’>{1}</Value>” +
“</Geq>” +
“</And>” +
“</Or>” +
“</Or>” +
“</Where>”,
SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.UtcNow.AddDays(Completed)),
SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.UtcNow.AddDays(Failed)));

I was curious to know how SharePoint would have done if I had created a view out of it.

This is how it looks like . Since there are nested operators it is very difficult to relate to the the filters displayed ….If you have a complex view then you are better of by creating a custom view using code and the CAML rather than relying on the view creation UI of SharePoint.

The view as seen by Stramit CAML Viewer :

<Query>
<Where>
<Or>
<Or>
<Eq>
<FieldRef Name=”Status” />
<Value Type=”Choice”>Not Started</Value>
</Eq>
<Eq>
<FieldRef Name=”Status” />
<Value Type=”Choice”>In Progress</Value>
</Eq>
</Or>
<Or>
<And>
<Eq>
<FieldRef Name=”Status” />
<Value Type=”Choice”>Completed</Value>
</Eq>
<Geq>
<FieldRef Name=”TaskCreated” />
<Value Type=”DateTime” StorageTZ=”TRUE”>2009-11-16T22:05:59Z</Value>
</Geq>
</And>
<And>
<Eq>
<FieldRef Name=”Status” />
<Value Type=”Choice”>Deferred</Value>
</Eq>
<Geq>
<FieldRef Name=”TaskCreated” />
<Value Type=”DateTime” StorageTZ=”TRUE”>2009-11-16T22:05:59Z</Value>
</Geq>
</And>
</Or>
</Or>
</Where>
</Query>

SharePoint Content Approval – Never ending workflow

The Content Approval feature permits you to create a list or a document library where items or files that are submitted by users are not visible to other site users until the items or the files are approved by a user (such as the site administrator) who belongs to a site group that has the Manage Lists right.

When you add a new item or when you modify an existing item in a list or a document library after you enable content approval for that list or document library, the item is marked as Pending. Pending items are only visible to the user who created or modified the item and to users with the Manage Lists right. Pending items are not visible to users until the item is approved. When an item is approved, it is marked as Approved and is visible to all users. When an item is rejected, it is marked as Rejected, and is returned to the user who created it.

 

Use a workflow to manage content approval for a library

You can use a workflow to manage content approval (also called moderation) in libraries for which content approval is required.

Enabling OOTB Approval workflow for a list:

 

The Problem : 

In a SharePoint list after you are done with enabling content approval and creating an OOTB approval workflow, you might experience a strange behavior during the approval process.

The workflow can run endlessly. Every time the workflow task is approved, it triggers a Item Updated event which in turn starts another instance of the workflow…So the item remains pending even after the approval ! (After the workflow instance completes another instance is started because of the item updated event triggered by the workflow approval.

This happens in the list. The reason for the difference in behavior is the way the workflow setup is done for the list and document library. We will come to the settings in the library shortly.

The workaround Solution:

The solution is to handle the ItemUpdated event and update the Moderation status programmatically after turning off the event. The code is listed below.

 

class UpdateWorkflowStatus : SPItemEventReceiver

   {


       public override void ItemUpdating(SPItemEventProperties properties)

       {

           EventLog m_EventLog = new EventLog("");

           m_EventLog.Source = "Approval Workflow Status Update";


           try

           {

               base.ItemUpdating(properties);

               if (properties.AfterProperties["_ModerationStatus"] != null && properties.ListItem["_ModerationStatus"] != null)

               {

                   if(properties.AfterProperties["_ModerationStatus"].ToString() != properties.ListItem["_ModerationStatus"].ToString())

                   {

                       this.DisableEventFiring();


                       properties.Status = SPEventReceiverStatus.CancelNoError;

                       properties.ListItem["_ModerationStatus"] = properties.AfterProperties["_ModerationStatus"].ToString();

                       properties.ListItem.Update();


                       this.EnableEventFiring();

                   }

               }

           }

           catch (Exception ex)

           {

               m_EventLog.WriteEntry("Workflow Status Update Event Handler Error : " + ex.Message,EventLogEntryType.Error);

           }



       }

The reason why it won’t happen in a document library is that the way the settings are done. In a document library for the content approval minor and major version has to be enabled. And any edit that happens becomes a minor version and a workflow gets triggered only when the major version is published.

BUT ! there is a way to reproduce it in document library as well. You should create the workflow first and then enable content approval, you will end up in the same problem that I explained for the list !!!