filterREST error above list threshold limit

Forums Autocomplete filterREST error above list threshold limit

This topic contains 11 replies, has 2 voices, and was last updated by  Alexander Bautz 1 month, 1 week ago.

  • Author
    Posts
  • #21693

    Mike
    Participant

    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

  • #21697

    Alexander Bautz
    Keymaster

    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

  • #21699

    Mike
    Participant

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

  • #21735

    Alexander Bautz
    Keymaster

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

    Alexander

  • #21737

    Mike
    Participant

    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

  • #21743

    Alexander Bautz
    Keymaster

    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

  • #21747

    Mike
    Participant

    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?

  • #21749

    Mike
    Participant

    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"}
  • #21763

    Alexander Bautz
    Keymaster

    Please try using these settings:

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

    http://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

  • #21805

    Mike
    Participant

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

  • #21807

    Mike
    Participant

    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.

  • #21814

    Alexander Bautz
    Keymaster

    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

You must be logged in to reply to this topic.