Toni on Tech

Getting Users to Appreciate Lookup Columns in SharePoint – Part I

—

by

Toni Frankola
in , ,

A software engineer or a technical person can easily understand the benefits of lookup columns (in SharePoint or any other system). However, not every SharePoint user is familiar with the concept of foreign keys and the advantages they provide. In this and next two posts, I will present a couple of use cases related to lookup columns and stress their potential benefits. I hope these will be typical enough to help users map them to their own real-life problems.

Use case 1: Simple lookup list (codebook)

For this example I’ve created a list “Payment Methods” and added some items to it:

BP2_Payment Methods - All Items - Internet Explorer 2013-12-22 22.37.39

This list contains just the names of possible payment methods. It will be used with invoices: when new invoice for a client is issued, a payment method needs to be defined.

So, in another list called “Invoices” we can reference “Payment Method” list by creating a column with these settings:

  • Column name: Payment Method
  • The type of information in this column is: Lookup (information already on this site)

BP2_Create Column - Internet Explorer 2013-12-27 21.57.31

  • Require that this column contains information: Yes
  • Get information from: Select list “Payment Methods” from drop-down
  • In this column: Method name (this means that the name of the payment method will be displayed in the “Invoices” list.)

BP2_Create Column - Internet Explorer 2013-12-27 21.58.30

In case of this lookup column, you can leave the default values for all the other settings. This is the simplest case of lookup column, and we will examine other settings in use cases 2 and 3.

Let’s see is what this looks like on the Invoice form (when user is adding a new invoice):

BP2_Invoices - New Item - Internet Explorer 2013-12-27 22.14.20

Now, this use case looks pretty static and the data field could easily be set up as a choice column in list “Invoice”. However, even for this case, there are a couple of benefits:

  • If any of the payment method names changes even slightly, we will not be able to group them easily with the data collected prior to this change, e.g. for reporting purposes.
  • System will not allow deleting of the payment method which was used at least once
  • If any of the payment method names is changed, it will immediately show up as such on all the existing invoices.