Create SharePoint Content Organiser rules programmatically with lookup fields

Tue, Dec 12, 2017 3-minute read

I’ve wittered on before about how much I like the content organiser in SharePoint. It’s really pretty handy. Even creating rules that target other content organisers is possible. (With a little trickery.)

But since it’s SharePoint, it’s not without its quirks. I like to do as much as my dev work programmatically as possible. Every now and then you think maybe you’ve hit something you can’t script and you’ll have to resort to adding a deployment step. I was close with this.

So here’s the scenario:

  1. A Site Column which is a lookup in to a list
  2. Content Organiser rules that use a value in this list as one of the routing conditions

Creating site column lookup fields programmatically

Creating a site column which is a lookup field is easy:

using (var site = new SPSite("http://yoursite")
using (var web = site.OpenWeb())
{

// lookuplist is the list you'll be retrieving values from
var lookupList = web.Lists.TryGetList("yourlist");

// add the lookup field to the web
web.Fields.AddLookup("LookupFieldName", lookupList.Id, false);
             
var lookupField = (SPFieldLookup)web.Fields["LookupFieldName"];
lookupField.LookupField = "FieldNameOfTheColumnToRetrieveFromLookupList";
lookupField.AllowMultipleValues = false;
lookupField.Group = "Custom";
lookupField.Update(true);
}

And then adding this to a content type is easy too:

// Add to Content type
var ct = web.ContentTypes["My Content Type"];  
ct.FieldLinks.Add(new SPFieldLink(lookupField));
ct.Update(true);

Creating content organiser rules programmatically

Creating a content organiser rule is also easy:

var newRule = new EcmDocumentRouterRule(web)
            {
                Name = "Test Routing Rule",
                Description = "Routes documents to somewhere.",
                ContentTypeString = "My Content Type",
                RouteToExternalLocation = false,
                CustomRouter = null,
                Priority = "5",
                TargetPath = targetPath, // site relative path to your target library
                ConditionsString = conditions
            };
 
            newRule.Update();

So the bit missing in the above is the definition of the variable ‘conditions'.

In normal usage (with one condition) this looks like this:

var mySimpleField = web.Fields["My Field"];
var conditions = string.Format("<conditions><condition Column='{0}|{1}|{2}' Operator='IsEqual' Value='{3}'></condition></conditions>", mySimpleField.Id, mySimpleField.InternalName, mySimpleField.Title, "FieldValue");

Creating content organiser rules with lockup fields in the conditions

The issue with lookup fields is that the field doesn’t actually contain any values. (Compare this with a ‘choice’ column, which does.) So if you just assign the value that you want to see, then your condition will fail with a “value does not fall within the expected range” error. A lookup field actually references the ID of the item in the lookup list itself. So in order for your lookup field to work, you need to go and find the ID of the item that you’re using in your condition:

var myLookupField = web.Fields["MyLookupField"];
var lookupList = web.Lists[myLookupField.LookupList];

int itemId = 0;
// NOTE! Only use the GetItems() method on really small lists. If your lookuplist has lots of items, use CAML.
foreach (SPListItem item in list.GetItems().Cast<splistitem>().Where(item => (string)item["FieldNameOfTheColumnToRetrieveFromLookupList"] == "FieldValue"))
            {
                itemId = item.ID;
            }

var conditions = string.Format("<conditions><condition Column='{0}|{1}|{2}' Operator='IsEqual' Value='{3}'></condition></conditions>", myLookupField.Id, myLookupField.InternalName, myLookupField.Title, itemId);
</splistitem>

And with this (or something like it) your content organiser rule will now be configured to use values in a lookup list.

Bonus: creating content organiser rules with multiple conditions

The conditionsstring is actually just XML. It’s definitely not CAML as I assumed it would be. So if you wanted to create a rule with more than one condition (up to 5 are supported I think), then you simple do:

<conditions>
<condition Column...></condition>
<condition Column...></condition>
</conditions>

Jublees.

Note: XML is case sensitive but WordPress appears to be doing weird things with the XML parts of this post. So Conditions etc. should have a capital C. For a full treatment of setting up content organiser rules, this poorly formatted article has useful info.