We are working to recreate a time tracking system that we moved into SharePoint earlier this year. The SharePoint version solved several problems, but as is often the case with new ventures, it also surfaced a few new issues. I am going to parse our lessons out over a short series of posts; today, I am going to revisit an old enemy – Lookup Columns.

With SharePoint in its third major release, the fact that the out-of-the-box incarnation does not support banging a Lookup Column against a filtered view really makes me scratch my head. Actually, when I think of all the things I can do, the fact that I can’t do this kinda makes my head hurt. Before you send me to all the blogs that explain how to do this, all the things I can configure in SharePoint Designer and all the products I can buy, STOP! I have been to the blogs, I know how to do this in SD and I own at least one of those products. It still bothers me that this isn’t O-o-B behavior, because if it was, more of my users would build SharePoint solutions on their own. This is not a feature that should require heavy lifting!

The list at the center of this effort is a list of projects that our attorneys charge their time to. The full list of projects is large and too unwieldy to plop into a drop-down. They want to drive the lookup from a constantly changing list of “Active Projects”. In the opposite corner are the accountants who want to see any project that has had time charged to it in this fiscal year. SharePoint’s new ‘Restrict Delete’ feature doesn’t help me because the lawyers want to delete items from the list driving the Lookup Column and the accountants say “no!”

The simple approach is a Yes/No column in the list called ‘Active’ – would that it were that simple. I found several solutions that allow me to put a filtered drop-down list on the New and Edit forms using SharePoint Designer. I was planning to use that approach until I discovered two things. One, judging by the comments on those blogs, these solutions all had some sort of problem. Two, one of our attorneys works out of the Data Sheet view and never uses those forms. In addition to those problems and other potential problems, I realized that the attorneys might want to reference Active Projects elsewhere; I needed a much simpler solution.

We opted to go old school on this. We created a second list called Active Projects. When projects are added to or edited in the master list, a workflow updates the Active list accordingly. To appease the accountants…ok, that’s not a fair way to start this sentence. To meet the accounting requirements, when a time entry is made, another workflow copies the relevant data out of the Active list and stores it in Text columns where it will remain forever. This approach seems to make everyone happy, and it has several benefits:

  • It works – Attorneys only have to pick from active projects but accounting never loses any details.
  • It scales – The attorneys can build more solutions off that same Active Project list and they can do that without IT’s help.
  • It works everywhere – The Active Project lookup works in the Data Sheet view.
  • It is more useful – All of the solutions that created a filtered lookup list left us unable to fully sort or filter the resulting Time Entry list on that column. Having the detail information in Text columns allows full sort and filter capabilities in the resulting list.

I have to admit, I was pretty proud of the option using a Filtered Lookup drop-down list in the New and Edit forms. Those forms would solve the problem in an elegant and technically interesting manner. The only problem was the fact that the user didn’t want to use the forms. I also like some of the more elaborate options that provide a Filtered Lookup Column, but they result in a less useful end product. Again, the people using the list that we are trying to build want to be able to sort and filter that list. I’ve always said my job would be easier without users, but I guess that is ultimately self-defeating. I learned a lot working though the early stages of this small project. I’ll be sharing some of those other lessons next week.