Cascading Drop Down when more than 5000 items?

Forums Cascading dropdowns Cascading Drop Down when more than 5000 items?

Viewing 23 reply threads
  • Author
    Posts
    • #8542
      David S Kaimann
      Participant

      Alex,

      I was trying to use this solution on a list where I had more than 5000 items and it wouldn’t work until I took the list under 5000 items. Is this anything that can be changed – or – is this tied to the List View Threshold?

    • #8543
      Alexander Bautz
      Keymaster

      Hi,
      Which version of spjs-utility.js are you using? – I posted an update a few month ago to add this support: https://spjsblog.com/2015/07/26/spjs-utility-updated-with-support-for-querying-large-lists/

      Alexander

    • #8547
      David S Kaimann
      Participant

      I’m using version 1.250 that was published on 9/8/2015 with the “DFFS and Plugins” release. I see that you have an updated version since 9/8/2015, so I can try that

    • #8548
      David S Kaimann
      Participant

      I confirmed that I get the same error (attached) when using the newest published files under “DFFS and plugins”.

      Attachments:
    • #8661
      Ryan Cornfoot
      Participant

      I also get this error when Querying a list of 20K items, if I reduce its size it works fine.

    • #8662
      Alexander Bautz
      Keymaster

      Could you bring up the developer console (hit F12) and look at the “Network” tab. Double click the “lists.asmx” line with status 500 (or the red one) – can you send me the details from the “Request header” and “Response header”?

      Also, can you double check the spjs-utilty.js version by bringing up the developer console and select the “Console” tab. Enter this by the cursor and hit enter:

      spjs.utility.version

      Whet does the version number read?

      Alexander

    • #8680
      Ryan Cornfoot
      Participant

      *Request Headers*

      Accept:application/xml, text/xml, */*; q=0.01
      Accept-Encoding:gzip, deflate
      Accept-Language:en-GB,en-US;q=0.8,en;q=0.6
      Connection:keep-alive
      Content-Length:1080
      Content-Type:text/xml; charset=UTF-8
      Cookie:_ga=GA1.2.140540675.1424861500; Ribbon.ListForm.Edit=632492|-1|328|-800423560; WSS_KeepSessionAuthenticated={9e742d76-c8cb-4594-a749-f80bafb31eca}
      Host:ameyportal.ameygroup.int
      Origin:http://MYSERVER
      Referer:http://MYSERVER/sites/travel/Lists/Travel%20Requests/NewForm.aspx?RootFolder=&IsDlg=1
      User-Agent:Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/45.0.2454.101 Safari/537.36
      X-Requested-With:XMLHttpRequest

      *Response Headers*

      Cache-Control:private
      Content-Length:711
      Content-Type:text/xml; charset=utf-8
      Date:Fri, 09 Oct 2015 13:11:46 GMT
      MicrosoftSharePointTeamServices:14.0.0.6115
      Server:Microsoft-IIS/7.5
      X-AspNet-Version:2.0.50727
      X-MS-InvokeApp:1; RequireReadOnly
      X-Powered-By:ASP.NET

      *SPJS Version*
      1.252

    • #8685
      Alexander Bautz
      Keymaster

      Hi,
      Sorry, I forgot to tell I need to see the “Response” also – can you add it?

      Alexander

    • #8686
      Ryan Cornfoot
      Participant

      It says something about field types but it works fine <5000 items…

      <?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><soap:Fault><faultcode>soap:Server</faultcode><faultstring>Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.</faultstring><detail><errorstring xmlns="http://schemas.microsoft.com/sharepoint/soap/">One or more field types are not installed properly. Go to the list settings page to delete these fields.</errorstring><errorcode xmlns="http://schemas.microsoft.com/sharepoint/soap/">0x81020014</errorcode></detail></soap:Fault></soap:Body></soap:Envelope>
    • #8690
      Alexander Bautz
      Keymaster

      Can you post the config / query for the cascading dropdown?

      Alexander

    • #8701
      Ryan Cornfoot
      Participant

      Dont know if this helps, its just a screenshot of the config, there are more Field Internal names but you cant see them due to length.

      Attachments:
    • #8714
      Ryan Cornfoot
      Participant

      Alex, Could this be related to the 5000 item threshold set by our SP Farm admin?

    • #8717
      Alexander Bautz
      Keymaster

      Yes, It has to do with this setting, but my “plan” with the “queryItemsThrottled” in SPJS-utility.js was to overcome this limitation but splitting the query in chunks of 5000 items.

      Could you possibly try to index the columns you use in the dropdown (in the source list) to see if this helps?

      PS: Are some of these columns of lookup, person or taxonomy / managed metadata?

      Alexander

    • #8719
      Ryan Cornfoot
      Participant

      They are all basic data, name, divisions, accounts etc. Nothing Fancy, no metadata.

      Ill try an index now.

      P.S I have just checked the error in F12 Developer mode and it is saying the following: “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”

    • #8721
      Ryan Cornfoot
      Participant

      Indexing doesnt help 🙁

    • #8723
      Alexander Bautz
      Keymaster

      Could you try changing to only ONE level (field) in the cascading dropdown configuration in DFFS backed to see if this prevents the error? – also, the indexing might take some time to complete so maybe you need to retest this tomorrow.

      Alexander

    • #8725
      Ryan Cornfoot
      Participant

      I can try the one field yes, however I’m thinking I might try it a different way…

      User selects their Division, Business Unit and account, then enters their ID number this will pre filter the results to a maximum of 951 possible items. (Max people in any one account)

      Does that sound like it could make a difference? The reason I think it will is because the initial load query will only return 5 unique values (Divisions) Or do CAML Queries not work this way?

    • #8727
      Ryan Cornfoot
      Participant

      I have just tested the above theory, it does not work.

      Making it one field doesnt help either 🙁

    • #8729
      Alexander Bautz
      Keymaster

      Hi,
      I may have misled all of you trying to get this working on a large list. I mistakingly thought this solution relied upon the “queryItems” / “queryItemsThrottled” in SPJS-utility.js, but clearly I remembered wrong.

      I’ll look into it and rewrite it to support the “queryItemsThrottled” method from SPJS-utility.js to split a query failing because it triggers the “list view threshold” of 5000 items.

      Ryan: I’m not sure this will fix your problem, but it’s worth a try. I’ll post here when I have updated the solution.

      Sorry for the inconvenience,
      Alexander

    • #8731
      Ryan Cornfoot
      Participant

      OK Alex, thanks for you time and efforts on this!

    • #8733
      Alexander Bautz
      Keymaster

      I have now posted an update to the spjs-casc.js / spjs-casc_min.js file (v3.525): get it here

      This is the changes:

      Added a method to bypass the list throttling occurring in SP 2010 and 2013 when exceeding 5000 items in a list. Please note that the query will take some time if you have more than 5000 items in the list.

      I’m sorry I did not see this immediately, but I must have confused it with another of my solutions.

      Ryan:
      Can you try this new version, and if you still have trouble, try using the ID column in the “Comma separated list of fields (FieldInternalName) in the lookup list.” field in DFFS backend where you configure Cascading dropdowns. Also turn on “Write debug information to the top of the page” and let me know if you see anything in the top of the page.

      Alexander

    • #8762
      Ryan Cornfoot
      Participant

      Hi Alex,

      It works, but the load time in IE8 is horrendous (Not your fault obviously)

      Google Chrome – NewForm Load Time = 7 sec (Up by around 5 seconds)
      Internet Explorer 8 – NewForm Load Time = 1 min 38 sec! (up by over a minute)

      Im going to try and go down the route of getting the threshold removed.

    • #8764
      Alexander Bautz
      Keymaster

      I’m glad it worked technically, but as you have observed the load time will suffer – especially in older IE versions.

      You might be able to use the Autocomplete plugin – have you looked at this one for this scenario?

      Alexander

    • #8766
      Ryan Cornfoot
      Participant

      I wasn’t sure Autocomplete could do it, but I have just seen your reply in the Autocomplete Forum.

      Ill have a go with that and see how it goes.

      Thanks for all your help Alex.

Viewing 23 reply threads
  • You must be logged in to reply to this topic.