filterREST error above list threshold limit

[MikeS]

Thanks for an awesome DFFS tool suite. Truly remarkable.

I’m working with a large list of over 40,000 items (in-house list view threshold is 15,000 items). The user would rather type ahead than select 1 or 2 items from a pull-down of 40,000 items (via DFFS cascading dropdowns).

“filterREST”: “startswith(VarSprsd,’n’)”,
This entry in the custom JS returns only those items with a ‘no’ field when lookup list is below 15K but when the lookup list is above 15K I get the following error:
[SPJS-Autocomplete]
{“readyState”:4,”responseText”:”{\”error\”:{\”code\”:\”-2147024860, Microsoft.SharePoint.SPQueryThrottledException\”,\”message\”:{\”lang\”:\”en-US\”,\”value\”:\”The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.\”}}}”,”responseJSON”:{“error”:{“code”:”-2147024860, Microsoft.SharePoint.SPQueryThrottledException”,”message”:{“lang”:”en-US”,”value”:”The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”}}},”status”:500,”statusText”:”Internal Server Error”}

I also tried a CAML query
“filterCAML”: “<Eq><FieldRef Name=’VarSprsd’/><Value Type=’Text’>no</Value></Eq>”,
but it did not filter the list regardless of size.

Do you have an autocomplete filter solution that works regardless of list size, and when multiselect = true?

Thanks

[Alexander Bautz]

Thanks,
Have you indexed the VarSprsd field? Which type field is it? – a Yes/No field, or a plain text field with the the exact value “no”?

Alexander

[MikeS]

Yes, we indexed fields while below the 15K threshold and then loaded the remaining data. So VarSprsd is indexed, as well as the field used for the lookup (VarNo).

VarSprsd is a single line plain text field with the exact value “no”

The filter is being used to return only those values of VarNo where the superseded flag equals “no” (i.e. the most current VarNo).

[Alexander Bautz]

Which version of DFFS and the autocomplete plugin are you using?

Alexander

[MikeS]

Autocomplete plugin: 1.6.22
DFFS Backend 4.4.3.47
spjs-utility version: 1.323

Thanks for your help. Need this to work.

Mike

[Alexander Bautz]

I didn’t notice earlier, but your CAML was missing <Where> – change it like this:

"filterCAML": "<Where><Eq><FieldRef Name='VarSprsd'/><Value Type='Text'>no</Value></Eq></Where>",

Alexander

[MikeS]

The CAML string above works properly with a list below the threshold. When I use a large list the edit form returns the following error repeated over multiple lines as soon as I get enough characters to start the search ( “minLengthBeforeSearch”: 5):

soap:ServerException of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.One or more field types are not installed properly. Go to the list settings page to delete these fields.0x81020014

Portion of my DFFS custom JS:
“searchFields”: [],
“filterCAML”: “<Where><Eq><FieldRef Name=’VarSprsd’/><Value Type=’Text’>no</Value></Eq></Where>”,
“useREST”: false,
“preloadData”:false,
“filterREST”: “”,
“optionDetailFields”: [],
“optionDetailPrefix”: [],
“enforceUniqueValues”: true,
“rowLimit”: 50,
“listOptionsOnFocus”: false,
“minLengthBeforeSearch”: 5,
“reValidateOnLoad”: false,
“allowAddNew”: false,
“isLookupInSelf”: false,
“addNewAdditionalFields”: [],
“multiselect”: true,
“multiselectSeparator”: “; “,
“orderBy”: [],
“setFields”: [],
“debug”: false

Any other suggestions?

[MikeS]

The CAML query above now works with DFFS Cascading Dropdowns on large lists (45,000 items).

<Where><Eq><FieldRef Name='VarSprsd'/><Value Type='Text'>no</Value></Eq></Where>

Still takes longer to return the filtered list (as you mention in your field help) but this is very hopeful. Just need it to work with the autocomplete plugin.

Obviously a REST solution would be optimal. Why won’t this REST string work in the same DFFS Cascading Dropdowns optional filter field?

{"filterField":"VarSprsd","filterValue":"no","operator":"eq"}

[Alexander Bautz]

Please try using these settings:

"useREST": true,
"preloadData":true,

https://spjsblog.com/dffs/dffs-plugins/spjs-autocomplete/#preloadData

This will load all items before you can search, so it will take some time before the control is ready. Let me know how this works out.

Alexander

[MikeS]

CAML Query test results

 "searchFields": [],
 "filterCAML": "<Where><Eq><FieldRef Name='VarSprsd'/><Value Type='Text'>no</Value></Eq></Where>",
 "useREST": "",
 "preloadData": "",
 "filterREST": "",

Returns filtered values to the autocomplete field for any list size. It also, as mentioned above, works with any size list in the DFFS Cascading Dropdowns Optional Filter field.

REST filter test results

"showField": "Title",
 "searchFields": [],
 "filterCAML": "",
 "useREST": "",
 "preloadData": "",
 "filterREST": "startswith(VarSprsd,'n')", 

Does NOT return filtered values to the autocomplete field.

 "searchFields": [],
 "filterCAML": "",
 "useREST": true,
 "preloadData": true,
 "filterREST": "startswith(VarSprsd,'n')", 

Returns filtered values to the autocomplete field for list BELOW 15,000 records. The same values return the following error message when used with ABOVE 15,000 records:

[SPJS-Autocomplete]
{“readyState”:4,”responseText”:”{\”error\”:{\”code\”:\”-2147024860, Microsoft.SharePoint.SPQueryThrottledException\”,\”message\”:{\”lang\”:\”en-US\”,\”value\”:\”The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.\”}}}”,”responseJSON”:{“error”:{“code”:”-2147024860, Microsoft.SharePoint.SPQueryThrottledException”,”message”:{“lang”:”en-US”,”value”:”The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.”}}},”status”:500,”statusText”:”Internal Server Error”}

[MikeS]

We have decided to use the CAML query that works with any size list in Cascading Dropdowns as it allows additional fields to be brought back from the lookup list. (Autocomplete will not allow that when multiselect=true).

Thanks for your help. No further action needed on this topic.

[Alexander Bautz]

Hi,
I’m glad you found a workaround. I’m not sure why the query didn’t work, but I’ll try to look into it in a later version.

Best regards,
Alexander

[Author]

Posts