travislowdermilk.com

This year, I was tasked to be a leader in an organizational change movement at Kaweah Delta District Hospital.

I decided to use this opportunity to study and discover what brings about change (both at the organization level, as well as, the personal/team level).

I also wanted to learn more about what motivates employees to do great work and, conversely, what prevents them from putting forth their best efforts.

Below are some resources I’ve found quite remarkable (dare I say life-changing?).

Also, I think as application developers, we are often put in the position of “change promoter”.  I mean let’s face it, more often than not, we are brought in to build an application because a process or workflow is failing and needs to be changed.

These resources can be extremely valuable if you’ve ever felt yourself saying: “They just don’t get it.” or “These users are just lazy!” or “How can I get them excited about this new tool!?” or “How do I get my own team to care about my application!”

Switch: How to Change Things When Change is Hard (by Chip and Dan Heath):

Chip and Dan put together a framework on how to bring about change.  It focuses on 3 main components.

To help people change (behavior, software programs, habits, etc.), you must:

Direct the Rider – Appeal to an individual’s need to see what behaviors are necessary and the long-term positive effect it will have. (“If you are willing to spend 10 minutes a day, entering data into this program, we are going to collect invaluable information about our patients”.)

Motivate the Elephant – Appeal to the individual’s emotion and help them understand the “feeling” of why the change is important. (“Here’s Billy.  His life might’ve been saved had we had this kind of information in our hands.  Now that we have this tool, we have a chance to offer treatment earlier and avoid wasting valuable time when intervention is needed!  That’s why using this application is so important!  We don’t want another child to suffer like Billy did.”)

Shape the Path – Removing obstacles.  (“I’m going to remove the loading screen in the beginning, because I realize it frustrates you when you have to wait.  Especially, when you have very little time to use the application as it is!”)

They also talk a lot about avoiding the “Attribution Error”.  It’s defined as assuming a characteristic about a group rather than investigating a logistical problem (e.g. Instead of assuming the users are “lazy”, maybe they don’t use your program because the entry form has too many fields and is time consuming.)  If you get anything from this book, as a programmer, it should be to remind yourself of our propensity to fall victim to the Attribution Error.

Drive: The Surprising Truth about What Motivates Us (by Daniel Pink):

I don’t even need to explain this book.  Just watch this video.  If I’ve learned anything from this book it’s this. Incentives aren’t always good. Sometimes, people need to be motivated in other ways (i.e. more freedom to do their job).  Money, perks, or punishment (negative reinforcement) are not the only tools available (and sometimes do more damage than good!).  This book is about the science of motivating people and helping them realize their greatest potential.

Good to Great: Why Some Companies Make the Leap…And Others Don’t (by Jim Collins):

More the “CEO Handbook” than anything else, this still offers plenty of inspiration of “Level-5″ leaders.  If you feel like you don’t have what it takes to lead people towards a vision, there are some great patterns of success presented in this book.  If you ever thought to yourself: “What makes a great leader?”, this book is for you.

The Ten Faces of Innovation: IDEO’s Strategies for Defeating the Devil’s Advocate and Driving Creativity Throughout Your Organization (by Thomas Kelley):

You’re in the conference room, the lights are down, and you’re finally showing off your big idea, then someone in the back pipes up: “I’m just gonna play devil’s advocate for a moment…” and then proceeds to completely obliterate any hope of getting your idea launched.

This book provides ways to deal with that kind of criticism (you know, the “unhelpful” kind?).  What you discover is that if you get the right personalities on your team (and in that conference room) , the Devil’s Advocate doesn’t stand a chance.

I think its important, as developers, to challenge ourselves with other reading instead of books like: “Learn <new programming language> in 24 Hours!”  Getting to the root of how our users think (both emotionally and analytically) is important in creating products that truly make their lives easier.

As far as the organizational change I was tasked to help bring about?  Take a look at our ISS department explaining how technology impacts people’s lives all around the hospital and in our community.

This is the final post in a series about creating a robust search U.I. for SharePoint.

We have successfully captured each search query submitted through our U.I. and stored them into a normalized schema in our database.

So the final question is: “How do we use this information to provide search suggestions to our users?”.

The first step starts with our queries.  Our schema has 3 tables:

Tag Table: This captures all the unique keywords a user might type into their search query e.g. “dress”, “code”, etc.
Context Table: Captures the date, time, and the user who performed the search.
Context_Tag: Combines keywords and context.

These three tables allow us create some nifty results.

For instance:

What are the top ten phrases? (Use this to make a tag cloud):

 DECLARE @tblContextTag TABLE(Row_Num int, ContextID int, Ordinal int, Tag nvarchar(255))
 DECLARE @tblContextPhrases TABLE (Phrase nvarchar(255))
 DECLARE @Tags int
 DECLARE @Tag n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
 DECLARE @Row_Num int
 DECLARE @ContextID int
 DECLARE @Ordinal int
 DECLARE @Phrase n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]</pre>
INSERT INTO @tblContextTag (Row_Num, ContextID,  Ordinal, Tag)
 (SELECT ROW_NUMBER() OVER(ORDER BY ContextID, Ordinal) AS 'Row_Num', ContextID, Ordinal, Tag
 FROM Context_Tag A INNER JOIN
 Context ON A.ContextID = Context.ID INNER JOIN
 Tag ON A.TagID = Tag.ID
 GROUP BY ContextID, Ordinal, Tag, TagID)

 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = (SELECT TOP 1 ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num ORDER BY ContextID)
 WHILE @Tags <= (SELECT COUNT(*) FROM Context) - 1
 BEGIN
 SET @Ordinal = 0
 SET @Phrase = ''
 WHILE @Row_Num <= (SELECT TOP 1 Row_Num FROM @tblContextTag WHERE ContextID = @ContextID ORDER BY Row_Num DESC)
 BEGIN
 SET @Tag = (SELECT TOP 1 Tag FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Phrase = @Phrase + ' ' + @Tag
 SET @Ordinal = @Ordinal + 1
 SET @Row_Num = @Row_Num + 1
 END
 INSERT INTO @tblContextPhrases (Phrase) VALUES (LTRIM(@Phrase))
 SET @ContextID = (SELECT ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Tags = @Tags + 1
 END

 SELECT TOP 10 Phrase, COUNT(Phrase) AS Weight
 FROM @tblContextPhrases
 GROUP BY Phrase
 ORDER BY COUNT(Phrase) DESC, Phrase

Or, how about:

What are the top 20 suggestions for <specified network login> based on what this user has search for in the past?  Plus I want to include what is popular among other users’ searches.  And please filter by <specified first letter of the query>.

-- Add the parameters for the stored procedure here
 @value n_varchar(1), --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
 @networkid n_varchar(10) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 -- Insert statements for procedure here
 DECLARE @tblContextTag TABLE(Row_Num int, ContextID int, Ordinal int, Tag nvarchar(255))
 DECLARE @tblContextPhrases TABLE (Phrase nvarchar(255))
 DECLARE @tblContextTOPPhrases TABLE (Phrase nvarchar(255))
 DECLARE @Tags int
 DECLARE @Tag n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]
 DECLARE @Row_Num int
 DECLARE @ContextID int
 DECLARE @Ordinal int
 DECLARE @Phrase n_varchar(255) --fix n_varchar to nvarchar [added due to issue with WordPress code plugin]

 INSERT INTO @tblContextTag (Row_Num, ContextID,  Ordinal, Tag)
 (SELECT ROW_NUMBER() OVER(ORDER BY ContextID DESC, Ordinal) AS 'Row_Num', ContextID, Ordinal, Tag
 FROM Context_Tag A INNER JOIN
 Context ON A.ContextID = Context.ID INNER JOIN
 Tag ON A.TagID = Tag.ID
 WHERE NetworkID = @networkid AND
 Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()
 GROUP BY ContextID, Ordinal, Tag, TagID)

 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = (SELECT TOP 1 ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num ORDER BY ContextID)
 WHILE @Tags <= (SELECT COUNT(*) FROM Context WHERE NetworkID = @networkid AND Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()) - 1
 BEGIN
 SET @Ordinal = 0
 SET @Phrase = ''
 WHILE @Row_Num <= (SELECT TOP 1 Row_Num FROM @tblContextTag WHERE ContextID = @ContextID ORDER BY Row_Num DESC)
 BEGIN
 SET @Tag = (SELECT TOP 1 Tag FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Phrase = @Phrase + ' ' + @Tag
 SET @Ordinal = @Ordinal + 1
 SET @Row_Num = @Row_Num + 1
 END
 INSERT INTO @tblContextPhrases (Phrase) VALUES (LTRIM(@Phrase))
 SET @ContextID = (SELECT ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Tags = @Tags + 1
 END
 --Now get overall popular results
 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = 0
 SET @Tags = 0
 DELETE @tblContextTag

 INSERT INTO @tblContextTag (Row_Num, ContextID,  Ordinal, Tag)
 (SELECT ROW_NUMBER() OVER(ORDER BY ContextID, Ordinal) AS 'Row_Num', ContextID, Ordinal, Tag
 FROM Context_Tag A INNER JOIN
 Context ON A.ContextID = Context.ID INNER JOIN
 Tag ON A.TagID = Tag.ID
 WHERE Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()
 GROUP BY ContextID, Ordinal, Tag, TagID)

 SET @Tags = 0
 SET @Row_Num = 1
 SET @ContextID = (SELECT TOP 1 ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num ORDER BY ContextID)
 WHILE @Tags <= (SELECT COUNT(*) FROM Context WHERE Date_Time >= DATEADD(mm, -6, GETDATE()) AND Date_Time <= GETDATE()) - 1
 BEGIN
 SET @Ordinal = 0
 SET @Phrase = ''
 WHILE @Row_Num <= (SELECT TOP 1 Row_Num FROM @tblContextTag WHERE ContextID = @ContextID ORDER BY Row_Num DESC)
 BEGIN
 SET @Tag = (SELECT TOP 1 Tag FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Phrase = @Phrase + ' ' + @Tag
 SET @Ordinal = @Ordinal + 1
 SET @Row_Num = @Row_Num + 1
 END
 INSERT INTO @tblContextTOPPhrases (Phrase) VALUES (LTRIM(@Phrase))
 SET @ContextID = (SELECT ContextID FROM @tblContextTag WHERE Row_Num = @Row_Num)
 SET @Tags = @Tags + 1
 END

 --Insert overall popular results
 INSERT INTO @tblContextPhrases (Phrase)
 SELECT TOP 20 Phrase FROM @tblContextTOPPhrases
 GROUP BY Phrase HAVING Count(Phrase) > 5
 ORDER BY COUNT(Phrase) DESC, Phrase

 SELECT DISTINCT Phrase AS value
 FROM @tblContextPhrases
 WHERE Phrase LIKE @value + '%'

END

The above stored procedure will give you the result set you need in order to inject meaningful suggestions into your U.I. using the jQuery autocomplete function.  Just call the stored procedure from the web service that is connected to your jquery plugin.  Here is some more info on how the jquery autocomplete code functions.  Also, if you’re wondering how you might get the user’s login from SharePoint to pass to the stored procedure, check this post out.

And the final result is a rich U.I. with personalized search suggestions. Right in SharePoint!

This is a continuation of a series about creating a robust search U.I. for SharePoint.

We’ve created our U.I. elements and embedded them into the master page. We’ve also implemented JQuery and connected our U.I. to a web service to populate our auto completion.

The next questions is: “How do I populate it the auto completion with suggestions?”

Well, before we can answer that, we have to figure out how we are going to store the search queries in the first place. The challenge, as you might well imagine, is that we can’t just store every string value a user might type into the search box.

For instance, if several users type in the keywords “dress code”, we don’t want to store that phrase, multiple times, into the database.

After some lengthy discussions with my brother and a couple of napkin drafts, we came up with a rather simple solution.

Rather than store the entire phrase “dress code”, couldn’t we just parse the words “dress” and “code” and store them into a table with an identifier?

So, instead of this:

We get this:

I decided to create a stored procedure that would collect a search phrase, parse it into “tags”, and INSERT them into the “Tag” table. If the record already existed, then an error would be caught and I would move on.

Note: I won’t go into detail about how I connected the stored procedure to the user control. Suffice to say, I created my connections and parameters in my web service. The U.I. passes the phrase to the web service when the user executes a search. The web service then makes a connection to the database, collects the phrase into a parameter, and calls the stored procedure. This is all standard .NET data stuff and there are a plethora of articles on this subject.

Here is the SQL code for my stored procedure “spStoreSearch”:

-- Add the parameters for the stored procedure here
	@TagList nvarchar(255),
	@NetworkID nvarchar(10)
	AS
	BEGIN
 --Please refer to attached code.

Note:For some reason, my blog article wont post with the SQL code in it. Here’s a link to the script.

You might have noticed, I’m also storing a value of “NetworkID” into a table called “Context” and values of “ContextID”, “Ordinal”, and “TagID” in a table called “Context_Tag”. As you might’ve imagined at this point, I cannot just store the individual tags into the database. They would lose their context. In other words, I would only be able to say: “Guess what guys!!! The word ‘dress’ is the most popular search word!!!! WOOO HOOO!!! YEAH!” They’d look at me like I had lost my marbles. The word “dress” could have so many different contexts: “dress code”, “dress policy”, “informal dress”, “dress code violation”…You get the idea.

So I have to be able to store the contextual information in a meaningful way.

Also, I would like to know who’s completing the search.

Thankfully, SharePoint provides that information by way of collecting your network credentials. I wrote a blog post about how you can go about getting that kind of user information from SharePoint. I used the same methodology to get my “NetworkID” here.

Combined, the information would get stored like this:

Example: At 0750 on 03/16/2010, user “tlowderm (a.k.a Travis Lowdermilk) searched the phrase “dress code”
Tag Table

Context Table

Context_Tag

As you can see, this is as normalized as it’s gonna get (unless you can suggest otherwise?). You’ll also notice the column “Ordinal” in the Context_Tag table.
This is used to reference the order a sequence of tags were entered.

For example, if I were to query the phrase “dress code”, the result would be “code dress” if I were to ORDER BY tag alone.

The next challenge is putting together queries that present meaningful search data. Answering questions like: “What’s the most popular search phrase?” and “What searches has Travis requested?”.

In the next post, SharePoint Search on Steroids Part 4 – Retrieving Search Queries, I’ll detail how to retrieve this kind of information and how to enrich the user search experience by
injecting suggestions based on popular search phrases.

This is a continuation of a series about creating a robust search U.I. for SharePoint.

Connecting the U.I. to Search Results

We now have our user control embedded on the master page of our site. First we should have the U.I. forwarding the user to the search results upon clicking “Search” or submitting a query.

This is simple enough. In reality, we are just redirecting them to the appropriate search results page and placing their query in a query string parameter:

//This would be for a general query
Response.Redirect("/SearchCenter/Pages/results.aspx?k=" + txtSearch.Text.Replace(" ", "%20"));
//This would be for a people search
Response.Redirect("/SearchCenter/Pages/peopleresults.aspx?k=" + txtSearch.Text.Replace(" ", "%20") + "&s=People");
//This would be for a custom scope
Response.Redirect("/SearchCenter/Pages/Results.aspx?k=" + txtSearch.Text.Replace(" ", "%20") + "&s=This isMyCustomScope");

You can create a U.I. that stores the selected search type into a session variable. I created tabs upon the top of the search. If the user clicks on “People”, for instance, I store a session variable of the value “people” and direct them to the appropriate page (peopleresults.aspx) upon the search click or search submit event:

Each tab stores a value in a session variable. The variable redirects the user to the appropriate search result page.

Enhancing search with JQuery and WebServices

We first need to integrate JQuery into our SharePoint site.  I imagine there are many ways to do this, however, this approach seemed the most straightforward to me:

1. Download the JQuery code.  It’s also available on the Microsoft CDN, but for an Intranet site, I prefer offering it from our own servers.

2. Open up SharePoint Designer and create a folder in the root called “_scripts” and place the jquery.js file there.

3. Open up your masterpage in SharePoint Designer and in the <head> section add:

<script type="text/javascript" src="/_scripts/jquery-1.3.2.js"></script>

4. You’ll also want to download the autocomplete plugin from JQuery.  This plugin allows us to present suggestions (very much like Google) as the user begins to type their query.

5. Follow steps 2 and 3 to add your autocomplete.js file

For brevity, I won’t go  into specifics about how the autocomplete js file works.  There are plenty of other sources on the Internet.  However, in order for our suggestions to be produced dynamically we do need to connect our user control to a web service.  In SharePoint this may not be as easy as it seems.  I chose to integrate the web service code into my existing site.  I’m sure there are many other methods, but this seemed the most straight-forward way:

1. Add a “New Item…” to your Visual Studio Web Project (the same project that contains your user control)

2. Choose “Web Service” to create a new .asmx file.

3. Write the necessary web method(s) to provide data to your autocomplete script (We’ll cover this in more detail in Part 3)

Now, when we compile our web project, the .dll will include code for our web service as well.  The .dll will go into our bin folder of our site, we will also need to create a space for our .asmx file.  I found that creating a folder in the location of your site’s file system is easiest (\\<front-end web server>\<drive-letter>$\inetpub\wwwroot\wss\VirtualDirectories\<site name and port>).

I created a folder called “_webservices” and placed the .asmx file in there.

I then used a ScriptManagerProxy control inside the user control to create a script proxy to be used by the autocomplete jquery plugin:

<asp:ScriptManagerProxy ID="SMP" runat="server">
<Services>
<asp:ServiceReference Path="/_webservices/<web service name>.asmx" InlineScript="false" />
</Services>
</asp:ScriptManagerProxy>

You will need to give “Everyone” or “Domain Users” access to this folder because the service actually provides a .js file to be embedded into your site at load time.

We now have the “plumbing” in order.  Let’s review:

1. We have a user control on the master page for all of our U.I. elements

2. We’ve added the jquery and autocomplete .js files, using SharePoint Designer, to a “_scripts” folder in the root.  We created references to those files in the <head> section of the master page.

3. We added a web service (.asmx) item to our Visual Studio project and added the necessary code to make our autocomplete plugin work.

4. The user control creates a proxy to connect to a web service that is stored in our site’s folder structure (\\<front-end web server>\<drive-letter>$\inetpub\wwwroot\wss\VirtualDirectories\<site name and port>\_webservices\webservice.asmx)

The next step will be to capture and store search queries so that we can provide things like tag clouds and suggestions.

Coming up… SharePoint Search on Steroids Part 3 – Storing Search Queries

Probably the greatest out-of-the-box offering from the SharePoint product is search. However, the U.I. for search within SharePoint sites leaves a bit to be desired. The various site templates place the search box in areas on the page, I felt, weren’t prominent enough. Plus, I wanted to offer things like suggestions when searching for people and tracking/trending search topics.

Based on my research in user behavior, I knew that having a robust search U.I. would be vital in providing a successful Intranet site using SharePoint.

So, I set out to create a fully-functional user control to enhance the experience for our users. This is the first in a series of posts that will cover how it all got implemented.

Creating the User Control

I felt like search should be a tool that is readily available throughout the entire Intranet experience. I decided to create one user control that could be placed on the master page of the Intranet site. All the code would be developed into the user control and data would be provided by a web service.

1. Open Visual Studio and create a new Web Site Project
2. Add a new user control to the project
3. Add References to Microsoft.SharePoint and System.DirectoryServices (The Microsoft.SharePoint.dll can be located on your front-end web server if you develop on a separate workstation)
4. You may create a masterpage and attach it to the Default.aspx in your project. Then you can add the user control to the Default.aspx page. This will create an environment similar to your SharePoint site.

Now we have an environment similar to our SharePoint site. We can build design elements on our user control and bring them into our SharePoint site experience.

That’s great. But how do I get the user control working across my entire SharePoint site?

Because the search is so fundamental in our design, I wanted to add the user control to the master page of the SharePoint site. This would make the search experience prominent within the site (think Google or Yahoo).

1. Create a folder called “usercontrols” so that you can put the user control design (.ascx) file in a location the SharePoint site can find. (\\\C$\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\CONTROLTEMPLATES\usercontrols)
2. In the usercontols folder, place your .ascx file (after you build the project of course)
3. In the bin file of your site (\\$\inetpub\wwwroot\wss\VirtualDirectories\sharepointsite.domain.com80\bin) copy the compiled .dll from your project.
4. Open up SharePoint Designer and navigate to your master page (in my case I’m using “_catalogs\masterpage\default.master”)
5. Register the user control on the master page: <%@ Register src="~/_controltemplates/usercontrols/.ascx” tagname=”” tagprefix=”uc1″ %>

Of course, for this to work we need to mark certain assemblies as “safe” in the web.config of our site if they have not been deployed to the GAC. For instance, if you are using a ScriptManager you may want to add

<SafeControl Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" Namespace="System.Web.UI" TypeName="*" Safe="True" AllowRemoteDesigner="True" />

to your web.config under “SafeControls”. Depending on any other assemblies (Telerik, Dundas, etc.) you may need to add those as well. There is plenty of information in Googleland to handle those types of questions.

You may also want to take a look at my post about controlling the styling of your master page. This will help you in getting your user control placed and designed exactly how you want it.

Next Part: SharePoint Search on Steroids Part 2 — Using JQuery and Web Services

SharePoint has pretty amazing “people” searching capabilities. However, if you are like our organization, your Active Directory data may not be as complete as you’d like it to be. Or maybe you manage identities using another system like PeopleSoft. So the question becomes: How do you get identity information into SharePoint?

I decided to develop a small console application that would insert user profile data, pulled from our PeopleSoft database, into the SharePoint User Profile Store:

What kind of dark magic must be peformed to get information in here!?

Also, we had employee photos, sitting in a web directory on the Central Admin, front-end web server, that I wanted to point to for the profile pictures.

All of this can be done using the SharePoint User Profile Manager class in the SharePoint API.

1. Start a Console Project and add references to System.Web, Microsoft.SharePoint, and Microsoft.Office.Server

(NOTE: If you are deving on a box that doesn’t have WSS or MOSS installed, you won’t be able to debug, however, you can still add the .dll files to your project for compiling.  Just go to your MOSS or WSS server and pull the Microsoft.Office.Server.dll and the Microsoft.SharePoint.dll [%Program Files%Common Files\Microsoft Shared\web server extensions\12\ISAPI] into your project)

Below is the code I used.  Obviously, there are many variants to this to make it work and no two data structures are the same.  However, I think the general concept could work for many different environments.

Essentially, what I did was iterate through a DataTable that had all my employee data in it.  I then pulled up the user profile, using the SharePoint Profile Manager, and inserted the profile property values.

All of the values I’m referencing (PhoneNo, JobTitle, Supervisor_NetworkID, etc.) were available in our PeopleSoft database. If you don’t have access to that level of data, or it’s not being collected in your organization…well…this will be kinda hard.

Finally, I committed the changes in SharePoint and moved on to the next record:

using Microsoft.SharePoint;
using Microsoft.Office.Server;
using Microsoft.Office.Server.UserProfiles;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace SharePoint_Employee_Photos
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Get usernames
                //Retrieve E-Mail Addresses
                SqlConnection sqlconn1 = new SqlConnection("Data Source=Server;Initial Catalog=Database;User Id=dbuser;Password=dbpassword;");
                SqlCommand sqlcomm1 = new SqlCommand("Stored_Procedure", sqlconn1);
                sqlcomm1.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sda = new SqlDataAdapter(sqlcomm1);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                using (SPSite spSite = new SPSite(@"http://sharepoint.site.org"))
                {
                    ServerContext siteContext = ServerContext.GetContext(spSite);
                    UserProfileManager pManager = new UserProfileManager(siteContext);

                    foreach (DataRow dr in dt.Rows)
                    {
                        String strUsername = "Domain\\" + dr["NetworkID"].ToString();
                        UserProfile userProfile = null;
                        //Check to see if the profile exists in SharePoint
                        if (pManager.UserExists(strUsername))
                        {
                            userProfile = pManager.GetUserProfile(strUsername);
                            Console.WriteLine("User Success: Domain\\" + dr["NetworkID"].ToString());

                            //Update any of the user profile property values

                            //Photos are made available through a separate SSIS task that queries the PeopleSoft database and outputs
                            //the images to a web folder.  The web folder is served on my Central Admin, web front end server.
                            //Let's make sure the file is there first.
                            if (File.Exists(@"[C:\PhotoWebsite\photos\" + dr["EmpID"].ToString() + ".jpg"))
                            {
                                userProfile[PropertyConstants.PictureUrl].Value = "http://website.photos.org/photos/" + dr["EmpID"].ToString() + ".jpg";
                            }
                            if (!dr["PhoneNo"].Equals(DBNull.Value))
                            {
                                if (!dr["PhoneNo"].Equals(String.Empty))
                                {
                                    userProfile[PropertyConstants.WorkPhone].Value = dr["PhoneNo"].ToString();
                                }
                            }
                            if (!dr["DeptName"].Equals(DBNull.Value))
                            {
                                if (!dr["DeptName"].Equals(String.Empty))
                                {
                                    userProfile[PropertyConstants.Department].Value = dr["DeptName"].ToString();
                                }
                            }
                            if (!dr["JobTitle"].Equals(DBNull.Value))
                            {
                                if (!dr["JobTitle"].Equals(String.Empty))
                                {
                                    userProfile[PropertyConstants.Title].Value = dr["JobTitle"].ToString();
                                }
                            }
                            if (!dr["Supervisor_NetworkID"].Equals(DBNull.Value))
                            {
                                if (!dr["Supervisor_NetworkID"].Equals(String.Empty))
                                {
                                    String strSupervisorUsername = "Domain\\" + dr["Supervisor_NetworkID"].ToString();
                                    if (pManager.UserExists(strSupervisorUsername))
                                    {
                                        userProfile[PropertyConstants.Manager].Value = strSupervisorUsername;
                                        Console.WriteLine("Supervisor Success: Domain\\" + dr["Supervisor_NetworkID"].ToString());
                                    }
                                }
                            }

                            userProfile[PropertyConstants.WorkEmail].Value = dr["Email"].ToString();

                            //Commit the changes
                            userProfile.Commit();
                            Console.WriteLine("Import Success!");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                Console.ReadLine();
            }
        }
    }
}

I found myself in a situation, the other day, where I needed to display items from a Links List on one of my subsites.

The trouble was that the Links List resided on the parent site.  Well, that should be easy right?  Wrong.

I started by using the Content Query Web Part (CQWP).   I selected to “Show items from the following list” and selected the Links list on the parent site.

The list resolved, however, this is what it looked like:

Hmmmm...That dont look right

It turns out that the CQWP doesn’t know what to do with the URL and Title fields on the Links List.

Also, when you click on the “(Blank)” link, it takes you to the item view of the link and not the page you’re linking to.

So I started Googling and found Ishai Sagi’s clever little tip.

Basically, he exports the web part and makes some modifications.  Essentially, creating a custom web part.

1. I would start by configuring any settings you will want to carry over to your custom web part (Query, Appearance, Title, Sorting, Audience Targeting, etc.)  This will save you from having to make those adjustments after importing the web part to your pages.

2. Click the “edit” button on the web part and select “Export…”

Select "Export..." from the edit menu on the web part

3.  Save the .webpart file to your hard disk.  Then open it using your favorite XML editor.

4.  You’re going to be looking for a property “CommonViewFields”:

<property name="CommonViewFields" type="string" />

Change it to this:

<property name="CommonViewFields" type="string">URL,text</property>

5. Now we need to add a custom “Item Style”, so the web part will understand what to do with the new “CommonViewFields” property. This will require editing the item style XSL of the site.

6. Open up SharePoint Designer and navigate to your site or parent site.

7. Check out and open Style Library > XSL Style Sheets > ItemStyle.xsl

8. We are now looking for the template for the style “NoImage”:

<xsl:template name="NoImage" match="Row[@Style='NoImage']" mode="itemstyle">

9. Make a copy of the complete template:

from

<xsl:template name="NoImage" match="Row[@Style='NoImage']" mode="itemstyle">

to

<xsl:template name="NoImage" match="Row[@Style='NoImage']" mode="itemstyle">

and paste it under the “NoImage” template.

1. Rename “NoImage” to “LinkList”.
2. Change the “href” attribute to point to the URL column, the text before the column.
3. Change the link text to point to the URL column, the text after the column.

10.  Here’s the complete code:

<xsl:template name="LinkList" match="Row[@Style='LinkList']" mode="itemstyle">
 <xsl:variable name="DisplayTitle">
        <xsl:call-template name="OuterTemplate.GetTitle">
            <xsl:with-param name="Title" select="@URL"/>
            <xsl:with-param name="UrlColumnName" select="'URL'"/>
        </xsl:call-template>
    </xsl:variable>
 <xsl:variable name="LinkTarget">
        <xsl:if test="@OpenInNewWindow = 'True'" >_blank</xsl:if>
    </xsl:variable>
 <div id="linkitem" class="item" >
  <div class="bullet link-item">
   <xsl:call-template name="OuterTemplate.CallPresenceStatusIconTemplate"/>
   <a>
    <xsl:attribute name="href">
     <xsl:value-of select="substring-before($DisplayTitle,', ')">
     </xsl:value-of>
    </xsl:attribute>
    <xsl:attribute name="title">
     <xsl:value-of select="@Description">
     </xsl:value-of>
    </xsl:attribute>
    <xsl:value-of select="substring-after($DisplayTitle,', ')">
    </xsl:value-of>
   </a>
  </div>
 </div>
</xsl:template>

If you make any mistakes with this part of the styling, it will affect ANY other CQWP you might have deployed on other pages.  Might be a good idea to check on those right now.

11. If everything looks gravy, save the changes to the file and go to the Site Settings of your site or parent site.

12. Under “Galleries”, select “Web Parts”

13. Upload the .webpart file.  Some other settings will come up. Give it a file name, Title, Description. I also like to specify a “Custom” Group value. That way the web part is easy to find in the “Add Web Parts” dialog.

14. Go to your site page or subsite page and Add Web Part.  Add the custom web part to the page.

15. Modify the web part and under Presentation > Styles > Item Style,  select “LinkList” from the dropdown menu:

The links should be showing correctly.  Plus, when you click on them, they go directly to the page you are linking to.

Nice work Ishai!

Even though most organizations deal with Adobe Acrobat and .pdf documents, it doesn’t mean Microsoft added this file type in SharePoint. Sure you can upload .pdf documents to document libraries, but you can forget about crawling those documents and returning results from your search queries.

Thankfully, there are a few resources to address this issue. I will document those resources below, however, here is a condensed list of actions:

1. You have to get your hands on an IFilter from Adobe. Essentially, the IFilter is the link that gets SharePoint to understand what a .pdf document is and how to crawl it. The easiest way to install the IFilter is to download the latest version of Adobe Acrobat Reader on your Central Administration server. The latest versions (8.0 and up) of Reader include the IFilter.

2.  You then need to add the extension to your SharePoint extension list:

1. Click Start, click Run, type regedit, and then click OK.
2. Locate and then click the following registry subkey:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Search\Applications\GUID\Gather\Search\Extensions\ExtensionList
3. On the Edit menu, point to New, and then click String Value.
4. Type 38, and then press ENTER.
5. Right-click the registry entry that you created, and then click Modify.
6. In the Value data box, type pdf, and then click OK.

3.  Add the PDF file type to the Extensions List for WSS search by editing the registry:

1. Start regedit
2. Open the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Search\Applications\{Random GUID}\Gather\Search\Extensions\ExtensionList
3. Add PDF to the list as a new String Value. Use a new high value e.g. if 37 is the highest value, use “38″ as the key with the value “pdf”

4.  Make a graphic for the .pdf icon to be displayed in the search results and add it to SharePoint files to be referenced:

1. Create a 16X16 pixel image of the Acrobat file icon.
2. Save the image as pdf16.gif
3. Add the Acrobat PDF picture to the SharePoint templates directory. Copy the Acrobat PDF picture called pdf16.gif in the 12 Hive\TEMPLATE\IMAGES folder, e.g. %programfiles%\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\IMAGES.
4. Bind the Acrobat PDF picture to the PDF file type
5. Open the 12 Hive\TEMPLATE\XML\DOCICON.XML file
6. Find the <DocIcons.ByExtension> part
7. Add the following mapping: <mapping Key=”pdf” Value=”pdf16.gif” OpenControl=”" />

5.  Run IISReset

6. For MOSS 2007 users, Add the file type to Central Administration:

1. Go to your SSP site
2. Click on Search Settings > File Types > New File Type
3. Add pdf as a file type

7. Complete a Full Crawl of your content sources.  This will re-crawl pdf documents that may already be in libraries.

Some of you might see the following warning in your crawl logs:

The file reached the maximum download limit. Check that the full text of the document can be meaningfully crawled.

This is because some of your .pdf files may be too large.  Also, SharePoint wont crawl .pdf documents that have other .pdf documents contained within them.

There doesn’t seem to be a fix for complex .pdf documents, however the size issue can be resolved by adjusting the MaxDownloadSize key in the registry:

1. Start Registry Editor (Regedit.exe).
2. Locate the following key in the registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\12.0\Search\Global\Gathering Manager
3. Open Edit – New – DWORD Value. Name it MaxDownloadSize. Double-click, change the value to Decimal, and type the maximum size (in MB) for files that the gatherer downloads.

You may also want to adjust the timeout period for SharePoint to crawl the documents as you have just adjusted the amount of data that can be crawled and it may take longer:

1. In Central Administration, on the Application Management tab, in the Search section, click Manage search service.
2. On the Manage Search Service page, in the Farm-Level Search Settings section, click Farm-level search settings.
3. In the Timeout Settings section change Connection and Request acknowledgement time.

1. For WSS3 users: The registry key for is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\
   Web Server Extensions\12.0\Search\Global\Gathering Manager

Default would be MaxDownloadSize (16MB) * MaxGrowFactor (4MB) = 16 * 4 = 64MB of text can be indexed.

After all of your changes, you should restart the CA server and run another full crawl.

Resources:

http://support.microsoft.com/default.aspx/kb/927675

http://blog.tylerholmes.com/2008/04/walkthrough-installing-adobe-v6-pdf.html

http://bloggingabout.net/blogs/harold/archive/2008/10/02/index-pdf-documents-on-sharepoint-using-adobe-pdf-ifilter-9.aspx

http://grounding.co.za/blogs/neil/archive/2008/12/02/working-with-pdf-s-and-sharepoint.aspx

http://fmuntean.wordpress.com/2008/05/22/increase-file-size-crawling/

One of my favorite features to demo on SharePoint is its incredible searching capabilities. It is, by far, the feature that elicits the greatest reaction.

More specifically, I just plain LOVE the way SharePoint handles searching for people within the organization.

However, the data comes from our Active Directory schema and if your organization is anything like ours, it’s not the cleanest set of data.

For instance, I was finding devices, voicemail boxes, security and distribution groups, and all sorts of other objects showing up in my people results.

I did some binging around the Internet and discovered Mindsharp’s blog.
They had a couple of posts regarding filtering profile data within SharePoint.

This one written by Wayne Hall (archived) and
this one written by Daniel A. Galant.

Here’s the breakdown:

1. Go to your Central Administration site and select your Shared Service Provider. It may require you to log in under the farm administrator account.
2. Under User Profiles and My Sites, select: “User profiles and properties”.
3. On the User Profiles and Properties page, select: “View import connections”.
4. You can select “Create New Connection” or edit an existing connection.

You’ll have to specify your own domain controller information (most of it is already auto detected). The most important item is “User filter”.

The user filter allows you to specify criteria you would like included or excluded from the import. There is a variety of options to explore using LDAP query filters. There are SO many resources out there to explain writing LDAP filter queries. Here’s one to get you started.

The query that I decided to use, was this:

This, essentially, filters the Active Directory schema to only import those objects that:

1. (objectCategory=person) = are under category “person”.

AND

2. (objectClass=user) = are of class “user”.

AND

3. (sn=*) = have a surname [Last Name].

AND

4. (mail=*) = have a valid e-mail address.

AND

5. (showInAddressBook=*) = are available in our corporate address book.

AND

6. (!(userAccountControl:1.2.840.113556.1.4.803:=2)) = is an account that is active.

This filter eliminated over 9,000 unnecessary items in our user profile list making our people search far more effective.

NOTE : One area that messed me up was setting the Scope to “One level”. This was producing an error:

A test import based on current input returned no results. Please re-check the search base and filter.

This was occurring because the filter was searching for items at the root level. Because I had no items that matched my criteria, at the root level, it was returning no results.
Once I selected the option “Subtree” it searched the entire schema and I was all aboard the Gravy Train Express.

Passing multiple, comma-delimited, parameters to a stored procedure is tricky. I found the need for this trick while trying to pass multiple parameters from a web form to my Microsoft SQL Reporting Services report.

Let’s say, for instance, you would like to filter employee data by a series of employee numbers:

One would think this would work. However, it does not. The IN operator cannot accept a comma-delimited string. Too bad. It would make things a lot easier if it did.

4GuysFromRolla came up with this solution around this problem.

Essentially, they create a user-defined function that receives a comma-delimited string and converts it into a table. The IN operator loves it some tables.

Here’s the function:

You can now use this function like so:

The Split function returns a table to the IN operator:

Furthermore, because you are using a SELECT statement with the Split function, you could apply additional filters to remove specified parameters that you would like to ignore: