none
OData query with select and expand results in an error when the related lookup is not required and contains blank values RRS feed

  • Question

  • I'm battling an OData issue with SharePoint online (can't confirm if it's SharePoint on Prem too as don't have it setup) where I'm trying to pull data from SharePoint custom lists into some nicely formatted grids. The issue relates to including values from lookup fields where the lookup is not a required field so there can be records with no related entity. When attempting to read data in I'm using a request in a format as follows:

    https://xxxxxx.sharepoint.com/customersite/_vti_bin/listdata.svc/Client?$select=CompanyName,PrimaryContact/Name,SecondaryContact/Name&$expand=PrimaryContact,SecondaryContact

    The PrimaryContact field is required so has a matching value for every record so with that alone the request passes perfectly. However, the SecondaryContact is not a required field so there may be no related Secondary Contacts and in that case you receive a response of:

    <m:error>
        <m:code></m:code>
        <m:message xml:lang="en-US">An error occurred while processing this request.</m:message>
      </m:error>

    I've found someone else experiencing the exact same issue and although there is confirmation that the query he is providing is valid and that SharePoint isn't returning the data correctly, there is no resolution that I can find anywhere. The link to his post is below:

    https://social.msdn.microsoft.com/Forums/en-US/fac4eefa-b14f-4b3a-b77d-7cebc0a21b6b/odata-query-with-select-and-expand-results-in-an-error?forum=os_specifications

    Is this a bug that will be fixed, is there a workaround?

    Tuesday, February 28, 2017 5:49 PM

All replies

  • Hi,

    In SharePoint Online or SharePoint 2013 on-premise, we can use the following REST API to achieve it.

    https://xxxxxx.sharepoint.com/customersite/_api/web/lists/getbytitle('Client')/items?$select=CompanyName,PrimaryContact/Name,SecondaryContact/Name&$expand=PrimaryContact,SecondaryContact

    More information for your reference:

    REST API reference and samples

    https://msdn.microsoft.com/en-us/library/office/jj860569.aspx

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, March 1, 2017 2:44 AM
    Moderator
  • Hi,

    Please check the Dennis's suggestion, if it helps you, please mark the reply as answer, it will make others who stuck with the similar issue easier to search for valid solutions in this forum.

    Thanks


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, March 10, 2017 8:38 AM
    Moderator