Are Your Lookup Lists Involved in a Relationship?

SharePoint 2010 adds the ability to define relationships between two SharePoint lists in which one list serves as a lookup to the other list. This capability existed in prior versions of SharePoint. However the lack of enforced relationships allowed users to modify the contents of the lookup list without considering whether individual lookup values were used by the referencing table. SharePoint 2010 corrects this missing feature by allowing the user to either restrict or cascade updates to the referencing list.

What does that mean? Suppose I have a list of Projects that my organization is working on. I want to use this project list as a lookup column source for a project field in a second list, such as a Lessons Learned list. In the Lessons Learned list, I define a column named Projects using the lookup column type. In the Additional Column Settings section, I edit the Get Information From field to point to Projects and use the Project Name column as the source for the In This Column field. This definition establishes the link as shown in the figure below.

Note that once I link the Project list to another list, I can also bring along any of the other fields in that list such as the Project Manager field to be displayed with the data in the Lessons Learned list. The emphasis on displayed comes from the fact that while the Lessons Learned list displays the project manager’s name associated with the selected project, I cannot change that name in the referencing list. I can only change the Project Manager’s name in the Projects list. Furthermore, the column name associated with the project manager’s name will be the concatenation of the list and column name of the lookup, Referenced Projects:Project Name in this case.

If I do not do anything else when defining the lookup, it would be possible for someone to come into the Projects list and change the name of a project that is already linked to one or more lessons learned or even to delete the project from the Projects list. Anyone familiar with referential integrity would refer to this action as creating an orphaned reference in the lessons learned table since the referenced project would no long exist.

In SharePoint 2010, a Relationship section was added to allow the list designer to enforce relationship behavior. The designer has three possible options. First, I can choose to ignore the enforcement of any relationship. Sometimes, this is the correct solution especially if I do not want to change the historical value of the lookup over time that could change the lookup values. Another possibility is to use the Restrict Delete option. This option prevents changes to the values in the lookup list that have already been used at least once in the referencing list. In other words, it would prevent changing the project name if that project had at least one lessons learned record. Finally, the third option is Cascade Delete. With this option, if I change the project name in the lookup list, SharePoint cascades that change to all lessons learned records automatically. Similarly, if I delete a project from the lookup list, it deletes all lessons learned records for that project.

With either relationship enforcement options, I must index the column before the referential connection can be created. This is similar to the behavior of referential fields in a database which require the linking fields to be indexed. The difference is that only the linking field in the referencing list must be indexed within SharePoint. If that field does not contain an index, SharePoint prompts for one using the following dialog.

Clicking OK builds the index and creates the relationship. I can review the relationships defined for a list by selecting List Settings from the List ribbon and scrolling to the last link in the Columns section to display the Indexed columns.

SharePoint then tells me the number of indices the current list has and the fields associated with each one.

In a similar fashion, I could add referential lookup columns to the Lessons Learned table for up to 20 different fields in the table. Let’s suppose I have three referential columns and therefore, three indexed columns as shown here.

Note that in order to review the type of referential link each field uses, I must return to the individual lookup table and review the properties of the lookup column. If I change the referential enforcement option, that change applies only to new or edited records going forward, not existing records that are not edited.

For the purposed of this example, I defined the Project lookup to be a Cascade Delete and the Status and Lesson Type lookups to be a Restrict Delete.

Let’s now add our first Lesson Learned as shown in the following figure.

For the purposes of this example, suppose I add a few more records to the Lessons Learned list so that the first few records look like the following figure.

Now suppose that I go into the Lesson Type list and decide that I want to delete the Coding option from the list. However, I already used the Coding lesson type in at least one Lesson Learned item (see above).

Upon clicking Delete Item from the options dropdown menu, I get a message first asking me if I am really sure that I want to do this. (Personally, I think SharePoint should check the referential option first and if Restrict Delete is set as it is here, to skip over this question.)

After confirming that I do want to delete this item from the lookup table, SharePoint tells me that it cannot delete the item because it is used in the Lessons Learned list.

(Note: Although the option Restrict Delete is in effect, changes to the lookup value will always cascade down to the referencing list.)

Next, let’s look at the Project lookup list. In the Lessons Learned list, I defined this relationship as a Cascade Delete. Next I go into the Project list and delete Project 1 at this point as shown in the following figure.

I would first get a warning message such as the one shown below that tells me that any related items in the Lessons Learned list will also be sent to the recycle bin and would ask me if I was really sure I wanted to do this.

If I click the OK button, not only does the project get deleted from the Project list, but SharePoint also deletes all of the lessons learned related to that project as you can see from this last figure of the Lessons Learned list which no longer has any lessons from Project 1.

That’s all for now.

C’ya next time.

Truth in Advertising

I stopped at a local gas station the other day.  It is one of those that print coupons on the back side of the gasoline receipt.  I’m not going to tell you who it was, but I will say that it is a major chain that has been pushing hard in the Orlando area for the past year or more.  Anyway, after filling up my tank with gasoline, I finished the transaction and asked for a receipt.  On the back of the receipt they have pre-printed coupons usually for food items in the store.  Sometimes, I will immediately go into the store to use one or more of the coupons so of course I turned over the receipt to look at what I could get.  On the back were three items that I would consider getting since it was early evening and I was looking for a snack anyway.  But when I looked at the coupons, I saw that the expiration date had already passed.


I just got the gasoline and printed the receipt.  How could the coupons have already expired?  Sure enough, the expiration date was December 31, 2013 and the current date was January 17, 2014, both clearly printed on either side of the receipt.  Of course this had to be a mistake so I went into the store anyway to see if they would still honor the coupons since they had clearly just be printed.  However, when I finally got the clerks attention away from the phone game she was playing, ‘Candy Crush’ I believe is the name, she said that she was not allowed to take expired coupons and had indeed been reprimanded previously for taking a coupon that had expired.

I tried in vain to explain that I had just purchased gasoline and this receipt was just spit out from the pump.  She said that it does not matter.  Rules are rules.  Ok, I get that, but why was the paper in the pump filled with expired coupons?  What does that say about how they really feel about their customers.  Afterall, this was more than two weeks after the expiration date.  I do kind of get that they did not want to throw out perfectly good receipt paper (except for the fact that the coupons had all expired).  But would they ever catch up to the point where the coupons were not only still valid, but had a few days before they would no long be good?

Sometimes it is the little things like this that cause people to like or dislike stores or vendors.  It is not just the big mistakes, but the lack of attention to details that make customers feel unwelcome and unappreciated.  After all, if it wasn’t these coupons, it would have been other coupons.  Isn’t the purpose of the coupons to get customers to first buy their gasoline at their store and then hopefully come into the store to buy other things besides the items on the coupons?  Good marketing and good customer service might say that it would be best to just honor the coupons regardless of the date and either replace the paper used by the pumps with new paper or just continue to honor the old coupons until the paper was used.

This weekend I celebrated a birthday.  Maybe I should not say celebrated as much as I observed the passing of another year.  I realize that I have not posted as often as usual because this past year has been quite a roller coaster ride and will try to get back on track with regular blog entries over the next couple of weeks, especially now that I have an external keypad for my iPad.

However, I would like to remind all of my local readers that the Tampa SQL Saturday is coming up later this month, February 22nd to be exact.  I hope to see at least some of you there.  The tracks should cover most areas of SQL Server.  However, it should be no surprise that I will be involved in the Business Intelligence tracks again.  For more information, go to: and check for the Tampa event in the upcoming events list on the home page.  Hope to see you there.

C’ya next time.

Calculated Columns

This week I’m returning to SharePoint to look at a lists, specifically calculated columns within lists. Calculated columns allow you to combine two or more columns into a new third column. You can combine columns with different data types as long as either you or Sharepoint can convert all of the values to a common type. That common data type is typically a string, but not always. You can even use string functions to pull out a portion of the text in a single column.

Let’s begin by looking at a simple concatenation. Suppose you have a list with the first and last names of your staff in separate columns. However, when you display the data, you would like to display their full name. You don’t want to force the user entering the data to enter the name a second time after they already entered the person’s first and last names into separate fields. Therefore, you decide that you can easily concatenate these two values into a single name column using the Calculated Column data type in a SharePoint List.

Assume that we have a list representing project data and in that list, we already have the project manager’s first and last names stored as separate fields. To create a calculated column, open the list and select List Settings from the List ribbon. In the Columns section, click on Create Column. In the resulting dialog, you begin defining the calculated column by assigning it a name and data type. As shown in the figure below, I’ve selected the name: Project Mgr Name and chosen Calculated as the data type.

As with any other column definition, after selecting the column data type, the second half of the screen changes to accommodate the properties most needed for the selected data type. In the case of a calculated column, that means you must define a formula. You can simply type the equation you want to use to calculate the column in the Formula box. However, you can get some help for the names of available columns from the Insert Column list box to the immediate right of the Formula box.

In the following figure, you can see that I’ve entered a description for my Full Name field showing that I want to format the name as First Name, space, Last name. Notice that names of other fields in the list must be enclosed in square brackets. This will happen automatically when you double click the field name in the Insert Column list to add it to the current cursor position in the Formula box.

It is important to add the space between the first and last name. Otherwise, the equation will attempt to combine the names without a space in the new database. Also note that literal text strings must be enclosed in double quotes, not single quotes. You can also see that I’m specifying the output of the calculation to be a single line of text.

If I were to attempt to save this formula, I would get an error because the ‘+’ operator is used only to add two numeric values together.

To concatenate strings, you must use the ‘&’ operator. In fact, even if the values on either side of the ‘&’ operator are numeric, they will be treated as strings and concatenated in the resulting expression value.

As the figure below shows, my calculated formula for Project Mgr Name correctly concatenates the values in the Last Name and First Name fields.

Let’s now try something a little different. Suppose we want to calculate the expected project completion date given the project start date and the project length. The following figure shows these new data fields added to the project record.

For the Project End Date, we can create a calculated column that begins with the project start date column and adds the project length in weeks to it. Since SharePoint stores dates as a numeric value with the integer portion of the value representing the number of days since a predefined start date, we must add the project length in days, not weeks. Therefore, we can take the Project Length column and multiply it by 7 to get the number of days the project should last. Note that we multiple by 7 and not 5 which is the number of workdays because we are looking at elapse time, not actual number of days expended. In other words, there may only be 35 work days in 7 weeks, but there are 49 elapse days because of weekends. Note that this simplistic approach does not account for holidays, workload availability or any other factors that could influence the true elapse time to complete the project.

Behind the scenes, SharePoint converts the date to its numeric equivalent and then adds the number of days represented by the project length multiplied by 7. The resulting number however would not mean much to us. Therefore, we ask SharePoint to return the value from the formula displayed as a date without the time.

After creating this calculated column, we can display the list again to see that the project which started on January 20, 2014 and that is scheduled to last 5 weeks should end on February 24, 2014.

Suppose next that we want to have columns in the project that define the project rank and the cost. However, we want to display the data as a concatenated field of these two values. For our purposes, suppose that both columns are numeric data types as shown in the figure below.

Initially, we might think that we could simply add the two numeric values together as shown below and then tell SharePoint to display the result as a string.

Doing this however will add the Project Rank of 2 to the Project cost of 12.56 million to get 14.56. Note that the plus sign operator performs a mathematical add of the two values, not a concatenate as shown in the next figure.

If we simply switch the plus sign to an ampersand, we should be able to treat the two numeric values as strings and concatenate the two column values as shown in the next figure.

While this formula technically did concatenate the two values, the resulting output of 212.56 can be confusing. Does this project have a rank of 2 and a cost of 12.56 or a rank of 21 and a cost of 2.56?

A slight modification of the formula for the calculated column allows us to insert a space, hyphen, and a space between the two column values as shown below.

As the next figure shows, the output of this calculated column is now more readable.

Finally, let’s suppose that we want to know how many days we are currently into each of the projects in the list. We might assume that we could simply calculate the difference in days between the start date of the project and today. In fact, we might remember that SharePoint has a built-in function [TODAY] to return the current date. Therefore we might try creating the following calculated column.

If we attempt to save this formula however, we will get an error informing us that we cannot use a volatile function like [TODAY] or [ME] in a calculated column as shown in the following error message.

Sometimes, you can find another way around these types of problems. For example, you may remember that many programming languages have datetime functions like TODAY() and NOW(). As it turns out, both of these work. However, NOW() includes the fractional portion of the current day. In other words, 6 AM would be .25, noon would be .5 and 6 PM would be .75. Rather, using TODAY() returns an integer for today. Therefore, if we subtract the project start date from the integer return from the function TODAY(), we will get the number of days between the two dates. (Strictly speaking, if you want to could both the start date and today as separate days, you will also need to add ‘1’ to the result).

The following image shows that the current project is only 4 days old (Today is January 24, 2014).

That’s all for now.

C’ya next time.

Monster Trucks and Turn Signals

Today I came out of work and walked to my car in the parking garage to find a monster truck parked next to me where there was a small passenger vehicle before.  You see, I generally look for a spot in the garage that will not block my view of cars coming down the lane while I pull out.  Several years ago my daughter was pulling out of a parking spot in which her view was blocked by a monster truck and another person speeding through the parking lot tried to go around her but got clipped on the side of their car by the back of my daughter’s car.  In the ‘infinite wisdom’ of the local police, they blamed my daughter for hitting the other car while she was pulling out.  The problem is that from the inside of many cars, it is not possible to see if a car is coming down the lane when a monster truck or van without windows is totally blocking the view.  In order to see, you have to pull out a little, but even pulling out of the spot slowly is no guarantee to that impatient driver who tries to get around you instead of stopping to let you out.

In my opinion, I believe that the person coming down the lane should be responsible instead because they have the best view of any cars that might be attempting to back out of a spot.  Even if you try to park next to a smaller vehicle or at least one that has windows that you can see through (remind me about the dangers of dark tint on glass),  that car or truck can leave and someone else can take the spot before you leave.  And if that someone else is a monster truck with a truck bed that has sides higher than the roof of your car or is a van with no windows or is any vehicle with dark window tinting, your ability to back out safely is reduced if not eliminated.

But then no one every said that laws were meant to be fair.  Laws are created only so that people do not have to think logically about a situation.  They attempt to define the black and white of issue that really are shades of grey.  Another example is the use of turn signals.

I guess I never realized that turn signals were not really meant to tell others about your intention to turn when it was safe.  Rather it appears that turn signals are meant to give you warning that the other driver is about to cut in front of you and you had better have one foot already moving to the break to allow them in.  This often occurs when long lines occur at intersections.  Rather than wait their turn in the line to make their turn, these drivers run up the adjacent lane until they get to the intersection, turn on their signal, and then cut in front of you to make the turn at the last minute.  I suppose the turn signal is really meant to tell you stop because their need to get to their destination is far more important than your need to get home, to work, or anywhere else.  Furthermore, if you do not have your foot on the break and actually hit them as they cut in front of you, the police will rule that the accident was your fault because you hit them with the front of your car and you apparently did not have control of your vehicle.  Never mind the fact that they cut you off the last minute.  The law divides black and white so no thought needs to occur and blame can be assessed without any real analysis of what really happened.

Finally there are those drivers who use exit and entrance lanes for turn offs of major highways as a passing lane to get around other traffic and at the last minute turn on the turn signal to cut back into a normal traffic lane because they really were not getting off at the exit, they just wanted to get a few cars ahead of where they were.

Now not all drivers are this crazy, but it only takes a few to raise the stress level of your drive to work or your drive home.  Perhaps these are the same people who wait until just before lunch or just before it is time to go home to come to your office and tell you that they need you to work on a project that is an emergency and needs to be done before 1 PM or perhaps before the next morning.  The fact that they may have known about the tasks for several hours but were “too busy” to come to you earlier never crossed their mind.  Perhaps your boss only comes by your desk with a new urgent task 5 minutes before you are about to go home.  Did they really not know about the task before that?  Did they just wait until 5 minutes before the end of the day just to see if you left your desk early?  Or do they just get a powergasm of controlling your life not only at work, but your home life as well?  Is this the same mindset as the driver that cuts you off at the last minute because their time is more important than yours?

Yes there will be emergencies and some times events like these are the result of  those emergencies.  However, the frequency in which these types of things occur begs the question whether these people are just under the stress of the moment or if they are just so ego-centric that nothing else and no one else matters.  If you know someone like that can you talk to them about it?  Or will they resent you for presuming that you know how they should live their lives.  The net result though is that it seems like the level of inconsiderate behavior that has a direct affect on others is rising.  Maybe we should all think about that next time before we cut around someone just to gain a few car-lengths.

C’ya next time