Dear Microsoft: Please Fix Retrieving SharePoint Lookup Columns with REST When the Lookup List is in Another Web

I love SharePoint. I really do. I especially love writing client side code to build awesome applications for my clients.

Today’s annoyance, though, comes while I am in the process of rewriting an application I built on SharePoint 2007, porting it to SharePoint Online in Office 365. This ought to feel like a huge leap forward technologically, and in some ways it does. I’m changing all my SOAP calls with SPServices to REST calls. I’m switching from KnockoutJS to AngularJS, which will simply perform better given the profile of the applications. (KnockoutJS was the right choice years ago when I first built the applications, but the data and feature requirements have outgrown it.)

Unfortunately, I’m running into a simple constraint that makes my life a lot harder. When I first started building these applications five years ago, I created what I’ve got to say is a very solid information architecture. It’s withstood shifting needs and requirements in the interim, and I stand by it. One of the aspects of this good information architecture is storing commonly used reference lists in the root site of the Site Collection. By creating a Site Column which is a lookup into each reference list, I can reuse those common reference values throughout my subsites.

This works great in SharePoint 2007 with SOAP calls. When I retrieve items with one of these lookup Site Columns from a list in a subsite, I simply get the ID and Title values, separated by a “;#”. However, when I try to do the same thing with “modern” REST calls, I get an error like this:

{error":
  {"code":"-1, Microsoft.SharePoint.SPException","message":
    {"lang":"en-US","value":"The field 'Recommendation' is not supported in query. The lookup list is in another web."}
  }
}

I’ve been a good team player, and I’ve suggested they fix this on the SharePoint User Voice in my suggestion Enable support for lookup columns in other webs in the REST API. The votes are up, and it’s been a while.

There’s a workaround, but it’s not very pleasant. (The easiest workaround is to simply stick with SOAP calls and SPServices – I’ve done that in several cases in other projects. But SOAP is officially “deprecated”, so…)

Here’s a specific example. The client I’m working with is in financial services, and they issue recommendations on securities. Those recommendations are very standard, and predictable: Hold, Buy, Sell, etc. In other words, perfect to store in a list in the root site called Recommendations. Why not a Managed Metadata column, you might ask? Well, I also wanted to store several other columns in the Recommendations list, like Description (e.g., “The analyst expects the security to outperform their coverage universe.”), a SortOrder value so I could rearrange the values in dropdowns using SPArrangeChoices, and several other fields which drive configuration of some reports. In other words: great information architecture. The values are all consistent across the various subsites, I store them once, etc. Nice setup.

I created a Site Column back in the beginning called Recommendation, which is a lookup into the title column of the Recommendations list (Hold, Buy, Sell, etc.). I used that Site Column in many Content Types defined on the subsite level. Those Content Types are mainly used in a list I’ll call Notes.

In SOAP with SPServices, I can make this [simplified] call:

$().SPServices.SPGetListItemsJson({
  listName: "Credit Notes",
  CAMLViewFields: "<ViewFields>" +
    "<FieldRef Name='ID'/>" +
    "<FieldRef Name='Title'/>" +
    "<FieldRef Name='Recommendation'/>" +
    "</ViewFields>",
  CAMLRowLimit: 0,
  CAMLQueryOptions: "<QueryOptions>" +
    "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>" +
    "<IncludeAttachmentUrls>TRUE</IncludeAttachmentUrls>" +
    "</QueryOptions>",
  mapping: {
    ows_ID: { "mappedName": "ID", "objectType": "Counter" },
    ows_Title: { "mappedName": "Title", "objectType": "Text" },
    ows_Recommendation: { "mappedName": "Recommendation", "objectType": "Lookup" }
  }
});

This retrieves the items and returns nice JSON for me. Because Recommendation is a lookup column, it comes back as something like “1;#Buy” and that’s easy to turn into a JSON object like:

{ ID: 1, Title: "Buy" }

Easy, peasy.

However, when I try the analogous call in REST:

/_api/web/lists/getbytitle('Notes')/items?$select=ID,Title,Recommendation/Title&$expand=Recommendation

I get the error:

{error":
  {"code":"-1, Microsoft.SharePoint.SPException","message":
    {"lang":"en-US","value":"The field 'Recommendation' is not supported in query. The lookup list is in another web."}
  }
}

In other words, there’s no way to $expand the Recommendation column because it comes from an other Web, even though that is ideal information architecture!

The workaround, which André Lage (@aaclage) pointed out in my UserVoice suggestion (but I clearly didn’t get at the time), is to simply ask for the Recommendation column’s ID instead. This isn’t obvious at all:

[siteCollectionSubSiteUrl]/_api/web/lists/getbytitle('Notes')/items?$select=ID,Title,RecommendationId

This doesn’t follow the syntax we’d expect: we need to append “Id” to the end of the lookup column’s InternalName. Of course, this just gets us the ID of the item in the Recommendations list; it doesn’t fetch us the Title value, which is what we really want. Because of this, I need to do a *separate* REST call to get the items from the Recommendations list and merge the values in my client side code.

[siteCollectionRootSiteUrl]/_api/web/lists/getbytitle('Recommendations')/items?$select=ID,Title

Now, one could argue that this is more efficient. I don’t ask the server to $expand the values across thousands of notes (yes, there are way more than 5000; I’ve written enough about that lately – I may have mentioned it here and here and here and here), so it gets a break. Retrieving the 5-10 values in the reference list (in this case) is no big deal.

But I have a half dozen or so of these lookup columns to deal with in this application, which means a half dozen extra REST calls, plus the code to merge the values. More work for me, but more importantly a longer wait for the application user when they load the page. I believe that poor UX is what has doomed many a SharePoint roll out, and I loathe creating a poor UX myself. In this case, I’ll make it work, but I’d really like to see this change.

by Marc D Anderson via Marc D Anderson's Blog