Cascading Drop Down when more than 5000 items?

[David S Kaimann]

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?

[Alexander Bautz]

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

[David S Kaimann]

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

[David S Kaimann]

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

Attachments:

1. Casc-Error.docx

[Ryan Cornfoot]

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

[Alexander Bautz]

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

[Ryan Cornfoot]

*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

• This reply was modified 9 years, 2 months ago by Ryan Cornfoot.
• This reply was modified 9 years, 2 months ago by Ryan Cornfoot.
• This reply was modified 9 years, 2 months ago by Ryan Cornfoot.

[Alexander Bautz]

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

Alexander

[Ryan Cornfoot]

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>

[Alexander Bautz]

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

Alexander

[Ryan Cornfoot]

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:

1. 

   DFFS_CASC.png
   

[Ryan Cornfoot]

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

[Alexander Bautz]

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

[Ryan Cornfoot]

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.”

[Ryan Cornfoot]

Indexing doesnt help 🙁

[Alexander Bautz]

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

[Ryan Cornfoot]

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?

[Ryan Cornfoot]

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

Making it one field doesnt help either 🙁

[Alexander Bautz]

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

[Ryan Cornfoot]

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

[Alexander Bautz]

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

[Ryan Cornfoot]

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.

[Alexander Bautz]

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

[Ryan Cornfoot]

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.

[Author]

Posts