Best Practices for Building SharePoint Solutions

A friend forwarded this link to me: 10 Best Practices For Building SharePoint Solutions.  Most are fairly obvious, but sometimes it's good to state the obvious things, lest we forget them.

Performing Outer Joins on a SharePoint List from Access

I'm currently creating reports from the custom SharePoint lists I have been working on over the past few months.  The way I'm accessing the data in my SharePoint lists is through Access 2007, and then I have an Excel 2007 spreadsheet which uses the Access database to get the data. In this manner, I can use the power of Access' database engine to perform queries against my lists, and then just import the data to Excel.  It sounds cumbersome, and perhaps I'm showing some of my ignorance of Access and Excel development, but it runs much faster than the earlier version of the report which uses VBA only to generate similar reports.

That being said, I ran into an issue with one query.  I have a list which tracks FCC filings.  There can be a major filing and a minor filing, both tied to the same physical location.  So there can be one or two filings (items) per location stored in this list.  I needed to report on whether the filings were "complete".  They are considered complete if the major filing is complete, or if the minor filing is complete in the case where a location does not have a major filing.

My SQL statement was structured like this:

SELECT * FROM
(
    SELECT <<completed major filings>>
    FROM Filings
    WHERE Filings.Type = 'Major'
UNION
    SELECT <<completed minor filings>>
    FROM Filings Minor
    LEFT JOIN Filings Major ON Minor.Location = Major.Location
    WHERE Minor.Type = 'Minor'
    AND Major.Type = 'Major'
)

However, this didn't work.  The second part of the query kept behaving like an inner join.  I did some proofs-of-concept to discover that, yes, outer joins do work in Access on actual tables within Access, but they just weren't working on linked tables where the source is SharePoint.

What to do?

After beating my head against the wall for a while, and possibly pulling some hair out, I finally had the bright idea to break things into smaller chunks.  Thinking that Access had a problem joining a linked table with itself, I created an Access query which only returned items representing minor filings, and another Access query which only returned items representing major filings.  Then I removed the where clauses from my original SQL statement above and replaced direct access to the linked table with the queries I had created.

It worked beautifully!

The new SQL Statement looks like this:

SELECT * FROM
(
    SELECT <<completed major filings>>
    FROM AllMajorFilings
UNION
    SELECT <<completed minor filings>>
    FROM AllMinorFilings Minor
    LEFT JOIN AllMajorFilings Major ON Minor.Location = Major.Location
)

Lesson learned: when working with Access, break things into smaller components to avoid confusing Access.

I can't wait to get back to "normal" C# development!

Slow Going

As you know, it's the end of the year.  Holidays are only days away.  So is vacation time.  I'm frantically finishing up 2009 work so that I can take some time off and return fresh in the new year.

So here's to a good start in 2009 on this blog.  I'm aiming to make 2010 even better.  Perhaps I'll even get a new URL to boot.

I have lots of things planned, just need to implement the plan now.  I'll be posting my learning experiences here.

As always, I look forward to your feedback.

Strange Column Names

In SharePoint 2007, you can have a column named "!".

I'm sure there are other characters you can use, as well, but this one surprised me. I suppose it is because I'm a C# developer…the "!" (or "bang") is an operator, not an identifier.

But in SharePoint (at least the 2007 version) it is a valid column name.

[Update: It appears that the column named "!" appears in SharePoint Designer as "_". I haven't tried including this column in any of my workflows, so I'm not sure how it would behave. If you have the opportunity to do so, please leave a note letting me know how it works for you. -sj]

SharePoint “Lookup” Columns

As you may know, you can include a “Lookup” column in your SharePoint 2007 lists which, in effect, creates a reference to another SharePoint list.  This can be used, for example, if you have an address list and want to ensure that your states or provinces are all spelled correctly—just create a “StatesProvinces” list, fill it with the properly-named states and provinces, then add a reference to it in your address list. The user experience is a combo box containing the items in the “StatesProvinces” list, and they can select the one they want without having to type it themselves. [Note that you can configure SharePoint to allow multiple items from the referenced list to be selected, but that’s not what I’m focusing on in this post.]

In order to add a lookup column to your list, you first must have created the other list that you are going to reference.  You don’t actually need data in it, yet, but it does need to be created.  Fair enough.

Next, after choosing the list, you must choose which column in that other list you want to display to the user.  So in this example, you may want to have the “Title” column display to the user.

Once you save everything and add data to the “StatesProvinces” list, you have a workable solution.

But what if you want to have a workflow run against this address list which has the reference to the “StatesProvinces” list?  This is where you need to know a little bit about the inner workings of lookup columns in SharePoint.

If you know anything about database development, you probably know about the concept of a “foreign key”. In short, a foreign key resides in a table that is related to another table.  Let’s call the first table the “Child” table, and the second table the “Parent” table.  So the Child table would hold the foreign key to the Parent table.

What does a foreign key look like? Simple: it is the primary key of the Parent. [A primary key uniquely identifies a row in the database table.] It is usually a numeric type.

The same concept applies to a lookup column in SharePoint, but the execution is slightly different. You see, the actual value stored in the lookup column is the ID of the referenced list, but what is displayed to the user is the configured column from that list.

Back to my workflow scenario: if you want to manipulate lookup columns from a SharePoint workflow, and I’m talking specifically about using SharePoint Designer to create the workflows, you need to keep the foreign key concept in mind. As a test of this, do the following:

1. Create a workflow and attach it to a SharePoint list which contains a lookup column.
2. Create two variables in the workflow, one of type “String” and one of type “List Item ID”.
3. Set both variables to the value of the lookup column

If you examine the contents of each variable (by logging it to the workflow history) you will see that the String variable contains the value of the configured column to display (in our example, the “Title” column), and the List Item ID variable would have a number (which corresponds to the ID column of the referenced list).

If you are going to change the value in the Lookup column via a workflow, you must set its List Item ID (number), not the displayed value(text).  You will wonder why your workflow isn’t working correctly if you don’t follow this advice, especially if you are new to creating SharePoint workflows using SharePoint Designer.

I hope I have helped someone out there with this advice.

Mentoring

I got my start in programming from typing lines of BASIC in the backs of magazines into my Commodore 64.  I didn’t really understand what each line did at first, but I dutifully typed in the lines, just to see the finished product in action.

I got my professional start in programming when I was a student at Georgia Tech.  When I enrolled at Tech, I indicated that I wanted to be part of the cooperative education plan, which meant that I would attend classes for one quarter, then work at a  company for one quarter, then repeat.  It took me a little over a year to find a company that would hire me as a “co-op student”, but once I did I stayed with that company until I graduated from college.  In fact, that company hired me when I graduated, and I went to work for them full-time.

But while I was still a co-op student, I often felt that I learned more while I was on the job rather than when I was in school.  I think the reason for this is that I was very fortunate to have found a job where there were many people eager to share their knowledge of programming with me.

In a nutshell, I was surrounded by mentors.

I have very fond memories of those days—people smarter than myself showing me how things got done in the “real world”.  As a result, I often take the time to teach others things that I have learned, now that I’ve been a professional developer for nearly 20 years.

I guess that’s where this blog is coming from.

In my current role as a consultant, my coworkers are all very knowledgeable about software development, so I don’t often get to mentor them.  But on the rare occasion that I get to explain something, or show something that I know, it really gives me a sense of satisfaction when I can see that I’ve made a difference—when the other person has that “a-ha” moment and has learned something.

I think we can all learn from each other, and I think that there is great value in continually learning new things.  My company doesn’t currently have a mentoring program, but I’m considering suggesting that they start one.

What about your company?  Do you have a mentoring program of sorts?  If so, what’s it like?  Can you quantify the value of it?  Is it company-supported?

I’d be willing to collaborate with you (Google Wave, anyone?) on a “Mentoring Charter” of sorts if you want to start a similar program at your company.

What Do You Want From Me, Anyway?

I started this blog a week or so ago, and I have filled the first few entries with mainly SharePoint-related information.  Mostly from my current project.

But I know lots more than just SharePoint.

I can write about patterns.  .NET, C# in particular.  Middle-tier development.  Consulting.  Project management.

But I don’t want to just spout things out without some feedback from you, my dear reader.  [And, yes, at this point I may be the only reader!]

Please leave me a comment to let me know what you’re interested in reading about.  I know that this blog will take shape over time, and will most likely track what I’ve been working on, but there’s nothing stopping me from deviating from that pattern and posting something that you’re actually interested in.

Like how I got laid off twice within one year and each time landed a new job within 1 1/2 weeks.

Or perhaps you’d like to know how to make money by blogging?  [Sorry, but I haven’t figured that one out, yet.]

In any case, do leave a comment, no matter when you are reading this.  Meaning, if the year is now 2012 and you’ve stumbled upon this post because you’re preparing for the world to end on December 21st, please leave me a message.  Note that I don’t think it will end, but the crustal displacement theory sounds plausible.

You won’t find what you’re looking for here unless you ask!  So ask away!