filterREST error above list threshold limit

Forums Autocomplete filterREST error above list threshold limit

Viewing 11 reply threads
  • 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,

      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

    • #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

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