There are plenty of good articles and examples. I found this set of tutorials particularly useful:
The built in lookups within SharePoint are pretty much dysfunctional, especially on large lists. In the past if you want a custom third solution you would buy or write custom Field Controls, such as Kwizcom’s Cascading Lookup:
The problem with solutions like this is that they are “server side”. That is you actually need to install a DLL on the SharePoint server. So they can’t be used in hosted scenarios such as Office 365 (SharePoint Online).
Also Kwizcom’s solution is pretty much unusable on large lists and their support in such circumstances will most likely leave you frustrated.
So, what I am going to do here is outline a solution that will give you an awesome lookup UI in SharePoint that will:
• Work on large lists (>1,000,000 entries)
• Work on premises as well as on hosted SharePoint
First we need some large lists to work with. You can download a list of the worlds cities here:
Here is a copy of it in Excel Format:
So the first step is to import the lists from this spreadsheet into SharePoint…
You do this by “Adding the Import Spreadsheet app”
We are going to start by importing the Continents table from the spreadsheet listed above:
When you click Import, Excel should appear (if you have it installed). You should see three “tables” that are available to import. Start with the Continents table:
Click Import and you should have a list that looks like this:
Repeat the process for the Countries table:
There is also a Cities table in this file. This one will take some time as there are over 1.4 million entries in it! But don’t worry about it for now. I will be dealing with lookups on large lists such as this one in Part 2 of this tutorial.
So now we are ready to create a new list, where we implement the cascading lookups. We are going to run into a few problems on the way, which I will address as we get to them.
So lets go ahead and create a list just using out-of-the-box functionality and see what happens:
So we start by adding a Custom List app:
Lets call it Customers.
Then click the Customers icon to configure:
Click the List tab
Then click “Create Column”
Configure as shown:
When finished, click “Create Column” again to add a Country lookup as shown. This time we will make allow multiple values:
Now create a new item and view the result:
A couple of problems here: There is no way to filter the Country options based on the Continent choice. Also the Multiselect UI leaves a lot to be desired.
You could use something link SPServices to get cascading lookups, but I’m not sure if you can use other JQuery plugins, such as Chosen with it. At any rate you could certainly combine it with the JSLink approach listed below.
With JSLink, we can override the templates used by any form element in SharePoint. For more information see this set of tutorials that I mentioned previously. The JSLink that I am going to use looks like this.
We need to upload this JS file to a document library within SharePoint, usually the Master Page gallery. But just to be different I am going to upload it to the Style Library:
Now the next step is to link or NewForm and EditForm to this JSLink file.
So go to the Customers list and click New Item:
Then click the Cog at the top right and choose “Edit Page”
Then select the Customers Web Part and choose Edit Web Part
And set the JSLink property to point to the location of your uploaded JSLInk file:
Click OK and Stop Editing the page.
If the moons are aligned, your page should now look like this…Notice how nice the multiselect UX is now! Filtering built in even!
Lets go ahead and select a few items and click Save:
And the result should look like this:
Now you go to edit this item you will still see the default form:
But if you edit this page and set the JSLink of the Web Part as before to the same file, it should come alive:
Use this technique to bring your SharePoint forms to life! But for those interested in dealing with the issues of forms with lookups to large lists, there’s more! In Part 2 I will create a new City field that is also multiselect. There are 1.4 Million cities in the excel file mentioned above, so this will be a real challenge.