Thursday, 28 June 2012

Monday, 25 June 2012

SharePoint 2010 CAML List Joins


A customer pinged me asking about how to write a JOIN query in CAML.  Honestly, I haven’t done this since in a long time, so it took awhile to shake off the cobwebs.  I tried to make things explicit and easy to reproduce in your environment, please let me know in the comments of this post if something is unclear.
SharePoint 2010 adds the ability to create relational lists.  To use the JOIN operator in CAML, your lists must have a defined relation.  Let’s start by creating the lists and their relationships.

Creating the Lists

I first add a standard Contacts list named “DSE” and populate it with some data.
image
Go to the list settings for your new contact list and hover over the column names.  Many SharePoint developers forget this, the name you see in the web UI is the .Title property, which may be different than the internal name that SharePoint uses (the .InternalName property of the SPField object).  For a quick way to see what the actual field name is, hover over the column name and look in the address bar in your browser, and we see that the “Last Name” column is actually the “Title” field.
image
Next, I create a custom list named “Projects” and add a column named “Manager”.  The type is a lookup column.  Note the name “Manager”, we’ll refer to this name in our code.
image
In the additional column settings section for the “Manager” column, I set the lookup to the ID column for the DSE list, and additionally show the First Name and Last Name. 
image
When I add a new item to the Projects list, I get a drop-down that lets me select a value from the lookup column.
image
The result looks like this:
image
Now that we have the lists, a lookup field, and some data, let’s query it.

Querying Using JOINS and SPQuery

Nothing speaks louder than a code sample.  At the top of my code, I add a few using directives.
using System;
using System.Linq;
using Microsoft.SharePoint;
Next, the code to query the list. 
The first thing to notice is the SPQuery.Joins property which lets me provide 1 or more joins for the list.  The list must already have a lookup column and a relation defined to the list being joined to.  Notice in the Joins property that we refer to the name that we provided when we created the lookup column, “Manager”. 
The second thing to notice is the ProjectedFields property.  This is where we tell SharePoint how to project the lookup columns into the result.  In a contacts list, the “First Name” column has an internal name of “FirstName”.  The “Last Name” column is actually the Title column (see above for how to determine the .InternalName of a field). 
Finally, the ViewFields property lets us define which fields are included in the result.  We use the same name that we used in the ProjectedFields property.  This name could be anything, so long as the name in ProjectedFields and the name in ViewFields match.
class Program
{
static void Main(string[] args)
{
using (SPSite site = new SPSite("http://spstc.sharepoint.com"))
{
    SPWeb web = site.RootWeb;

    SPQuery query = new SPQuery();

    query.Joins = "<Join Type='INNER' ListAlias='DSE'>" +
                    "<Eq>" +
                        "<FieldRef Name='Manager' RefType='Id'/>" +
                        "<FieldRef List='DSE' Name='ID'/>" +
                    "</Eq>" +
                    "</Join>";
    query.ProjectedFields =
        "<Field Name='DSEFirstName' Type='Lookup' " +
                "List='DSE' ShowField='FirstName'/>" +
        "<Field Name='DSELastName' Type='Lookup' " +
                "List='DSE' ShowField='Title'/>";

    query.ViewFields = "<FieldRef Name='Title'/>" +
                        "<FieldRef Name='DSEFirstName'/>" +
                        "<FieldRef Name='DSELastName'/>";
    SPList customerList = web.Lists["Projects"];
    SPListItemCollection items = customerList.GetItems(query);
    foreach (SPListItem item in items)
    {
        SPFieldLookupValue dseLastName =
            new SPFieldLookupValue(item["DSELastName"].ToString());
        SPFieldLookupValue dseFirstName =
            new SPFieldLookupValue(item["DSEFirstName"].ToString());

        Console.WriteLine("{0}  {1}   {2}",
                item.Title,
                dseLastName.LookupValue,
                dseFirstName.LookupValue);
    }
}
}
}
}

Thursday, 14 June 2012

Tuesday, 12 June 2012

How To Create SharePoint 2010 custom visual webpart properties

http://onlinecoder.blogspot.in/2011/04/custom-properties-in-sharepoint-2010_7158.html

https://www.nothingbutsharepoint.com/sites/devwiki/articles/Pages/How-To-Create-SharePoint-2010-custom-visual-webpart-properties.aspx

http://msdn.microsoft.com/en-us/library/dd584174%28v=office.11%29.aspx

Content Organizer for a Document Library

http://community.bamboosolutions.com/blogs/sharepoint-2010/archive/2010/11/03/sharepoint-2010-cookbook-content-organizer-for-document-library.aspx

The other thing that happens after the activation is that SharePoint will create a document library named DropOffLibrary along with a RoutingRules list.  The Drop Off Library will be the default destination when a user tries to upload a document to this site.  The Routing Rules list, as its name implies, contains the rules for how a document is to be routed to its final destination.

  • Redirect Users to the Drop Off Library: Set this check box to force all uploads to go to the Drop Off Document Library.   When an individual document is uploaded, the document properties window for Drop Off Library is displayed, at which point metadata properties can be filled in and the submission process completed. After submission, the routing rules are applied to route the document appropriately and the user is shown the final URL for the item.
  • Sending to Another Site:  Set this property to allow rules to be created that direct uploads in the current site to be sent to another site in your SharePoint Web app.   The destination site must also have the Content Organizer feature installed and activated. You can only route documents within a SharePoint Web application.
  • Folder Partitioning: This features allow you to create a threshold limit on how many documents can be stored in a document library.  SharePoint 2010 recommends a maximum of 5,000 documents per folder. Once the limit is reached, you can instruct SharePoint to automatically create a new folder, route documents to that new folder, and let the user know about the URLs of the final destination.


  • the Content Organizer is a very cool feature in SharePoint 2010.  It allows you to capture a set of standard documents and route them to the proper destination, and the end user doesn't have to worry about where it should go.  This feature should be used in conjunction with Document IDs, Term Sets, and Metadata publishing in order to design an effective document management strategy for your SharePoint portal.