Second Life of a Hungarian SharePoint Geek

November 14, 2011

Creating custom validation rules in our list field iterators

Filed under: CAML, Custom forms, SP 2010, Validation — Tags: , , , — Peter Holpar @ 13:36

Last week we saw how to hide specific field controls or how to display them as read-only even on the edit form. In the current post I introduce a simple technique one can apply to create custom validation for SharePoint forms without InfoPath.

First, what are the main problems with the out of the box SharePoint form validation capability?

As written in the first post of this series about custom list field iterators, SharePoint 2010 made a large step to the right direction compared to its predecessor, WSS 3.0. One can define field level and form level validation rules using a syntax similar to the one used with calculated columns, based on values of other fields of the current item.

However, you can define only a single validation rule for your form having a single validation error message, and a single validation rule and error message for the fields. Furthermore, there is another serious limitation, namely one can validate only the basic field types (like text, date, numeric) and the same is true to the fields one can refer to in the validation rule.

It means, you are out of luck if you have to work with validation of lookup or user fields, managed metadata or even your custom field types.

In the validation rule you can refer to the fields of the current item, you cannot build validation rules based on field values of other, related items, custom user properties, not to mention accessing other systems, like CRM, 3rd party web services, etc.

Moreover, the validation error messages are static text, one can not built them up dynamically based on field values, for example.

I found that in real world applications customers need usually much more validation power.  All of these inflexibilities listed above lead me to create some kind of custom solution for validation. You can read another approach built on custom field types here, but in this post I illustrate the list field iterator based solution.

As you might know the BaseFieldControl class, the base class of all field controls implements the IValidator interface. In practice it means, you can use these field controls similar to standard ASP.NET validator controls.

But before working with them, we first have to get a reference to the field controls. So we extend our BaseListFieldIterator class – introduced in the previous part – with the GetFieldControlByName method. There might be multiple forms on the page, so just to be sure that the field control is within our list field iterator, we call the GetIteratorByFieldControl method and compare the ClientID values.

The SetValidationError method is to set the validation error for the specified field.

  1. protected BaseFieldControl GetFieldControlByName(String fieldNameToFind)
  2. {
  3.     foreach (Control control in _formContext.FieldControlCollection)
  4.     {
  5.         if (control is BaseFieldControl)
  6.         {
  7.             BaseFieldControl baseField = (BaseFieldControl)control;
  8.             String fieldName = baseField.FieldName;
  9.             if ((fieldName == fieldNameToFind) &&
  10.                 (GetIteratorByFieldControl(baseField).ClientID == ClientID))
  11.             {
  12.                 return baseField;
  13.             }
  14.         }
  15.     }
  16.     return null;
  17. }
  19. protected Microsoft.SharePoint.WebControls.ListFieldIterator GetIteratorByFieldControl(BaseFieldControl fieldControl)
  20. {
  21.     return (Microsoft.SharePoint.WebControls.ListFieldIterator)fieldControl.Parent.Parent.Parent.Parent.Parent;
  22. }
  24. protected void SetValidationError(String fieldName, String errorMessage)
  25. {
  26.     BaseFieldControl fieldControl = GetFieldControlByName(fieldName);
  27.     fieldControl.ErrorMessage = errorMessage;
  28.     fieldControl.IsValid = false;
  29. }

Next we have to add the following code to our custom ListFieldIterator class.

  1. protected override void OnLoad(EventArgs e)
  2. {
  3.     if (Page.IsPostBack)
  4.     {
  5.         Page.Validate();
  6.         this.Validate();
  7.     }
  8. }
  10. public void Validate()
  11. {
  12.     if (base.ControlMode != SPControlMode.Display)
  13.     {
  14.         // here comes the validation logic
  15.     }
  16. }

We will extend the Validate method to fulfill our fictitious business needs.

The validation messages are stored as constants:

  1. public static class ValidationMessages
  2. {
  3.     public static readonly String TitleNotUnique = "Task title should be unique";
  4.     public static readonly String UserBusy = "This user already has another task for this time";
  5.     public static readonly String NoDateForAssignement = "A user can be assigned only if the start and due dates are specified";
  6.     public static readonly String PredecessorsNotCompleted = "At least one of the predecessors is not yet completed";
  7.     public static readonly String PredecessorSelfReference = "A task cannot be its own predecessor";
  8.     public static readonly String DueDateEarlierThanStartDate = "Due date should not be earlier than start date ";
  9.     public static readonly String CompletedTaskPercentage = "For a completed task the % complete should be 100";
  10. }

Despite of the constant values used in the sample, it is easy to create dynamic messages as well. For example, you can define a message like "Task title ‘{0}’ is not unique", then substitute the current value when setting the validation error.

The first rule is to allow only unique task titles.

  1. TextField titleField = GetFieldControlByName(SampleTaskListFields.Title) as TextField;
  3. if (titleField != null)
  4. {
  5.     String title = titleField.Value as String;
  7.     if (!String.IsNullOrEmpty(title))
  8.     {
  9.         if (ItemWithSameTitleExists(title))
  10.         {
  11.             SetValidationError(SampleTaskListFields.Title, ValidationMessages.TitleNotUnique);
  12.         }
  13.     }
  14. }

We check the uniqueness through a CAML query, and use the GetFieldRefs helper method to build the value of the ViewFields property of the SPQuery instance.

  1. protected bool ItemWithSameTitleExists(string title)
  2. {
  4.     SPQuery query = new SPQuery();
  6.     query.ViewFields = GetFieldRefs(SampleTaskListFields.Title);
  7.     // we should not check the ID in this case, as altering the title is not allowed
  8.     // title will be validated only on new item creation
  9.     // if title would be editable for existing items, then we should check
  10.     // whether the ID is not the one of the current (edited) item
  11.     // note, that CAML Eq for a Text field type is case insensitive
  12.     query.Query = String.Format("<Where><Eq><FieldRef Name='{0}'/><Value Type='Text'>{1}</Value></Eq></Where>",
  13.         SampleTaskListFields.Title, title);
  15.     bool result = List.GetItems(query).Count > 0;
  17.     return result;
  18. }
  20. private String GetFieldRefs(params String[] fieldNames)
  21. {
  22.     String fieldRefs = String.Concat(fieldNames.ToList().
  23.         ConvertAll(fieldName => String.Format("<FieldRef Name='{0}'/>", fieldName)));
  25.     return fieldRefs;
  26. }

The next image shows the validator in action.


The comparison is case insensitive.


Yes, you are right it is nothing more than a custom implementation for the built-in Enforce unique values feature, but this one does not require indexed columns, and you have the chance to handle issues like leading / trailing spaces. For example, if you enable Enforce unique values, it won’t prohibit users entering ‘ Sample task 1  ‘ without any difficulties. If you don’t like this, you can trim the title parameter value and/or use Contains instead of Eq in the CAML query.

After this basic validation, let’s see a more tricky one. This one can be solved using standard field validation, although in this case it is more complicated “thanks” to the read-only Start date field when the task is not in the “Not Started” status.

  1. DateTimeField startDateField = GetFieldControlByName(SampleTaskListFields.StartDate) as DateTimeField;
  2. DateTimeField dueDateField = GetFieldControlByName(SampleTaskListFields.DueDate) as DateTimeField;
  4. // if the task is not in 'Not Started' status, then we display the start date
  5. // as read-only and value would be null
  6. // in this case we should get the real value, stored in the item
  7. DateTime? startDateValue = (startDateField.ControlMode == SPControlMode.Display) ? (DateTime?)Item[SampleTaskListFields.StartDate] : startDateField.Value as DateTime?;
  8. DateTime? dueDateValue = dueDateField.Value as DateTime?;
  10. if ((startDateValue.HasValue) && (dueDateValue.HasValue))
  11. {
  12.     if (dueDateValue.Value < startDateValue.Value)
  13.     {
  14.         SetValidationError(SampleTaskListFields.DueDate, ValidationMessages.DueDateEarlierThanStartDate);
  15.     }
  16. }

The illustration of the validation error:


This validation is again a simple one. We require 100% for % Complete when Status is Completed.

  1. DropDownChoiceField statusField = GetFieldControlByName(SampleTaskListFields.Status) as DropDownChoiceField;
  2. NumberField percComplField = GetFieldControlByName(SampleTaskListFields.PercentComplete) as NumberField;
  4. bool checkForPredecessorStatus = false;
  6. if ((statusField != null) && (percComplField != null))
  7. {
  8.     String statusFieldValue = statusField.Value as String;
  9.     Double? percComplValue = percComplField.Value as Double?;
  11.     if (statusFieldValue == TaskStates.Completed)
  12.     {
  13.         checkForPredecessorStatus = true;
  14.         if ((!percComplValue.HasValue) || (percComplValue.Value != 1))
  15.         {
  16.             SetValidationError(SampleTaskListFields.PercentComplete, ValidationMessages.CompletedTaskPercentage);
  17.         }
  18.     }
  19. }

The screenshot of the validation:


Before you leave the post unread thinking this technique provides no additional value for validation, let’s see some more advanced example.

Using the standard Tasks list you can set an existing task as its own predecessor. That is not very nice, and in this case I found no simple OOB SharePoint tool to prohibit that.

Using this technique, you can achieve the result as shown below:

  1. MultipleLookupField predecessorsField = GetFieldControlByName(SampleTaskListFields.Predecessors) as MultipleLookupField;
  3. if (predecessorsField != null)
  4. {
  5.     SPFieldLookupValueCollection predecessorsValue = predecessorsField.Value as SPFieldLookupValueCollection;
  7.     if (predecessorsValue != null)
  8.     {
  9.         // a task can reference itself only in edit mode
  10.         if (ControlMode == SPControlMode.Edit)
  11.         {
  12.             if (!predecessorsValue.TrueForAll(predecessor => predecessor.LookupId != ItemId))
  13.             {
  14.                 SetValidationError(SampleTaskListFields.Predecessors, ValidationMessages.PredecessorSelfReference);
  15.             }
  16.         }
  17.         if (checkForPredecessorStatus)
  18.         {
  19.             if (!IsAllTasksCompleted(predecessorsValue.ConvertAll(predecessor => predecessor.LookupId)))
  20.             {
  21.                 SetValidationError(SampleTaskListFields.Status, ValidationMessages.PredecessorsNotCompleted);
  22.             }
  23.         }
  24.     }
  25. }

And in action:


The code above contains a check of predecessors when the task is to be set Completed. (The value of checkForPredecessorStatus computed earlier when validating % Complete.) We don’t want to allow that if there is at least on uncompleted predecessor. For the check we use the IsAllTasksCompleted method, passing the task ID of all of the predecessors as parameter.

  1. private bool IsAllTasksCompleted(List<int> taskIds)
  2. {
  3.     SPQuery query = new SPQuery();
  4.     query.ViewFields = GetFieldRefs(
  5.         SampleTaskListFields.Id,
  6.         SampleTaskListFields.Status);
  8.     query.Query = String.Format("<Where><Neq><FieldRef Name='{0}'/><Value Type='Text'>{1}</Value></Neq></Where>",
  9.                     SampleTaskListFields.Status, TaskStates.Completed);
  11.     bool result = true;
  13.     SPListItemCollection tasksNotCompleted = List.GetItems(query);
  15.     foreach (SPListItem task in tasksNotCompleted)
  16.     {
  17.         if (taskIds.Contains((int)task[SampleTaskListFields.Id]))
  18.         {
  19.             result = false;
  20.             break;
  21.         }
  22.     }
  24.     return result;
  25. }

The result of the validation is shown here:


Next, we want to allow to assign a user to the task if both start and due dates are set.

  1. UserField userField = GetFieldControlByName(SampleTaskListFields.AssignedTo) as UserField;
  3. if (userField != null)
  4. {
  5.     String userFieldValue = userField.Value as String;
  7.     if (!String.IsNullOrEmpty(userFieldValue))
  8.     {
  10.         if ((startDateValue.HasValue) && (dueDateValue.HasValue))
  11.         {
  12.             if (startDateValue.Value <= dueDateValue.Value)
  13.             {
  14.                 SPFieldUserValue userValue = new SPFieldUserValue(Web, userFieldValue);
  15.                 int? taskId = (base.ControlMode == SPControlMode.Edit) ? ItemId : (int?)null;
  16.                 if ((userValue.LookupId != -1) && (UserIsBusy(userValue.LookupId, taskId, startDateValue.Value, dueDateValue.Value)))
  17.                 {
  18.                     SetValidationError(SampleTaskListFields.AssignedTo, ValidationMessages.UserBusy);
  19.                 }
  20.             }
  21.         }
  22.         else
  23.         {
  24.             SetValidationError(SampleTaskListFields.AssignedTo, ValidationMessages.NoDateForAssignement);
  25.         }
  26.     }
  27. }

On the screenshot below, I “forgot” to set the start date, that is not allowed in this case.


Finally, using the method described in this post, we allow to assign the user for the task, if (s)he has no other assignment for that time interval. In this case we should ignore the task being edited when doing the validation.

  1. protected bool UserIsBusy(int userId, int? taskId, DateTime startDate, DateTime dueDate)
  2. {
  4.     SPQuery query = new SPQuery();
  5.     query.ViewFields = GetFieldRefs(
  6.         SampleTaskListFields.Id,
  7.         SampleTaskListFields.AssignedTo,
  8.         SampleTaskListFields.StartDate,
  9.         SampleTaskListFields.DueDate);
  11.     // NOTE: you can add filter for Status Neq 'Completed' as well if you wish
  12.     // I have not included that for the sake of simplicity
  13.     if (taskId.HasValue)
  14.     {
  15.         // it is editing an existing task, so we should exclude the task itself
  16.         query.Query = String.Format("<Where><And><And><Neq><FieldRef Name='{0}'/><Value Type='Integer'>{1}</Value></Neq><Eq><FieldRef Name='{2}' LookupId='TRUE' /><Value Type='Lookup'>{3}</Value></Eq></And>{4}</And></Where>",
  17.                         SampleTaskListFields.Id, taskId.Value,
  18.                         SampleTaskListFields.AssignedTo, userId,
  19.                         BuildDateRangeOverlapFilter(startDate, dueDate));
  20.     }
  21.     else
  22.     {
  23.         // it is a new task, we don't have to check te task ID
  24.         query.Query = String.Format("<Where><And><Eq><FieldRef Name='{0}' LookupId='TRUE' /><Value Type='Lookup'>{1}</Value></Eq>{2}</And></Where>",
  25.                         SampleTaskListFields.AssignedTo, userId,
  26.                         BuildDateRangeOverlapFilter(startDate, dueDate));
  27.     }
  29.     bool result = List.GetItems(query).Count > 0;
  31.     return result;
  32. }
  34. protected String BuildDateRangeOverlapFilter(DateTime startDate, DateTime endDate)
  35. {
  37.     StringBuilder sb = new StringBuilder();
  38.     sb.Append(String.Format("<And>{0}{1}</And>",
  39.         BuildSimpleDateFilter(SampleTaskListFields.StartDate, endDate, "Leq"),
  40.         BuildSimpleDateFilter(SampleTaskListFields.DueDate, startDate, "Geq")));
  42.     return sb.ToString();
  44. }
  46. protected String BuildSimpleDateFilter(String dateFieldName, DateTime filterDate, String relation)
  47. {
  48.     String datePattern = "yyyy-MM-ddT00:00:00Z";
  50.     return String.Format("<{0}><FieldRef Name='{1}'/><Value Type='DateTime'>{2}</Value></{0}>", relation, dateFieldName, filterDate.ToString(datePattern));
  52. }

And that is the outcome of the validation in this case:


Note: Other validation possibilities for Person or Group field type are to check the number of users set (you can allow a single user or multiple ones using the standard tools, but you can not expect exactly three of them), or to check if the specified users are from a specific Active Directory group.

Although the technique illustrated in this post require coding and not so straightforward as specifying a simple formula on the UI, I hope you can use it  effectively if the customer requirements demand something more sophisticated.

You can download the sample application from here.

November 11, 2011

How to find overlapping items for a date range

Filed under: CAML, SP 2010 — Tags: , — Peter Holpar @ 13:33

It is a common requirement to find list items (like tasks) that have an overlapping duration with a specified data range.

Several years ago I wrote a post about the DateRangesOverlap CAML element and about its strange behavior returning items outside of the specified month range. As I suggested there, we can (and should) create the right query using basic CAML elements.

It is easy to create a complex CAML query to filter items for overlapping date ranges, however, there is a way to create a simple query as well. The simplest way and the best description in the topic is the one I found on stackoverflow. It says, the date ranges are overlapping when it is true for both of them, that their start date is no later than the end date of the other date range. That is very clear if you think about that just a bit.

Based on that concept I’ve created two helper methods:

  1. protected String BuildDateRangeOverlapFilter(DateTime startDate, DateTime endDate)
  2. {
  3.     StringBuilder sb = new StringBuilder();
  4.     sb.Append(String.Format("<Where><And>{0}{1}</And></Where>",
  5.         BuildSimpleDateFilter("StartDate", endDate, "Leq"),
  6.         BuildSimpleDateFilter("DueDate", startDate, "Geq")));
  8.     return sb.ToString();
  9. }
  12. protected String BuildSimpleDateFilter(String dateFieldName, DateTime filterDate, String relation)
  13. {
  14.     String filter = String.Format("<{0}><FieldRef Name='{1}'/><Value Type='DateTime'>{2}</Value></{0}>",
  15.         relation, dateFieldName, SPUtility.CreateISO8601DateTimeFromSystemDateTime(filterDate));
  17.     return filter;
  18. }

And here is an example about the usage:

  1. DateTime startDate = DateTime.Today.AddDays(11);
  2. DateTime endDate = DateTime.Today.AddDays(15);
  4. SPList taskList = web.Lists["Tasks"];
  6. SPQuery query = new SPQuery();
  7. query.ViewFields = "<FieldRef Name='Title'/><FieldRef Name='StartDate'/><FieldRef Name='DueDate'/>";
  8. query.Query = BuildDateRangeOverlapFilter(startDate, endDate);
  10. SPListItemCollection matches = taskList.GetItems(query);
  12. foreach (SPListItem match in matches)
  13. {
  14.     Console.WriteLine(match["Title"]);
  15. }

Note 1: The start date of the date ranges must be less than or equal to the end date, but I think that is an acceptable restriction.

Note 2: Before you ask it, I’ve not tested this solution with recurring events, but I assume it does not work in that case. Sorry!

Note 3: To convert my date stored as DateTime to String I used earlier the DateTime.ToString(String format) method with format pattern "yyyy-MM-ddT00:00:00Z", later I found that more-or-less matches the UniversalSortableDateTimePattern, so switched to parameter “u”. If you check the source code of the SPUtility.CreateISO8601DateTimeFromSystemDateTime method used in this sample (for example with Reflector), you can see that instead of simply calling DateTime.ToString with the right pattern, it takes the individual parts of the DateTime (like year, month, day, etc.), and creates the result by appending these component to each other using a StringBuilder. At first it was quite strange for me, but then thought, it must be the fastest way of conversion, as it does not have the overhead of pattern recognition and lot of conditions found in DateTime.ToString. However, if you should work with time zone information, then it might be not ideal for you. Although ISO 8601 supports time zones, AFAIS it is not implemented in CreateISO8601DateTimeFromSystemDateTime, for example, by providing an overload with a DateTimeOffset parameter. I don’t know if CAML supports at all this kind of time zone information provided in the date string. I admit it is not a crucial question most of the cases, however it might produce a surprise if not taken into account. You can read a bit more about that here.

October 5, 2011

Creating a view that filters list items based on user permissions using CAML

Filed under: Bugs, CAML, SP 2010, Views — Tags: , , , — Peter Holpar @ 23:17

Recently I got a task that was about creating a SharePoint view that displays only list items that the logged-in user has editor permissions for. Sounds a bit tough at first, but if you have ever heard about the PermMask field (not very well documented, but you can read more about that here and here) then it gives you some hope.

So I’ve created a test list (called SecTest) and created two items. One of my test users has admin rights, the second one has editor permission for the first item and read permission for the second one. The list inherits the permissions of the parent site, the permission inheritance is broken for the second item.

First I’ve tried to use SharePoint designer to customize the existing All Items view of the list.

Added the PermMask to the FieldRefs:

<FieldRef Name="PermMask" />

And created the filter part for the Query:

        <FieldRef Name=’PermMask’ />
        <Value Type=’Computed’>somevalue</Value>

(I’ve tried different permission mask values, all with the same result.)

Saving the view in SPD resulted the following warning:


Pressing the Details I’ve got the following message:

soap:ServerException of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.0x80131904


(I’ve tried to use Text instead of Computed as the Type attribute of the Value in the Where section of the CAML expression, but it did not help.)

Checking the view in the browser produced an error as well.

I’ve found the following related error message in the SharePoint log (one for the saving in SPD and another one for opening the view in IE):

System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value ‘0xb008431061’ to data type int.     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     at System.Data.SqlClient.SqlDataReader.HasMoreRows()     at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)     at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock)     at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadl…    
Unable to execute query: Error 0x80131904    
Unknown SPRequest error occurred. More information: 0x80131904    

FYI, the hexadecimal value causing the conversion error (see the bolded section above, 0xb008431061 = 756052856929 in decimal) corresponds the permission mask of the Read permission level (see the value of the PermMaskfield in the Roles table of the content database, where in the record where Title is Read, that is SELECT PermMask FROM [YourContentDB].[dbo].[Roles] WHERE Title = ‘Read’)

Next, I’ve tried the same from code using a test console application:

  1. SPList list = web.Lists["SecTest"];
  2. SPQuery query = new SPQuery();
  3. // tried with and without these lines
  4. //query.IncludeAllUserPermissions = true;
  5. //query.IncludePermissions = true;
  6. query.Query = "<Where><Eq><FieldRef Name='PermMask' /><Value Type='Computed'>0xb008431061</Value></Eq></Where>";
  7. query.ViewFields = "<ViewFields><FieldRef Name='PermMask' /></ViewFields>";
  9. SPListItemCollection items = list.GetItems(query);
  11. // exception is thrown at the next line
  12. foreach (SPItem item in items)
  13. {
  14.     Console.WriteLine(item["PermMask"]);
  15. }

The code produced the same error.

I’ve started SQL Server Profiler to create a trace of the SQL side during the code execution. The trace captured the following errors:

Error: 245, Severity: 16, State: 1
User Error Message
Conversion failed when converting the nvarchar value ‘0xb008431061’ to data type int.

The source of the error was the following parameterized query:

exec sp_executesql N’DECLARE @DocParentIdForRF uniqueidentifier SELECT TOP 1 @DocParentIdForRF = Docs.Id FROM Docs WHERE Docs.SiteId = @SITEID AND Docs.DirName = @FDN AND Docs.LeafName = @FLN;   SELECT ScopeId, Acl, AnonymousPermMask FROM Perms WITH (INDEX=Perms_Url) WHERE SiteId=@SITEID AND DelTransId = 0x AND ScopeId IN (SELECT tp_ScopeId FROM Lists WITH (NOLOCK) WHERE tp_WebId=@L7 AND tp_ID = @L2 UNION SELECT t1.ScopeId FROM UserData INNER JOIN Docs AS t1 WITH(NOLOCK) ON (UserData.[tp_ID]=t1.[DoclibRowId]) AND (UserData.[tp_RowOrdinal] = 0) AND (t1.SiteId=UserData.tp_SiteId) AND (t1.SiteId = @SITEID) AND (t1.ParentId = UserData.tp_ParentId) AND (t1.Id = UserData.tp_DocId) AND (UserData.tp_ListId = @L2) WHERE ( (UserData.tp_Level = 1) ) AND (UserData.tp_SiteId=@SITEID AND (UserData.tp_ParentId=@DocParentIdForRF)) AND (UserData.tp_RowOrdinal=0) AND ((UserData.[tp_ID] = N”0xb008431061”) AND t1.SiteId=@SITEID AND (t1.ParentId=@DocParentIdForRF))) OPTION (MAXDOP 1)’,N’@LFFP uniqueidentifier,@SITEID uniqueidentifier,@L2 uniqueidentifier,@FDN nvarchar(4000),@FLN nvarchar(4000),@LISTID uniqueidentifier,@RequestGuid uniqueidentifier,@L7 uniqueidentifier’,@LFFP=’00000000-0000-0000-0000-000000000000′,@SITEID=’D2F4F094-9626-4B84-87D3-260B6B9213EF’,@L2=’C0768FD2-B3D6-478E-ADF3-4A1550DF4CBA’,@FDN=N’Lists’,@FLN=N’SecTest’,@LISTID=’C0768FD2-B3D6-478E-ADF3-4A1550DF4CBA’,@RequestGuid=’E75EC6C1-5CDE-4368-B55C-A9AAA661E539′,@L7=’3ED0DC82-1AC6-4A29-A177-56DA059B2EF8′

I’ve found that removing the bolded condition above helps to run the query without any exception.

In my case the result was (or would be?)  the next one:


The ScopeId field identifies the entity the permission is set on. See the Perms table in the content DB to decode it. In my case the first row is related to the root web, the second on is to the second item in the list, for which we set individual permissions. The Acl field stores the access control list for the entity in a binary format.

It seems that if you include the PermMask field in the CAML query, SharePoint tries to read up the related permission settings but fails due to a bug.

I’ve tried to locate the source of the issue, but was not fully successful. From the stack trace it seems that the ExecuteQueryInternal method of the internal SPSqlClient class (Microsoft.SharePoint namespace, Microsoft.SharePoint assembly) is called to run the SQL query, this method calls the ExecuteReader method of the Microsoft.SharePoint.Utilities.SqlSession, but in the ReadInternal method of System.Data.SqlClient.SqlDataReader the exception is thrown. However I’ve not found the method where the query is incorrectly assembled. I assume it is related to the SetCommand method of the SPSqlClient class but I’ve not yet found the caller of the method. The signature of the method (IntPtr queryText, uint lenQueryText, bool isStoredProc) and the Marshal.PtrToStringUni call within the method suggest that the caller might be even an unmanaged code. See the similar SetCommand method of the SPSqlClientClass class (Microsoft.SharePoint.Library namespace, Microsoft.SharePoint.Library assembly):

[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType=MethodCodeType.Runtime)]
public virtual extern void SetCommand([In] IntPtr pstrQueryText, [In] uint cchQueryText, [In] bool bStoredProcedureSyntax);

Very likely that I have to find an alternative approach to create my permission based view. I have some further ideas, but more about them in a possible post later.

February 9, 2011

How to query external lists on the server side using CAML and alter the external data?

Filed under: BCS, CAML, External list, SP 2010 — Tags: , , , — Peter Holpar @ 08:38

In my former post I’ve wrote about how to query SharePoint 2010 BCS external lists using client side code. Now I try to show how to do it on the server side.

For this example I used the external links sample demonstrated in a this post. Although the download for that post contains only a single sample link having SharePoint in its title, you need to create more such links to see the real effect of the code below. I used that code in a server side console application, but after minor modifications it can be used in web context as well.

  1. String siteUrl = "http://sp2010&quot;;
  3. using (SPSite site = new SPSite(siteUrl))
  4. {
  5.     using (SPWeb web = site.OpenWeb())
  6.     {
  8.         SPList list = web.Lists["External Links"];
  10.         // query items
  11.         SPQuery query = new SPQuery();
  12.         query.Query =
  13.             @"<Where>
  14.                 <Contains>
  15.                     <FieldRef Name='Title' />
  16.                     <Value Type='Text'>SharePoint</Value>
  17.                 </Contains>
  18.               </Where>
  19.               <OrderBy>
  20.                    <FieldRef Name='Title'/>
  21.               </OrderBy>";
  22.         query.ViewFields =
  23.             @"<ViewFields>
  24.                    <FieldRef Name='Title'/>
  25.               </ViewFields>";
  26.         // RowLimit seems to have no effect
  27.         query.RowLimit = 1;
  29.         SPListItemCollection items = list.GetItems(query);
  31.         foreach (SPListItem item in items)
  32.         {
  33.             Console.WriteLine("Title: '{0}'", item["Title"]);
  34.         }
  36.     }
  37. }

As you can see, the code is just like for a standard SharePoint list. You have to include the fields you use in the code in the ViewFields node of the CAML query as FieldRef. Similar to the client side example, specifying the RowLimit seems to have no effect, that is very sad, but true.

If your model allows that (it is not read-only and has the necessary methods), you can use code to alter (create, update, delete) your external data through the external list as well. In our case, the following code snippet will create a new link in the file system:

  1. newItem["Link"] = new SPFieldUrlValue { Url = ";, Description = "Link item from code" };
  2. newItem.Update();
  3. list.Update();

In this case we had to fill only the Link field with the link title and URL, as external list item Title field will be determined in our .NET assembly BCS connector class based on the link title (see the original post for details). It means that the fields you have to fill depend on the nature of the connector and the properties of the external system.

How to query external lists on the client side using CAML?

Filed under: BCS, CAML, Managed Client OM, SP 2010 — Tags: , , , — Peter Holpar @ 08:05

One of the great features of SharePoint 2010 BCS External List concept is that it enables you to publish and access external data similar way as in the case of standard SharePoint list (I wrote similar, because there are significant exceptions as well). It includes the programmatic access either.

Despite of the similarities, I have found forums questions that show it is not always trivial to achieve your goals. In this post I show you a code example that illustrates how one can filter the external data using managed client object model through a  CAML query. For this example I’ve created an external list on my server that connects to the Customers table of the well-known Northwind database. You can find several posts about how to create the external content type (ECT) for you external list, for example here.

Once you have your external list created (called Northwind Customers in my case), you can try this code from your client project:

  1. String siteUrl = "http://sp2010&quot;;
  3. ClientContext clientContext = new ClientContext(siteUrl);
  5. List list = clientContext.Web.Lists
  6.     .GetByTitle("Northwind Customers");
  7. CamlQuery camlQuery = new CamlQuery();
  8. // When using a CAML query for external list
  9. // one should include all referenced fields in ViewFields
  10. // otherwise a "The given key was not present in the dictionary" exception is thrown
  11. // RowLimit seems to have no effect            
  12. camlQuery.ViewXml =
  13.     @"<View>
  14.         <Query>
  15.           <Where>
  16.             <Eq>
  17.               <FieldRef Name='City'/>
  18.               <Value Type='Text'>London</Value>
  19.             </Eq>
  20.           </Where>
  21.           <OrderBy>
  22.             <FieldRef Name='ContactName'/>
  23.           </OrderBy>
  24.         </Query>
  25.         <ViewFields>
  26.           <FieldRef Name='CustomerID'/>
  27.           <FieldRef Name='ContactName'/>
  28.           <FieldRef Name='CompanyName'/>
  29.           <FieldRef Name='City'/>
  30.         </ViewFields>
  31.         <RowLimit>1</RowLimit>
  32.     </View>";
  34. // include referenced field here as well
  35. ListItemCollection listItems = list.GetItems(camlQuery);
  36. clientContext.Load(
  37.      listItems,
  38.      items => items
  39.          .Include(
  40.             item => item["CustomerID"],
  41.             item => item["CompanyName"],
  42.             item => item["ContactName"],
  43.             item => item["City"]));
  44. clientContext.ExecuteQuery();
  46. // display the result
  47. foreach (ListItem listItem in listItems)
  48. {
  49.     Console.WriteLine("CustomerID: '{0}', CompanyName: '{1}', ContactName: '{2}'",
  50.         listItem.FieldValues["CustomerID"],
  51.         listItem.FieldValues["CompanyName"],
  52.         listItem.FieldValues["ContactName"]);
  53. }
  55. Console.Write("Press Enter to continue!");
  56. Console.ReadLine();

The code filters customers that have London specified in the City field and order them by the value of the ContactName field.

Important things to note:

  • You must include all fields you use either to filter, order in CAML query, or to display later in your code into both the ViewFields node of CAML query as FieldRef and load into the ClientContext as illustrated above. Otherwise you will receive a "The given key was not present in the dictionary" exception when referencing the field later.
  • Specifying the RowLimit seems to have no effect, all of the matching items are returned by the query (I found similar info in this forum thread). That is not very nice when working with external lists having large item count.

May 14, 2010

Declaratively adding a lookup field to a list schema using the name of the referenced list

Filed under: CAML, SharePoint — Tags: , — Peter Holpar @ 01:09

This topic is really not a new one. I read already debates several times about if it is possible or not, see for example this post in Josh Gaffey’s blog:

Add SharePoint lookup column declaratively through CAML XML

or this post from Chris O’Brien’s blog:

Creating lookup columns as a feature

There are a lot of comments pro and cons in these posts.

Since I’ve got this question again on the MSDN forum (see create lookup column in list template without guid of parent list), I decided to check the issue myself.

In the past I deployed lists with lookup fields using the name of the referenced list formerly using a custom deployment tool, now I had to create a declarative feature using CAML definitions.

In my test I was to create a lookup field to the Title field of the standard Tasks list.

Based on the guide of Josh in the above post I first try to create a lookup site column.

To achieve this, I’ve created a simple feature that included the following fields.xml file:

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <Elements xmlns="">
  3.     <Field ID="{95d89725-eb97-428b-bc79-ee02ca8b724c}"
  4.         Name="TestField"
  5.         SourceID=""
  6.         StaticName="TestField"
  7.         Group="Test site columns"
  8.         Type="Lookup"
  9.         DisplayName="Test field"
  10.         List="Lists/Tasks"
  11.         FieldRef="ID"
  12.         ShowField="Title" />
  13. </Elements>

The feature.xml file looks like this:

  1. <?xml version="1.0" encoding="utf-8"?>
  2.   <FeatureId="2AABD552-10B1-4561-8A19-2311D39C9A2E"
  3.           Title="List name based lookup field demo feature (site column)"
  4.           Description="The sole purpose of this feature is to demonstrate how to create list name based lookup field in WSS 3.0 using a site column"
  5.           Version=""
  6.           Hidden="FALSE"
  7.           Scope="Site"
  8.           xmlns="">
  9.   <ElementManifests>
  10.     <ElementManifest Location="fields.xml"/>
  11.   </ElementManifests>
  12. </Feature>

Activating the feature resulted in the following in the site columns list:


At first sight that was OK, but after checking the details it turned out to be not perfect:


As you can see on the image above, the place of the parent list was empty.

Checking the field from code showed that the LookupList property of the site column was “Lists/Tasks”, so it was not resolved to the list GUID on feature activation.

When I tried to add the site column to a list, I’ve received the following exception:

Exception from HRESULT: 0x80040E07   at Microsoft.SharePoint.Library.SPRequestInternalClass.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)

  at Microsoft.SharePoint.Library.SPRequest.AddField(String bstrUrl, String bstrListName, String bstrSchemaXml, Int32 grfAdd)

I’ve also tried to create a custom list definition in the same feature, but when I tried to create a new instance based on that definition, I received the following exception:

Cannot complete this action.
Please try again.   at Microsoft.SharePoint.Library.SPRequestInternalClass.CreateListFromFormPost(String bstrUrl, String& pbstrGuid, String& pbstrNextUrl)
   at Microsoft.SharePoint.Library.SPRequest.CreateListFromFormPost(String bstrUrl, String& pbstrGuid, String& pbstrNextUrl)

Although some comments suggested to remove the ShowField attribute to get it work, in my experience it did not help. It only made the case even worse, as now the column information was also empty (as one could expect logically):


So I have to find an alternative way. I decided to try it with a custom list definition that contains the field definition itself.

If you are new to creating custom list definitions, you can read more about that here:

How to: Create a Custom List Definition

I’ve created a feature that contains the custom list definition. The main modifications of the simple custom list schema are the followings:

  1. <ContentTypes>
  2.     <ContentTypeID="0x01"
  3.     Name="Item"
  4.     Group="Item group"
  5.     Description="Item description"
  6.     Version="0">
  7.     <FieldRefs>
  8.       <FieldRef ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Name="Title" Required="TRUE" ShowInNewForm="TRUE" ShowInEditForm="TRUE"/>
  9.       <!– Title –>
  10.       <FieldRef ID="{95d89725-eb97-428b-bc79-ee02ca8b7225}" Name="TestField" Required="FALSE" ShowInNewForm="TRUE" ShowInEditForm="TRUE"/>
  11.       <!– Test field –>
  12.     </FieldRefs>
  13.     <XmlDocuments>
  14.       <XmlDocument NamespaceURI="">
  15.         <FormTemplates xmlns="">
  16.           <Display>ListForm</Display>
  17.           <Edit>ListForm</Edit>
  18.           <New>ListForm</New>
  19.         </FormTemplates>
  20.       </XmlDocument>
  21.     </XmlDocuments>
  22.   </ContentType>
  23.   <ContentTypeRef ID="0x0120" />
  24. </ContentTypes>
  25. <Fields>
  26.   <Field Type="Lookup"
  27.          DisplayName="Test field"
  28.          Required="FALSE"
  29.          List="Lists/Tasks"
  30.          ShowField="Title"
  31.          UnlimitedLengthInDocumentLibrary="FALSE"
  32.          ID="{95d89725-eb97-428b-bc79-ee02ca8b7225}"
  33.          SourceID=""
  34.          StaticName="TestField"
  35.          Name="TestField" />
  36. </Fields>

and a reference for the lookup field was added to the ViewFields:

  1. <ViewFields>
  2.   <FieldRef Name="Attachments">
  3.   </FieldRef>
  4.   <FieldRef Name="LinkTitle">
  5.   </FieldRef>
  6.   <FieldRef Name="TestField">
  7.   </FieldRef>
  8. </ViewFields>

Next, I’ve installed and activated the feature:

stsadm -o installfeature -name ListNameBasedLookUp
stsadm -o activatefeature -name ListNameBasedLookUp -url http://yoursite

The custom list definition appeared on the available list types:


I’ve created a new instance called LookUpList1:


The new list instance contained the lookup field:


And the definition of the field was correct. It pointed to the right list (Tasks) and to the right field (Title):


The field was added to the default view as expected:


In the meantime I’ve created to item in the Tasks list:


When I created a new item, the values of the lookup field came from the specified source list and field:


And last but not least, the item is saved correctly to the list:


So my experience matches to Scott’s comment in Josh’s post:

“To make this method work, the field definition must exist in a list definition since the wiring up to the GUID happens when you the provision the list, rather than when you add a column to an existing content type/list.”

At the end some note:

If you want to alter the feature XML files and would like to avoid caching issues, I suggest you to alter the feature and field GUIDs each time you deploy (deactivate/uninstall/alter/install/activate/IISRESET) the feature. Ignoring this hint may cause you a lot of headache. Believe me, I tell you that from my own experience!

Another side note, that the experience may depend on the version number of SharePoint in my environment I tested it with the December 2009 Cumulative Update (

You can find the full sample feature here.

December 5, 2009

Creating view to display list items created in the current month

Filed under: CAML, SharePoint — Tags: , — Peter Holpar @ 00:50

The task sounds rather simple at first: we should create a view that filters the items and displays only the ones that were created in the current month.

Unfortunately, the user interface gives only a very limited support to create views, so this task could be only implemented by creating two computed columns, first one for the first day of the month the item was created, and the second one for the last day. Then we could use these columns to compare with the current date (Today), when creating the view. I don’t like this solution, especially because of the need for the computed columns.

Instead of this, it is worth to create the view from code, for example, by cloning an existing view (like „All Items”), then by setting the Query property of the SPView class. Here we need a CAML query that makes it possible to filter for date intervals. That is exactly what DateRangesOverlap does. Since in this case the beginning and end dates are the same value, the value of the Created field, it must be included twice in the query.

Let’s see the final result:

SPView view = list.Views["All Items"].Clone("Current Month", 0, false, 
view.Query = @"<Where><DateRangesOverlap><FieldRef
Name='Created' /><FieldRef Name='Created' /><Value

Nothing is perfect. DateRangesOverlap has a little issue when filtering for a given month. You can read about that in my former post: Using Month element in DateRangesOverlap can return items not in the specified month.

Getting the list of new items from code

Filed under: CAML, SharePoint — Tags: , — Peter Holpar @ 00:26

You have probably already noticed the little ‘NEW’ icon next to the items recently created in SharePoint. This icon appears by default next to the items not older than two days, but you can configure the time interval if you wish. This can be done by altering the days-to-show-new-icon parameter for the web application:

stsadm.exe -o setproperty -pn days-to-show-new-icon -pv 0 -url [Your Virtual Server’s URL]

This example sets the value to zero, that means the icon does not appear at all beside the items, even when they are just created.

Sometimes it may be useful to get the list of these kind of „latest items” from code. The key of this job is to know how to get the above-mentioned parameter from code, all the others are simple CAML query. So let’s see, how to do that!

First, we have to get a reference for the web application. If it is the application of the current web, it is so straightforward:

SPWebApplication webApp = web.Site.WebApplication;

Otherwise, it is a bit more difficult, you should know the name of the web application:

SPWebApplication webApp = 

From the web application it is rather easy to get the value of the parameter:

int days = webApp.DaysToShowNewIndicator;

That is the number of days. Based on that there are two approaches to get the items created in this interval. In the first one, we get the exact time, that will be used for comparison, then build the CAML query using that exact value:

DateTime fromDate = DateTime.Now.AddDays(-days);
String fromDateString =
String query =
String.Format("<Where><Gt><FieldRef Name='Created'
IncludeTimeValue='TRUE' /><Value
, fromDateString);

The second approach is even more simple. In this case we use the number of days as an offset in the query:

String query = String.Format("<Where><Gt><FieldRef
Name='Created' IncludeTimeValue='TRUE' /><Value
Type='DateTime'><Today OffsetDays='{0}'
, -days);

Note, that since the item creation happened in the past, we should use negative values in both cases.

March 4, 2008

Using Month element in DateRangesOverlap can return items not in the specified month

Filed under: Calendar, CAML, SharePoint — Tags: , , — Peter Holpar @ 23:02

One can logically think that if you set the CalendarDate property of the SPQuery and apply Month as the Value in the DateRangesOverlap element then the query returns items from the month specified in the CalendarDate property.

For example, using the following code …

SPQuery query = new SPQuery();
query.CalendarDate = new DateTime(2008, 3, 1);

… and applying the following CAML query …

<FieldRef Name='StartDate' />
<FieldRef Name='EndDate' />
<Value Type='DateTime'>
<Month />

… one can expect that items having overlap with March 2008 will be only returned.

I’ve found that it is not exactly so, as elements from the end of February and beginning of April may be also returned. The reason for that I think is the simple fact that these items have overlap with the calendar range of the month view of March 2008 (e.g. 24/02/2008-05/04/2008), as the month calendar view includes the full weeks at the beginning and the end of the month, not only the days between 01/03/2008-31/03/2008.

Since DateRangesOverlap is (another) under-documented element of SharePoint and CAML I can’t decide if it is a bug or a feature. Since it is used primarily in the calendar views it seems to be logical that it should return all items that need to be displayed in the month view. So it is probably by design, sad that this fact is not documented.

This fact caused a misbehavior (or let’s call it bug) in one of our custom application, so we should return to our original complex CAML query assembled from Lt, Gt, Leq, Geq, composite And and Or elements…

Create a free website or blog at