{"id":1687,"date":"2016-08-25T18:15:48","date_gmt":"2016-08-25T16:15:48","guid":{"rendered":"https:\/\/blog.kodono.info\/wordpress\/?p=1687"},"modified":"2016-12-22T16:31:01","modified_gmt":"2016-12-22T15:31:01","slug":"bypass-a-lookup-field-not-displayed-because-of-threshold-on-newform-and-editform-sharepoint-2013","status":"publish","type":"post","link":"https:\/\/blog.kodono.info\/wordpress\/2016\/08\/25\/bypass-a-lookup-field-not-displayed-because-of-threshold-on-newform-and-editform-sharepoint-2013\/","title":{"rendered":"Bypass a lookup field not displayed because of threshold on NewForm and EditForm [Sharepoint 2013]"},"content":{"rendered":"<p><strong>EDIT 22\/Dec\/2016<\/strong>: there is s a new version of this article at <a href=\"https:\/\/blog.kodono.info\/wordpress\/2016\/12\/22\/2-bypass-a-lookup-field-not-displayed-because-of-threshold-on-newform-and-editform-sharepoint-2013\/\">https:\/\/blog.kodono.info\/wordpress\/2016\/12\/22\/2-bypass-a-lookup-field-not-displayed-because-of-threshold-on-newform-and-editform-sharepoint-2013\/<\/a><\/p>\n<p>Sharepoint is really annoying to limit the access to the list with more than 5,000 items&#8230; It causes many troubles, and one of them is when you use a lookup field into a form that is tied to a big list. Sharepoint will not display the field\/dropdown but will show the message:<\/p>\n<blockquote><p>This is a lookup column that displays data from another list that currently exceeds the List View Threshold defined by the administrator<\/p><\/blockquote>\n<p>There is a solution to bypass this problem and show our lookup dropdowns.<\/p>\n<p>You need:<\/p>\n<ul>\n<li><a href=\"http:\/\/jquery.com\/\">jQuery<\/a><\/li>\n<li><a href=\"http:\/\/aymkdn.github.io\/SharepointPlus\/\">Sharepoint<\/a><\/li>\n<li>And I use <a href=\"https:\/\/harvesthq.github.io\/chosen\/\">Chosen<\/a> to have a better dropdown<\/li>\n<\/ul>\n<p>I&#8217;ll show how I did it for the <strong>EditForm<\/strong>.<\/p>\n<p>First, <strong>copy\/paste the below JavaScript code<\/strong> into a file that you&#8217;ll store somewhere on your Sharepoint:<\/p>\n<pre class=\"brush:javascript\">\r\n\/\/ load jQuery, SharepointPlus and Chosen\r\nvar toLoad = '&lt;script src=\"\/Toolbox\/Documents\/js\/jQuery\/jquery-1.12.3.min.js\">&lt;\/script>'\r\n           + '&lt;script src=\"\/Toolbox\/Documents\/js\/SharepointPlus\/3.13\/sharepointplus-3.13.min.js\">&lt;\/script>'\r\n           + '&lt;link href=\"\/Toolbox\/Documents\/js\/Chosen\/1.5.0\/chosen.min.css\" rel=\"stylesheet\" type=\"text\/css\">'\r\n           + '&lt;script src=\"\/Toolbox\/Documents\/js\/Chosen\/1.5.0\/chosen.jquery.min.js\">&lt;\/script>'\r\ndocument.write(toLoad);\r\n\r\n(function() {\r\n  var aDeferred=[];\r\n  var modal;\r\n  \/\/ list the fields we want to fix\r\n  \/\/ \/!\\ Add here the Field ID of the fields that need to be fixed\r\n  var lookupFieldsToFix = [\"Voucher_x0020_Code\", \"User_x0020_Name\"];\r\n  \/\/ \/!\\ Specify the name of the related list, as well as the Column to retrieve\r\n  var configurationsLookup = [{list:\"Vouchers\", field:\"Title\"}, {list:\"Users\", field:\"Name\"}];\r\n\r\n  \/\/ this function is triggered once the form is loaded\r\n  function allLoaded(ctx) {\r\n    \/\/ delete all existing localStorage\r\n    localStorage.removeItem('RequestsLookup')\r\n    \/\/ check if our call to lists are done\r\n    $.when.apply(this, aDeferred).done(function() {\r\n      var save={}, count=0, i;\r\n      for (i=arguments.length; i--;) {\r\n        if (arguments[i]) {\r\n          save[arguments[i].field] = arguments[i].choices; \/\/ save it as {fieldName:choices}\r\n          count++;\r\n        }\r\n      }\r\n      if (count > 0) {\r\n        \/\/ we use localStorage\r\n        localStorage.setItem('RequestsLookup', JSON.stringify(save));\r\n        \/\/ now reload the page\r\n        $SP().closeModalDialog();\r\n        $('#aspnetForm').hide().after('&lt;h1>Reloading...&lt;\/h1>');\r\n        window.location.reload();\r\n      } else {\r\n        \/\/ we use Chosen\r\n        for (i=lookupFieldsToFix.length; i--;) {\r\n          var e=WPQ2FormCtx.ListSchema[lookupFieldsToFix[i]];\r\n          $(document.getElementById(e.Name+\"_\"+e.Id+\"_$\"+e.FieldType+\"Field\")).chosen({search_contains:true});\r\n        }\r\n        \/\/ and on Exam Name\r\n        $SP().formfields(\"Exam Name\").elem().chosen({search_contains:true});\r\n      }\r\n    })\r\n  }\r\n  \r\n  \/**\r\n   * Fix the broken lookup fields\r\n   * @param  {String} field Name of the field to fix\r\n   * @return {Deferred}\r\n   *\/  \r\n  function fixLookup(field) {\r\n    var deferred = jQuery.Deferred();\r\n    var saved, choices=[], config;\r\n\r\n    \/\/ we check if there is a Throttled\r\n    if (WPQ2FormCtx.ListSchema[field].Throttled) {\r\n      WPQ2FormCtx.ListSchema[field].Throttled=false;\r\n      \/\/ check if we have a localStorage, if yes it means we are after the reload\r\n      saved = localStorage.getItem(\"RequestsLookup\");\r\n      if (saved !== null) {\r\n        saved = JSON.parse(saved);\r\n        \/\/ we use the stored data to create our dropdown\r\n        WPQ2FormCtx.ListSchema[field].ChoiceCount=saved[field].length;\r\n        WPQ2FormCtx.ListSchema[field].Choices=saved[field];\r\n        deferred.resolve();\r\n      } else {\r\n        WPQ2FormCtx.ListSchema[field].ChoiceCount=0;\r\n        WPQ2FormCtx.ListSchema[field].Choices=[];\r\n        \/\/ then we show a Waiting message\r\n        if (!modal) {\r\n          modal=true;\r\n          $SP().waitModalDialog(\"Loading some data...\");\r\n        }\r\n        \/\/ and we get data from the list\r\n        config = configurationsLookup[SPArrayIndexOf(lookupFieldsToFix, field)];\r\n        if (config.list) {\r\n          $SP().list(config.list).get({fields:config.field, paging:true}, function(data) {\r\n            var res=[];\r\n            for (var i=data.length; i--;) {\r\n              res.push({LookupId:data[i].getAttribute(\"ID\"), LookupValue:data[i].getAttribute(config.field)});\r\n            }\r\n            deferred.resolve({field:field, choices:res});\r\n          });\r\n        } else deferred.resolve([]);\r\n      }\r\n\r\n    } else deferred.resolve();\r\n    return deferred;\r\n  }\r\n\r\n  \/\/ do some actions as soon as the fields are shown\r\n  var changeForm = {\r\n    Templates: {\r\n      OnPreRender:function(ctx) {\r\n        \/\/ we want to show Voucher Code and User Name even if there are more than 5000 items in those lists\r\n        if (SPArrayIndexOf(lookupFieldsToFix, ctx.ListSchema.Field[0].Name) > -1) {\r\n          aDeferred.push(fixLookup(ctx.ListSchema.Field[0].Name));\r\n        }\r\n      },\r\n      OnPostRender:function(ctx) {\r\n        \/\/ only trigger when everything is loaded\r\n        if (ctx.ListSchema.Field[0].Name === \"Attachments\") {\r\n          allLoaded(ctx)\r\n        }\r\n      }\r\n    }\r\n  }\r\n  \/\/ don't do it when editing the page\r\n  if (GetUrlKeyValue(\"PageView\") !== \"Shared\") SPClientTemplates.TemplateManager.RegisterTemplateOverrides(changeForm);\r\n})();\r\n<\/pre>\n<p>Please refer to the <strong>\/!\\<\/strong> symbol to configure two variables: only <code>lookupFieldsToFix<\/code> and <code>configurationsLookup<\/code> need to be set by you.<br \/>\nNote that the above code is compatible for IE8+, and all modern browsers.<\/p>\n<p>Now go to the <strong>EditForm.aspx<\/strong> of your list. Then <strong>edit the page<\/strong>:<br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture1.png\" alt=\"showing how to edit the EditForm thru Settings\" width=\"1091\" height=\"313\" class=\"aligncenter size-full wp-image-1691\" srcset=\"https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture1.png 1091w, https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture1-300x86.png 300w, https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture1-768x220.png 768w, https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture1-1024x294.png 1024w\" sizes=\"auto, (max-width: 1091px) 100vw, 1091px\" \/><\/p>\n<p>Next, <strong>edit the webpart settings<\/strong>:<br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture2.png\" alt=\"show how to edit webpart settings\" width=\"840\" height=\"432\" class=\"aligncenter size-full wp-image-1690\" srcset=\"https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture2.png 840w, https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture2-300x154.png 300w, https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture2-768x395.png 768w\" sizes=\"auto, (max-width: 840px) 100vw, 840px\" \/><\/p>\n<p>Go to the <strong>Miscellaneous section<\/strong> and enter the path to your JavaScript file (created before) in the <strong>JS Link<\/strong> field \u2013 you can use <code>~site<\/code> in the path:<br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture3.png\" alt=\"show where to input the path to the JS file\" width=\"346\" height=\"385\" class=\"aligncenter size-full wp-image-1692\" srcset=\"https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture3.png 346w, https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture3-270x300.png 270w\" sizes=\"auto, (max-width: 346px) 100vw, 346px\" \/><\/p>\n<p>Finally click ON, and then on <strong>Stop Editing<\/strong> in the ribbon.<\/p>\n<p>You can now try to edit an item and the lookup fields should be fixed. Example:<br \/>\n<img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture4.png\" alt=\"\" width=\"869\" height=\"217\" class=\"aligncenter size-full wp-image-1694\" srcset=\"https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture4.png 869w, https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture4-300x75.png 300w, https:\/\/blog.kodono.info\/wordpress\/wp-content\/uploads\/2016\/08\/capture4-768x192.png 768w\" sizes=\"auto, (max-width: 869px) 100vw, 869px\" \/><\/p>\n<p><strong>NOTE<\/strong><\/p>\n<p>The <code>document.write()<\/code> is not really a good practice because it will delay the page load. It would be better to load asyn the JS using <a href=\"https:\/\/gist.github.com\/Aymkdn\/98acfbb46fbe7c1f00cdd3c753520ea8\">this tiny function<\/a>. In that case you need to make sure to have only Vanilla JS in your <code>changeForm<\/code>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>EDIT 22\/Dec\/2016: there is s a new version of this article at https:\/\/blog.kodono.info\/wordpress\/2016\/12\/22\/2-bypass-a-lookup-field-not-displayed-because-of-threshold-on-newform-and-editform-sharepoint-2013\/ Sharepoint is really annoying to limit the access to the list with more than 5,000 items&#8230; It causes many troubles, and one of them is when you use a lookup field into a form that is tied to a big list. Sharepoint [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","hide_page_title":"","footnotes":""},"categories":[170,13,33],"tags":[123,152,117,166],"class_list":["post-1687","post","type-post","status-publish","format-standard","hentry","category-english","category-niveau-intermediaire","category-programmation","tag-english","tag-niveau-intermediaire","tag-sharepoint","tag-sharepoint-2013"],"_links":{"self":[{"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/posts\/1687","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/comments?post=1687"}],"version-history":[{"count":8,"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/posts\/1687\/revisions"}],"predecessor-version":[{"id":1747,"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/posts\/1687\/revisions\/1747"}],"wp:attachment":[{"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/media?parent=1687"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/categories?post=1687"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.kodono.info\/wordpress\/wp-json\/wp\/v2\/tags?post=1687"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}