Lookup field to a list with 65000 items RRS feed

  • Question

  • Hello,

    A list <A> will have a lookup field to a list <B> with 65,000 items. I also want to use OOTB functionality to insert/update the items in list <A>. The list <B> should grow about 200 items/year.  What should be the best approach?

    My approach is to add a web part to the insert/edit form that would filter the items from B and somehow "inject" the selected value to the appropriate field.

    Another approach is to use external content type and store the values to an SQL server - list B should use external content type. The web part should read the values from SQL server and the user will add/update values using OOTB Sharepoint functionality

    Thank you


    • Edited by XristosK Tuesday, February 17, 2015 5:41 PM
    Tuesday, February 17, 2015 5:35 PM


  • The interface is going to be the weakest part of this without question. I've worked on a list where (no one thought this was a good idea but that's another story) the threshold was eliminated and they had a lookup to a list approximately that large. The entire edit form was broken, whilst the server could handle the throughput (with a low user base) the client side couldn't handle getting that much data with the standard interfaces and either performed awfully or crashed.

    You're going to need to do some customisation, probably cascading drop downs where selecting a value queries the system for options for the next drop down.

    I'm not sure if it's engineered to handle this but there's the SPServices project which does cascading drop downs. Either as a library or a starting point that's worth a look.

    • Marked as answer by Eric Tao Sunday, March 8, 2015 2:28 PM
    Tuesday, February 17, 2015 5:52 PM