Ever want to use a lookup column in SharePoint to lookup data in a list, but that list resides in a different site collection or site than the one you're in? Well – out of box this isn't possible, but with the help of jQuery and an event handler, it certainly is possible!
The basic idea behind the solution is to use a text column instead of a lookup column, and as the user starts typing in the text column, a Web Service request will fire to the remote site via jQuery to retrieve a list of "Lookup Values" to present to the user in an Auto-Suggest box.
For example – say you have a site with a list in it that contains a list of Projects. Then, in another site that contains Project Documents, you want to associate a particular document with a Project ID from that other site.
All you have to do is start typing the project name in the ProjectID column for that document and the Auto-Suggest box will populate with suggestions based on your search term.
Then when you click an item in the Auto-Suggest box, it will put the Project ID into the Project ID column.
Pretty slick huh? There is one more important aspect of the solution however. As you can tell, a user could easily type in something that is not a valid project ID – so I also included an event handler that executes after the user hits OK. This event handler queries the lookup column list for the value that was typed, and if it is not found, it presents an error to the user that they didn't type a valid ID.
To Download my Visual Studio Project
http://philwicklund.com/Downloads/CrossSiteLookups.zip
Installation Instructions
Download project and open the LookupProjectID.js file under TEMPLATE\Layouts\Happenings. Edit the following fields to point to the SharePoint list you want to lookup data from:
var urlToWeb = "http://localhost:82";
var listName = "Project List";
var lookupValue = "Title";
In the same file, edit the callingColumnName to specify what text field in your edit form(s) is performing the lookup (this is how the Auto suggest box knows where to position itself):
var callingColumnName = "ProjectID";
Under the EventHandlers folder in the visual studio project, open the ConfirmLegitProjectID.cs event handler. Edit the following values to point to the SharePoint list you want to lookup data from:
public
override
void ItemUpdating(SPItemEventProperties properties)
{
// EDIT THIS!
string lookupListName = "Project List";
string lookupSiteURL = "http://localhost:82";
string lookupWebURL = "";
In the same file, edit the callingColumnName to specify what text field in your edit form(s) is performing the lookup:
string callingColumnName = "ProjectID";
Build the Visual Studio Project (this spits out a wsp solution file at c:\wsp). Add the solution to SharePoint (stsadm -o addsolution). Deploy the solution via central admin (or -o deploysolution). Activate the feature on the site(s) you want to lookup the data:
DONE!
Credits
I have to give credit to Timothy Groves for providing the jQuery Auto-Suggest box, as well as Jan Tielens for his write-up on jQuery and web service calls.
Cheers!
Phil