none
REST API - Can't expand Title of lookup columns "System.ArgumentException : Value does not fall within the expected range" RRS feed

  • Question

  • I have a list that has more than 12 columns of type lookup with more than 5000 items and I am using a date field as an indexed column so I can restrict the results to meet the list view threshold.  I am also using $select to restrict the columns to meet the lookup column threshold.

    If I run the query below it all works as expected:

    _api/web/Lists/MyList/items/?$Filter=Created gt '2015-03-10T05:00:00.000Z' and Created lt '2015-03-17T18:25:00.712Z'&$select=Lookup1/Id&$expand=Lookup1



    If I run this query it does not work:

    _api/web/Lists/MyList/items/?$Filter=Created gt '2015-03-10T05:00:00.000Z' and Created lt '2015-03-17T18:25:00.712Z'&$select=Lookup1/Title&$expand=Lookup1

    Error:

    <m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <m:code>-2147024809, System.ArgumentException</m:code>
    <m:message xml:lang="en-US">Value does not fall within the expected range.</m:message>
    </m:error>

    The lookup column "Lookup1" is using Title as the column's information and this should be working.  What is interesting is that changing the lookup threshold has no effect on this but raising the list item view limit does make it work.  The date range is selecting less than 5000 items.  So what am I missing here? I just want the Title for the lookup column and my query has about 10 items in it.



    • Edited by AlbertoJ123 Tuesday, March 17, 2015 8:26 PM typo
    Tuesday, March 17, 2015 8:25 PM

Answers

  • Thanks for looking into this, after opening a ticket with Microsoft this was determined to be a bug related to unique permissions.  Although the documentation states that a list can have up to 50,000 unique permissions per folder it seems there ia a bug causing this to not be the case.  As of now we are awaiting for a hot fix from Microsoft.
    • Marked as answer by AlbertoJ123 Monday, April 20, 2015 6:03 PM
    Monday, April 20, 2015 6:02 PM

All replies

  • Hi,

    According to your description,my understanding is that when you expand the lookup column title in rest api query, then it occurs the error above.

    This error System.ArgumentException: Value does not fall within the expected range with exception message may be due to throttling limit set at List View Lookup Threshold settings in WebApplication.

    In the case, you have 12 lookup column in a list, however, by default, the List view lookup threshold is 8.

    To resolve this error, open the SharePoint Central Administration site and click  “Manage Web Application” link located under Application Management Group. Select the web application that has the problem. Select the “Resource Throttling” link under the “General Settings” 

    Navigate to List View Lookup Threshold section and change the value to a higher value depending on the count of Lookup, Person/Group, or workflow status field available in the list that caused the issue.

    If the error still exists, I suggest you can use Fiddler to compare the difference between select id and select title.

    More information:

    How to resolve Error Value does not fall within the expected range with Exception

    Fiddler

    Thanks

    Best Regards


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



    Wednesday, March 18, 2015 7:25 AM
  • Thanks for the reply,

    We tried that before posting here, we set the list view item limit to 5000 and the lookup limit to 36 and the error still occurred.  Only changing the list view item limit to a high number fixed the error(25000).  Also, if I do an $expand on /Id instead of /Title the query resolves as expected.  It was my understanding that by using $select to only pick one column I am restricting the number of lookup columns the SQL query needs to join to only those specified in the $select.

    Wednesday, March 18, 2015 2:49 PM
  • Hi,

    I tried to reproduce your issue with the following steps:

    1. I create 12 lookup columns and add 5001 items in the list.

    2. I tried to filter the item with specific date range and then expand on the Lookup1/Title using Rest API. The result can be return if the item count is less than 5000.

    I assume your environment have something different from mine. Would you please tell me more detailed information about your environment so that I can try to reproduce the issue.

    Thanks

    Best Regards


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

    Thursday, March 19, 2015 5:32 AM
  • Our list has about 7000 items with 4 folders but most of the list items are outside of the folders.  Other than having docave as our migration tool our SharePoint farm has not been customized.  What other kinds of differences would you be expecting?
    Thursday, March 19, 2015 1:07 PM
  • Looking through the logs the error is thrown due to the following error messages:

    03/19/2015 08:26:12.12 w3wp.exe (0x6D34) 0x7F38 SharePoint Foundation Fields 84h8 High Field with internal name 'LookupFieldName_x005f_Title' already exists in the field cache 3446f49c-f2d2-d0a2-89f4-59dee19b2f45
    03/19/2015 08:26:12.12 w3wp.exe (0x6D34) 0x7F38 SharePoint Foundation Fields ki9p High Unable to add join related fields to the Query.[Error 0x80070057] 3446f49c-f2d2-d0a2-89f4-59dee19b2f45
    03/19/2015 08:26:12.12 w3wp.exe (0x6D34) 0x7F38 SharePoint Foundation General xxpm High Unable to execute query: Error 0x80070057 3446f49c-f2d2-d0a2-89f4-59dee19b2f45
    03/19/2015 08:26:12.12 w3wp.exe (0x6D34) 0x7F38 SharePoint Foundation General 8e2s Medium Unknown SPRequest error occurred. More information: 0x80070057 3446f49c-f2d2-d0a2-89f4-59dee19b2f45
    03/19/2015 08:26:12.12 w3wp.exe (0x6D34) 0x7F38 SharePoint Foundation General aix9j High SPRequest.GetListItemDataWithCallback2: UserPrincipalName=<removed>, AppPrincipalName= ,pSqlClient=<null> ,bstrUrl=siteUrl ,bstrListName=<removed> ,bstrViewName=<null> ,bstrViewXml=<View Scope="RecursiveAll"><Query><Where><Eq><FieldRef Name="ID" /><Value Type="Counter">5481</Value></Eq></Where></Query><ViewFields><FieldRef Name="LookupFieldName" LookupId="TRUE" /><FieldRef Name="LookupFieldName_x005f_Title" /></ViewFields><ProjectedFields><Field Name="LookupFieldName_x005f_Title" Type="Looku ,fSafeArrayFlags=SAFEARRAYFLAG_DATES_IN_UTC 3446f49c-f2d2-d0a2-89f4-59dee19b2f45


    The very first message states that it is unable to add join related fields, what could be the possible cause of this?  As you can see rather than specifying a date range which I know contains less than 5000 items, even filtering based on ID gives the same result.

    Friday, March 20, 2015 4:01 PM
  • Hi,

    I suggest you can check if you have some same title value in the source list.

    As the ID is a unique value field, so when you do query with ID, it will look up and join the ID which matches, but for the Title, then it may contains the same value and when you do look up join to find title, it will generate large number of records exceed the 5000 items.

    Also, I suggest you can try to add the both ID and Title to filter data to test if it works.

    Thanks

    Best Regards 


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

    • Proposed as answer by Eric Tao Friday, April 3, 2015 1:45 AM
    • Marked as answer by Eric Tao Sunday, April 5, 2015 8:40 AM
    • Unmarked as answer by AlbertoJ123 Monday, April 20, 2015 6:02 PM
    Tuesday, March 24, 2015 6:36 AM
  • Thanks for looking into this, after opening a ticket with Microsoft this was determined to be a bug related to unique permissions.  Although the documentation states that a list can have up to 50,000 unique permissions per folder it seems there ia a bug causing this to not be the case.  As of now we are awaiting for a hot fix from Microsoft.
    • Marked as answer by AlbertoJ123 Monday, April 20, 2015 6:03 PM
    Monday, April 20, 2015 6:02 PM
  • Hey guys and gals, any update to the item from Microsoft.??
    Tuesday, November 15, 2016 5:54 AM