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.

Similar Posts

8 Comments

  1. Hey Marc.
    I struggled with this before and the easy way out is to make the look up list a site column.
    Then you can use it with REST any where in the site collection.

  2. Hey Marc…
    Thanks for the post… Already bookmarked it because I KNOW I will be referencing it… :)

    Q. You state above that you then do a separate call to the “lookup” list which is at the site collection level… Since this is being done client side, Is that a problem for users that might not have permissions to the list at that level?

    Also: in your case, you probably already knew the Lookup list was in a separate Web and had the URL to it… Do you know if the List field definition would return that information? For example: in cases where a web app does not know anything about the setup in place, one would look at the List Field definition to determine its type and if it is a Lookup, then it would also tell us (with SOAP GetList) the lookup list and what field to display from that lookup…

    Just wondering if have had the need to do this type of thing with REST…

    /Paul

    ps. Have a Happy New Year.

    1. @Paul:

      Users have to have permissions to the reference list for the lookup column to work at all. Permissions don’t enter into the issues here.

      If you’d like to get information about the list itself, you can make a call to the list endpoint requesting Fields instead of Items. I can even get just the field I’m interested in:

      [siteCollectionSubSiteUrl]/_api/web/lists/getbytitle('Notes')/Fields?$filter=Title%20eq%20%27Recommendation%27
      

      In the SchemaXml, we can tell where the lookup is sourced:

      <Field Type="Lookup" DisplayName="Recommendation" Group="_My Group" EnforceUniqueValues="FALSE" Required="FALSE" Hidden="FALSE" ReadOnly="FALSE" CanToggleHidden="FALSE" ID="{f8454091-151e-4fdb-bbc8-caab9064e03a}" Name="Recommendation" StaticName="Recommendation" ShowField="Title" UnlimitedLengthInDocumentLibrary="FALSE" WebId="856f44db-47d3-4e24-a85c-3598640e2b03" List="{359f7a24-7d64-4a1a-b4a7-32ef1b5f2960}" SourceID="{856f44db-47d3-4e24-a85c-3598640e2b03}" Version="4" ColName="int4" RowOrdinal="0" Indexed="TRUE" />
      

      We can get the source list GUID from the List attribute; in this case it is {359f7a24-7d64-4a1a-b4a7-32ef1b5f2960}. The WebId tells us where that list lives.

      M.

  3. I complained about this a few weeks ago with Mikael. This week I’m complaining about REST with managed metadata and also publishingpageimages. The little things are causing me to dump rest entirely so that I’m consistent with my project approach as opposed to mixing. Which really doesn’t matter because no one cares but I like to think that others are going to read my code in the future and don’t want them to laugh at me.

  4. Are you using Angular 1.x or 2?

    Have you seen much instances to make use of 2 in the SharePoint landscape?

    1. @Moses:

      I’m still using AngularJS, aka 1.x. The issue with Angular 2+ (and they are on v4 now) is that it wants to take over the entire page. It isn’t suited for the widget-based approach.

      M.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.