SharePoint Feature Description
In Microsoft SharePoint, to connect list items to an item in another list, you have to use Lookup columns. From the database perspective, lookup columns are like the database keys which join different tables together.
In our test SharePoint server implementation, There is a Lesson learned list to store every lesson we learned from projects. So when the user wants to add a lesson learned item, he has to select which project this lesson learned item is extracted from, by selecting the appropriate lookup field.
SharePoint Challenge Description
There is a business requirement in our test SharePoint server implementation to enter the lesson learned items after the project marked as completed. There is a Boolean field in project list to show whether the project is completed or not. So according to the management request, the users cannot add lesson learned while the project is running and we have to reflect it on test portal implementation.
When you use the standard lookup column of the SharePoint, the drop down list shows all the information in the parent list. For example in our test portal, when the user wants to enter information in the lesson learned list, the dropdown item shows the list of all projects which are present in the project list. But according to the management request, we have to filter out the dropdown list to just show the finished projects. There is no feature currently available in SharePoint to address this issue.
One suggested solution was to store completed project information in a separate list and have the lookup column point to this new list, this solution may address the business requirement but it was not approved since it introduces duplicate information in Portal.
The other suggested solution was to use Visual Studio to create a custom application page for this requirement. This solution will address our requirement but the problem with this approach was the user cannot implement the new business logic in this page and the page is only customizeable by a developer. And also if we require this type of functionality for other lists, we should create more and more custom application pages in Visual Studio.
We come up with an idea to develop a new custom field for Microsoft SharePoint to be able to show filtered data based on some rules. We developed a special lookup feature which can be filtered by a CAML query. CAML query is a standard way in Microsoft SharePoint to show specific data of a list. So by developing this custom field, it is now possible to have more advanced lookup fields in Microsoft SharePoint which can be used in any project.
CAML Lookup fields store the data like the standard SharePoint lookup field and when a developer wants to use SharePoint Object Model to access data, he can use the standard SPLookupField class in order to work with CAML Lookup field.