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

[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]

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"}

[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]

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

[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).

[MikeS]

I would also be interested in this solution. Thank you for your help.

[Author]

Posts