CEDANIEL.COM

The Post applies to WSS 3.0 and MOSS 2007.

The fundamental building block of data in SharePoint is the List. Once you begin to develop an application in SharePoint, you will quickly begin to realize that almost all objects at some point are built off of or touch a SharePoint list. Although its data types are limited, it can be engineered to work somewhat like a table within SharePoint. The SharePoint List has its limitations, however I think it is important to explain how it can be used “similar” to a SQL table. Let’s examine a common task that you would typically perform with a SQL Table vs. SharePoint List.

How about a JOIN?

Hmmm.. Well, in a SQL Table you can easily establish a Foreign key relationship with one table against another so that you can query across the two of them. But what about a SharePoint List? Lets take a look at this.

Scenario: You have two SharePoint lists and both of them have a similar unique field in common. You want to be able to update data in one list based upon a match from another.

Solution: This can easily be solved by Building a Custom Workflow in SharePoint Designer. In SharePoint Designer, create a new workflow on List “A” that starts whenever an item is changed. In the workflow choose an Activity called “Update List Item”. In the “Update List Item” dialog choose the List “B” from the drop-down that you want to update data. Now choose a field that you want to update some information on when the item is updated. Finally, notice the “Find the List Item” section below. This is where you will do the join on the common unique field between the two lists. In the Field section – choose the common unique field from List “A”. In the Value section – choose the common unique field from List “B”. Now click “OK”. You will probably get a warning stating that there is a risk that the fields are not unique. So you want to make sure that the fields are absolutely unique. Just agree to the warning. Now install (Finish) the workflow. You when you change an item in List “A”, you will see another field update in List “B” where the common unique match is met between the lists.

Although this works, I’m not entirely sure how it will begin to perform when the list grows to a high volume of data. When performing updates on a JOIN using SQL tables, you would typically implement optimization strategies using various index techniques. I’m not entirely sure if SharePoint considers optimization when using this technique.