Enable support for lookup columns in other webs in the REST API – Boom! It’s Done?

On Jul 28, 2015, I posted a little item in SharePoint UserVoice: Enable support for lookup columns in other webs in the REST API. It actually seemed like a pretty simple idea, especially since this used to work in earlier versions of SharePoint.

Fast forward to this week, and the UserVoice item got the wonderful Boom! It’s Done response.

Cynic that I am, I didn’t join in on all the “Great job, guys!” enthusiasm. I needed to see if this could actually solve my specific problem, while not overly complicating everything else.

When you dig into the response, you see that the way to solve this particular issue is to use a different endpoint than we are used to. The most common scenario using REST calls is to simply retrieve some items from a list using the /_api/web/lists/GetbyTitle(listName)/items endpoint, using $select, $filter, $expand, and $orderby to specify exactly what we need.

While RenderListDataAsStream looks intriguing, none of the examples cover that scenario very well. It’s not that I can’t see it potentially covering the scenarios, it’s that there’s no obvious route to get there.

Let’s get down to brass tacks. I’ve set up a very simple test case to work this through.

I have a list in the site https://sympraxis.sharepoint.com/sites/Demos2013/RenderListDataAsStream called Client Interactions. (Yes, I named my site RenderListDataAsStream – it makes sense to me for testing, but it may make these URLs a little hard to follow.) It uses a Content Type named Client Interaction – defined in the subsite, and based on Item – which contains a Site Column named Client defined in the top level site, https://sympraxis.sharepoint.com/sites/Demos2013 . Client is a lookup column to the Clients.Title column. In other words, the good IA practices we’ve followed for years, albeit in a very simple example.

This works, as we would expect:

/_api/web/lists/getbytitle(‘Client%20Interactions’)/items

because it returns the ClientId, not the Title.

This does not work because the Client Site Column is in the parent Web:

/_api/web/lists/getbytitle(‘Client%20Interactions’)/items?$select=Client/Title&$expand=Client

The error is exactly what I described in the UserVoice item:

<m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
  <m:code>-1, Microsoft.SharePoint.SPException</m:code>
  <m:message xml:lang="en-US">The field 'Client' is not supported in query. The lookup list is in another web.</m:message>
</m:error>

So, cool. I’ll try this snazzy “new” RenderListDataAsStream endpoint. I decided to use Postman, and Liam Cleary’s post Postman and Office 365 was great help, as I was a bit rusty on this. Why do I need to use Postman? Well, the RenderListDataAsStream requires a POST, not a GET, so we can’t just issue it in the browser. (Strike one.)

/_api/web/GetList(@listUrl)/RenderListDataAsStream?@listUrl=%27%2Fsites%2FDemos2013%2FRenderListDataAsStream%2Flists%2FClient%20Interactions%27

This works fine, but it returns the items based on the default view. This presents several problems:

• Many times we need to request columns which aren’t shown in the default view.
• We can’t rely on the default view never changing – there are people involved.
• Requesting columns you *don’t* need for your client side app is inefficient and makes for a worse user experience. If I only want the Title, I should only request the Title.

For these reasons, since way back in the SOAP and SPServices days, I’ve recommended only requesting the columns you actually need. With the Lists endpoint, that means using $select. As far as I can tell, there’s no way to request the specific columns we need (strike two), though that leads us to another issue…

The documentation for the RenderListDataAsStream endpoint is very difficult to decipher. We can pass URI parameters to alter the results, but I can’t figure out how to request only the columns I need. I also can’t get filters to work using the FilterOp1-10 parameters, nor do several other of the parameters do what I would expect, and I can’t find ANY examples (other than the ones in the documentation page, which really don’t cover anything useful) showing how to use this endpoint. This may well be user error, but until that documentation is much clearer – strike three.

So, does the RenderListDataAsStream endpoint solve the Uservoice item? Yes, it does. I can get the lookup column values from another Web. However, I have to replace my existing REST calls with calls to an endpoint which at the moment doesn’t seem to meet my other needs. I’m very willing to be proven wrong on this, but for now, I can’t recommend switching to the RenderListDataAsStream endpoint just to solve this issue.

If we need to make two separate calls to get the data we need – one to the Lists endpoint and one to the RenderListDataAsStream endpoint, then I’ve got a better answer for you. As with most things, when I posted the UserVoice item about 2.5 years ago, I simply came up with a viable workaround. It’s actually pretty simple, and it always works.

When I want to request items with a lookup value which is stored in another Web, I simply request the column’s ID instead. This is easy:

/_api/web/lists/getbytitle(‘Client%20Interactions’)/items?$select=ClientId

Note that I’m not trying to $expand the Client column, I’m specifically asking for the ID for the lookup value. Then I simply issue another request for the items in the lookup list:

/_api/web/lists/getbytitle(‘Clients’)/items?$select=ID,Title

In my first call, I use the URL of the subsite (where the Client Interactions list lives): https://sympraxis.sharepoint.com/sites/Demos2013/RenderListDataAsStream . In the second call I use the URL where the Clients lookup list lives: https://sympraxis.sharepoint.com/sites/Demos2013/.

Then I just write code in my app to lookup the appropriate values from the second call based on the ClientId from the first call. In AngularJS (my favorite framework these days), I might use a $filter function something like this:

var clientName = $filter("filter")(vm.clients, { ID: thisItemsClientId } );

So the workaround I’ve been using seems to be more useful than the Boom! It’s Done – at least least in my book. I’ll continue trying to make sense of the RenderListDataAsStream endpoint and see if I can help to make the documentation make more sense. I’d rather use the endpoint that “is used by 1st party functionalities to resolve complex fields such as lookups and managed metadata fields”, but only if it has no negative impact on the functionality I can provide or the user experience.

Rebuttals are welcomed. I have no problem being proved wrong for the right reasons.

---

Addendum 2018-03-27

Elio Struyf (@eliostruyf) did a post the other day (after mine above) which gave a little more clarity on how to use the RenderListDataAsStream endpoint: Using the SharePoint RenderListDataAsStream API to fetch lookup and single managed metadata field values. In his post, Elio demonstrates how to use the ViewXml property in the request body to implicitly specify what you want. Yes, that’s the ViewXml you know (and love?) from the bad, old CAML days.

With my example above, I was able to come up with some CAML which enables me to both specify the columns I want as well as to add a filter – all right in the CAML.

POST _api/web/GetList(@listUrl)/RenderListDataAsStream?@listUrl=%27%2Fsites%2FDemos2013%2FRenderListDataAsStream%2Flists%2FClient Interactions%27
Body: 
{
    "parameters": {
        "RenderOptions": 0,
                "ViewXml": "<View><ViewFields><FieldRef Name='Client'/><FieldRef Name='OurOffice'/><FieldRef Name='ContractTotal'/></ViewFields><Query><Where><Geq><FieldRef Name='ContractTotal'/><Value Type='Number'>10001</Value></Geq></Where></Query></View>"
   
    }
}

This request enables me to:

• Request the three columns: Client, OurOffice, and ContractTotal, which aren’t shown in the default view
• Filter for items where the ContractTotal is >= 10001
• Client is a Lookup column, which is returned simply and in a usable way
• OurOffice is a Managed Metadata column, which is returned simply and in a usable way

Here’s an example response:

{
    "Row": [
        {
            "ID": "2",
            "PermMask": "0x7fffffffffffffff",
            "FSObjType": "0",
            "ContentType": "Client Interaction",
            "ContentTypeId": "0x0100CBF575EE0E07E3458A54BC8E2874DB0C00DFC834A16569E841A1FD82B3AD5A0EAE",
            "FileRef": "/sites/Demos2013/RenderListDataAsStream/Lists/Client Interactions/2_.000",
            "ItemChildCount": "0",
            "FolderChildCount": "0",
            "Client": [
                {
                    "lookupId": 2,
                    "lookupValue": "Microsoft",
                    "isSecretFieldValue": false
                }
            ],
            "OurOffice": [
                {
                    "Label": "Rhode Island",
                    "TermID": "d8bcd156-8b02-4ad9-a556-b8c97460dc2e"
                }
            ],
            "ContractTotal": "$200,000.00",
            "ContractTotal.": "200000.000000000"
        }
    ],
    "FirstRow": 1,
    "FolderPermissions": "0x7fffffffffffffff",
    "LastRow": 1,
    "RowLimit": 30,
    "FilterLink": "?",
    "ForceNoHierarchy": "1",
    "HierarchyHasIndention": ""
}

 

As it turns out, the way documentation for things like the RenderListDataAsStream endpoint is being maintained also changed in the last week or so. Now, any of us can submit a pull request for a doc, hopefully making improvements. If you can’t improve the doc, you can also submit a Github issue which may provoke someone else to improve the doc.

When you are reading a doc now, you may see the tools to the right. Clicking on Feedback will take you to the bottom of the page, where you’ll see two buttons:

• Give product feedback – Takes you to UserVoice for that product.
• Give documentation feedback – Allows you to enter feedback for the doc, which ends up in Github issues.

 

I’m not wild about the docs for RenderListDataAsStream, so I guess I need to suggest some edits!