SharePoint as a Twitter Client. Sort of – Part Three

Following on from part two I commented that there were a couple of issues outstanding,

1. It doesn’t automatically update the SharePoint list. We have to repeat the list creation process every time.
2. We have to run the process manually. 

This post will cover the first item on the list and is really straight forward.

Go back to the first post and make sure that you have created your list on your SharePoint site by exporting it. (Go now, I’ll wait).

Once it’s done in the Access Database delete the “status” table (or rename it if you want to play it safe).

Then from the “External Data” tab select the option to “Import SharePoint List”.

The wizard will start – enter your site name and select the option.

“Link to the data source by creating a linked table”.

Click Next to display the lists you can import.

Choose the list (I’ve called mine “TweetPoint” – think it’ll catch on?)

Click OK to link the list.

You’ll then see the SharePoint list alongside the other Access tables.

User Information List is also linked as this holds user information which will be entered onto the list for things such as the “Created by” field.

Now the clever bit to hook it up to our Twitter API calls.

Bear with me because this is really complex

Right click on your newly imported list and select the rename option

Change the name to “status”

Done! (ok I may have exaggerated how difficult this was)

So what’s actually happened here?

We’ve changed the name that Access uses to reference the table.

In the background it’s still hooked up to SharePoint via it’s URL so any changes we make on the table will automatically be updated on the SharePoint side!

The import process we created in the first post is expecting to append the data to a table called status (since that is what the Twitter API returns) so that is exactly what we’ve provided!

Only one thing left to clear up in the next post!

SharePoint as a Twitter Client. Sort of – Part Two

I intended to follow on from part one a bit sooner but it’s been really busy at the office!

In part one I gave a brief overview of what I was trying to accomplish and how to get a rough prototype working.

While it did the job I highlighted the following limitations.

1. It only fetches the last twenty updates – if there have been twenty five updates since you last did it then you’re out of luck.
2. Conversely, if there have only been a few updates it’ll still fetch the last twenty giving you duplicate items.
3. There is also the problem of needing to run this manually. 
4. It doesn’t automatically update the SharePoint list. We have to repeat the list creation process every time.
5. Finally, it doesn’t deal with protected Twitter accounts at all!

This post will deal with the items 1, 2 and 5 and is actually pretty easy.

We’re going to use a bit of VBA to make Access a little smarter. I’m not a programmer/developer so if the code looks silly or inefficient….there is a good reason for it!

Also you’ll notice a distinct lack of error handling. This is just an exercise to see if it would work so code is kept to a minimum!

The Twitter API method I used in the previous post had a parameter where you can specify a tweet ID and the API will return all tweets since then.

http://api.twitter.com/1/statuses/user_timeline.xml?screen_name=AndyParkes&since_id=13081868261

So the only difficulty now is actually figuring out what tweet ID to use.

If only we had some sort of list we could query to do this.

Again this is really easy as we’re using Access which is built for the job!

Here’s the bit of SQL I crafted

"SELECT TOP 1 VAL(status.id) as TwitID FROM status ORDER BY VAL(status.id) DESC;"

I’m using the VAL function to convert the tweet ID to a number as I had some weird sorting issues.

I wrapped this is in a VBA function called “Get_Last_Tweet_ID”

Function Get_Last_Tweet_ID() As String
Dim db As Database
Dim strSql As String
Dim rstData As DAO.Recordset
Dim strID As String

Set db = CurrentDb()
strSql = "SELECT TOP 1 VAL(status.id) as TwitID FROM status ORDER BY VAL(status.id) DESC;"

Set rstData = db.OpenRecordset(strSql)

    If rstData.EOF = False Then
        strID = rstData("TwitID")
    Else
        strID = ""
    End If

    rstData.Close
    Set rstData = Nothing

Get_Last_Tweet_ID = strID

End Function

So this function will simply return a tweet ID we can plug into the API call and it’ll only return the most recent tweets. If the function returns an empty string we just don’t bother to run the API call.

The next question you’re probably asking is HOW do you run the API call?

Remember the wizard we ran in the last post to import the XML? All that wizard does is use a VBA function called ImportXML

The syntax is simple

Application.ImportXML(DataSource, ImportOptions)

The parameters are the same options asked for by the wizard.

A data source and whether you want to import the data, the structure or both.

That’s the first part – but since I want to be able to take protected accounts into consideration we can’t just drop a URL straight in as the data source (where would you put your username and password!)

So we’ll just fashion the request ourselves.

We’re sending a request to a web service to get response back with some XML so the object we’ll use is XMLHTTP and it will send a GET request to the Twitter API which should throw back a chunk of XML.

Function Get_Latest_Tweets(strScreenName As String, strUser As String, strPassword As String, strLastID As String) As String

Dim myXML As MSXML2.XMLHTTP
Set myXML = New MSXML2.XMLHTTP

If strLastID = "" Then

    myXML.Open "GET", "http://api.twitter.com/1/statuses/user_timeline.xml?screen_name=" & strScreenName, "False",     strUser, strPassword
Else

    myXML.Open "GET", "http://api.twitter.com/1/statuses/user_timeline.xml?screen_name=" & strScreenName &     "&since_id=" & strLastID, "False", strUser, strPassword
End If

myXML.send

Get_Latest_Tweets = myXML.responseText

End Function

Note: Depending on what you have on your machine you may need to use “Microsoft.XMLHTTP” instead.

This function takes a couple of parameters, the ScreenName of the Twitter account to retrieve, a username and password for authentication and a Tweet ID.

We check to see if we passed an ID. If not then don’t use the “since_id” parameter and just fetch the last 20 tweets (the default). We could add an extra parameter to fetch a specific number of tweets.

We then send the response and the function returns a string containing some XML.

Now that we have our XML from Twitter what now?

Since the ImportXML wizard requires a file to work with we need to save the XML to disk.

Sub SaveTmpXML(strXML As String)
     ‘ Load the XML
     Dim xmlDoc
     Set xmlDoc = CreateObject("Microsoft.XMLDOM")
     xmlDoc.async = "false"
     xmlDoc.loadXML strXML
    xmlDoc.Save strTempFile
End Sub

This is a really simple procedure. We pass the XML to it, create an XMLDOM object, load the XML string and save it disk. I used a global variable for the filename so I can specify a temp path in an options table which I’ll cover later.

Finally we can import the XML into our table,

Public Function Fetch_Tweets() As String

Dim strID As String
Dim strXML As String

If Get_Settings = False Then
    Debug.Print "Couldn’t get settings"
    End
End If

strID = Get_Last_Tweet_ID
strXML = Get_Latest_Tweets(strScreenName, strUserName, strPassword, strID)

SaveTmpXML strXML

Application.ImportXML strTempFile, acAppendData

Tidy_User_table

End Function

This a wrapper function that calls all the code I’ve outlined above.

We find the last Tweet ID, fetch the tweets from Twitter, save them to an XML file and finally import them into our table.

You’ll notice the Get_Settings and Tidy_User_Table procedure calls, I’ll also cover those in a later post.

You could hook this wrapper function up to a button on a form. Once clicked it would update the table in the database with the latest tweets.

I think that’s all three issues solved. We now only fetch exactly the tweets we need and can work easily with protected tweets (Twitter recently announced a change that will break this but I’ll cover that in an upcoming post)

I’ll get part three together a bit sooner!

SharePoint as a Twitter Client. Sort of – Part One

This is going to be quite lengthy so I’ll split into a couple of posts.

Back in March, Mark Wilson of MarkWilson.co.uk (if you’re not reading his blog….go check it out now!) asked the following on Twitter.

Does anyone know how to read XML (i.e. Twitter updates) into a SharePoint List (not the XML webpart reading it dynamically)?

Since I like a challenge I decided to take a look at it.

If you search (using your favourite search engine) for Twitter and SharePoint you’ll find one of two things.

Either, examples of how to use a webpart to grab the RSS feed of someone’s Twitter status or people creating a Twitter-like application using SharePoint (which is pretty cool anyway).

What Mark was looking for was something slightly different.

He wanted to collect posts from a specific user into a SharePoint list. In effect creating an archive that a team could view. They could even use SharePoint to create views to look for keywords.

This meant the webpart wouldn’t do as it only shows a snapshot of the current status updates. If the particular user tweets quite a lot you could miss stuff.

Mark initially pointed me in the direction of the RSS feeds for each user (here’s mine) but this gives us a similar problem to the webpart as it only shows the last 20 tweets.

This was when I stepped out of my comfort zone and started looking at the Twitter API 

It turns out the Twitter API isn’t actually very complicated. All you need to do is craft a URL to pass certain parameters and it’ll throw some XML back at you.

For example,

This url will retrieve an XML file containing my last five tweets.

http://api.twitter.com/1/statuses/user_timeline.xml?screen_name=AndyParkes&count=5 (you can click this even if you don’t have a Twitter account. Authentication is only needed for protected accounts)

So I’d now found a way of grabbing the tweets…but how to get them into a SharePoint list?

IF I were a SharePoint developer armed with a copy of Visual Studio I actually don’t think this would be all that difficult to do. (you’ll see why in one of the later parts to this post)

However, since I’m not I need to make use of the tools at my disposal.

I had to think of something that can make use of XML but also talk to SharePoint and the answer is….

Microsoft Access

Yep..seriously.

Access has the ability to import XML files into a table.

Access also has the ability to link to a SharePoint list and update it.

By putting these two bits of functionality together I was actually able to reach the desired effect.

You can try it yourself.

Open Access and create a new blank database.

Click the External Data tab and choose XML file.

Access 2010 screenshot – procedure is similar in Access 2007 though

Enter your API URL into the file name field. The URL I’m using will return my last 20 tweets

You’ll be prompted to verify the import

Choose structure and data first time round so you actually create the tables

On subsequent runs you can just append the data

Done…you can save the import steps if you want so you can repeat this without going through the whole wizard again.

I’ve now got my last 20 tweets into an Access database.

You’ll notice there are actually two tables created.

Status contains the tweets.

User contains the details about the user this is being pulled from –it’s part of the XML returned.

So to get it into SharePoint.

Select the status table, click the External Data tab again this time use the Export section and select SharePoint list.

Run through the export wizard.

Choose a destination site.

You’ll see it creating the tables.

Done!

You can then browse to your site and view the list!

You can also create a view to just show the important fields.

TaDa!……well not quite.

Currently it’s not very dynamic.

All it does is fetch the last 20 updates. If you re-ran the process above again you’d run into problems doing the SharePoint export as the status table already exists you’d end up with multiple lists (eg. status, status_1, status,2)

Also it’s not smart enough to realise that if we already have some tweets in the database we don’t want to re-add those. If you followed the procedure above for a second time you’d also end up with duplicate tweets.

Finally if you tried to put them into chronological order you may notice the sort doesn’t quite work under certain circumstances.

So in the next part we’ll put Access to work to make this a bit more intelligent and finally automate the whole thing so it’ll fetch the tweets all by itself!

InfoPath – Errr! What?

I got an email earlier this week from Richard Tubb asking a couple of questions about InfoPath.

This is also the same Mr Tubb who has been “nagging” me to post more about InfoPath and SharePoint. I do take his point as I’ve posted very little on the topic since I spoke about it at AMITPRO last year (can’t believe it’s been nearly 12 months since that!)

So….InfoPath!!

I’ll talk about InfoPath in the Office 2007 world at the moment. Even though the latest version is upon us the current version will be hanging around for a while. Also the skills are transferrable and I’ll need material for future posts right?

What is InfoPath?

In a nutshell it’s all about electronic forms.

If you’ve ever created a form in Microsoft Access it’s along similar lines. What’s cool about InfoPath though is that it isn’t tied into a specific database system. It can talk to SQL Server, Access databases, SharePoint (which is is it’s main strength) and more. You can even make use of InfoPath forms within Outlook to gather information via e-mail!

I also think that when stacked up against a form in a Word document InfoPath wins a lot!

InfoPath allows you to do form validation to ensure data is entered correctly.

It’s biggest strength is it’s capability to “unlock” data which a Word document just can’t do. (Unless you want to do a lot of VBA coding)

This means an simple example is in order.

Lets say you had a Word document that your staff used to enter their weekly hours worked.

It might look like this.

As you can see there is a lot of important information here.

At the top we have data that lets us know who the time sheet belongs to, and when it’s for.

Moving down we have the actual data itself.

The hours worked.

Finally we have a brief summary of data so you can see at a glance what’s going on.

So what’s wrong with this?

Data Validation

You would have to trust whoever fills out the form to put a date in right place and numbers in the hours worked fields. What if someone enters 25 hours for single day? What if “Sales” isn’t even the correct name of the department?

Now you can sort of do this in Word 2007. But it’s not exactly simple. In fact, good luck finding how to insert a form field – The developer tab is hidden by default (I’ll explain how to display them in another post). Then you have to contend with “legacy form controls” and the new Office 2007 form controls. Not fun at all!

Calculations

Once again you’d need to trust whoever is filling out the form to get their maths right. As with data validation you can get Word to do calculations on tables but it’s not exactly simple or intuitive!

Trapped Data

This is the biggie!

I’ve only showed you one form. Let’s imagine you’re Mr Anderson’s line manager. Your boss walks up to your desk at 9am Monday morning and tells you that your department is spending a fortune in overtime. He wants to know exactly how many hours your department logged in overtime for the last three months overall and on a per-person basis…..and he wants it for the management meeting at 11am!

So lets say that each person would have 4 timesheets per month. That’s 12 per person.

If your department had 5 people in it that’s 60 Word documents you need to open, read through, check the maths and note the important figures to bring into your total summary.

That’s going to take some time!

How does InfoPath help?

Here’s the same form designed in InfoPath

At first glance you’ll notice it’s essentially the same form as before

But if you look in the first block of data

You can see a calendar control button. This means I can choose a date with the mouse as well as typing one in.

It won’t let me put anything other than a date in the field.

You’ll also notice all the fields in the first section have a red star next to them.

This because I’ve made them mandatory fields. It’s a little visual notification to remind the user to put some data in.

You also get InfoPath to pop up a little message if no data is entered!

The fields that contain the hours worked data also have some validation rules.

This screenshot shows I’ve tried to enter some text in a numeric field

We get the nice red dotted border AND a tooltip to let me know what’s wrong.

What’s really cool about this is that I didn’t even need to set this up. Just by specifying that the field was numeric InfoPath setup the basic validation for me.

Each control has LOTS of options around data validation to make sure we get exactly the correct data in.

You may also have noticed that the department field is a drop down list.

This means that we can ensure that only valid departments are entered. You’ll see why this can be quite important later on.

Also, the data is actually pulled in from a SharePoint list.

This means that these lookup lists can easily be managed without having to edit the InfoPath form. We can also set security on the SharePoint list to ensure only specific people can make changes to the department details.

InfoPath also has rules and events we can work with. We could actually set this up so all the staff names appeared in another drop down and that once a department has been chosen only the staff in that department are shown (or vice versa. You choose your name and your department is automatically filled out)

This totally solves the Data Validation problem I mentioned above.

So what about calculation?

Easy!

Same form as above with the same data entered

However this time the summary data is updated automatically.

I’ve set the summary fields to do a simple bit of addition and then made them read-only so they just work on there own without any user input needed!

Sorted!

Data Validation and Calculation all dealt with quickly and easily.

I could understand if you were still a little sceptical at this point though. As i said lots of similar functionality is already available all across the Office suite. That brings me onto the final point I made above.

Trapped Data

The real magic happens when this is published to a SharePoint site.

When I published the form I specified that I wanted the summary fields to be pushed through to the SharePoint form library.

This is called “property promotion”.

Here is the time sheet Tom Anderson just filled out on the SharePoint site.

I can now see how many hours Mr Anderson worked that week without even opening the file!

Multiply this across the whole team and you can see at a glance what everyone was up to!

Let’s throw a bit of SharePoint magic in.

I’ve grouped by Department and then by Week Commencing Date.

Can you see why it was important to get the department entry consistent?

We can now get a real overview of the hours entered by each person.

And one finishing touch.

I’ve asked SharePoint to sum the totals fields.

This can look a little confusing your grouping as it gives you overall totals as well as a total at each group level. You’d be better setting up a filtered view to do this.

Something like this.

I’ve removed a level of grouping and filtered to only show the Sales team.

You can now see really easily over the last two weeks the Sales team have posted 18 hours of overtime.

Just to show off….you could then dump this out to Excel for further analysis.

I created that chart with about five clicks of the mouse. Getting that report together for the boss would be nice and simple.

Finally one other cool thing about using SharePoint and InfoPath for a solution like this.

The files themselves are stored in XML format

This means if yoy needed integration with another system (your accounts package for example) this makes the whole thing a lot easier.

I’ll stop there….this was supposed to be a quick introduction.

If you’ve made it to the bottom of this post thanks for the reading!

This was just one example of how you can use InfoPath in a real world scenario.

There is lots more you can do, especially when you put it together with SharePoint.

For example, imagine having an expenses form tied into a workflow so that management approval is required when a certain monetary threshold is met?

Hopefully this all makes sense and you can see why I’m such a fan of InfoPath!

Please comment if you want me to post more about InfoPath and I’ll be happy to do so

*I’ve just noticed all the dates are in US format….I didn’t change the regional settings on my newly created test site…..i’m not re-doing the screen shots though! *

Office 2010 Versions

With everything that’s been going on personally and professionally the last few months the newest version of Office sort of skipped by me

Sure I installed the “alpha” when it was released on one of my machines but I wasn’t using it full time

Yesterday I had to do a rebuild of my main office PC so I took the opportunity to install the Beta

This post isn’t about the shiny new features though, it’s about the different suites you’ll be able to purchase

This time around we get four retail versions (from the Office 2010 engineering blog)

Office Home and Student1	Office Home and Business	Office Professional	Office Professional Academic
Word 2010	Word 2010	Word 2010	Word 2010
Excel 2010	Excel 2010	Excel 2010	Excel 2010
PowerPoint 2010	PowerPoint 2010	PowerPoint 2010	PowerPoint 2010
OneNote 2010	OneNote 2010	OneNote 2010	OneNote 2010
	Outlook 2010	Outlook 2010	Outlook 2010
		Publisher 2010	Publisher 2010
		Access 2010	Access 2010

On first glance I’m really pleased

OneNote for everybody!!!

However on second glance I’m a bit gutted as you’ll notice that once again there is no InfoPath unless you buy Office via licensing (Office Professional Plus for most businesses)

I commented before on how I think a lot of smaller businesses are missing out because InfoPath is inaccessible to them

It’s frustrating when we can sell Small Business Server 2008 with one of the key features being the inclusion of SharePoint and then not being able to create some really great solutions because a key piece of software isn’t as easily available. A quick search found Infopath 2007 box product to be somewhere in between £100-£140. Licensing is obviously different but if you’re going down that route you may as well just get Professional Plus

So it’s left to the SBS team to save us! Here’s my idea,

If you ever release SBS 2008 R2 (and I understand that is IF) can we get some InfoPath licenses added to the Premium CALS? 

I know that is unlikely to happen but a guy can wish right?

Working with hidden content types and site columns in SharePoint v3

This isn’t new but I’m reproducing for my own benefit!

I blogged ages ago about needing to get at the Event content type in SharePoint which by default is in the _Hidden category

I normally get at the Event content type by creating a calendar, enabling content types and then following the links until I uncover the content type

Today I needed to use a Wiki Page as a content type but my usual method didn’t work as you aren’t able to manage content types in a wiki library

When editing a content type your URL will look something like this (I’m doing this on a SBS 2008 SharePoint setup)

http://companyweb/_layouts/ManageContentType.aspx?ctype=0×0102

The ctype parameter identifies the content type (in this case the Event type)

All I needed to do then was find the ID for the wiki page content type and a quick google search made this the easy part

There were a ton of pages with this information but i used this one as it also provided them in a useful CSV and XLS format (thanks!)

The table below shows all the items in the _Hidden group. The full list is on the post i mentioned but I’m only interested in the hidden ones as i can get at the rest via the UI

*(these lists also include types for MOSS)

There is also another post showing hidden Site Columns (here) which also has the spreadsheet formats (though i couldn’t get the page to open while i was writing this and ended up using Google’s cache of the page)

Once I’d changed the group of the wiki page content type i then created a new wiki page content type that inherited from the original to create my own base wiki type (as I don’t think you should mess with the built-in types!)

I was able to use the wiki content type in regular a document library so i could mix wiki pages with regular documents (which was the whole point of the exercise!)

SharePoint Designer – Free?

There has been a rumour kicking around the last few weeks that Microsoft are to make SharePoint designer available for free

(Posts from various SharePoint based blogs here, here and here)

Any software announcement scheduled for April 1st always makes me suspicious (a couple of the blog posts I’ve mentioned have updated to say it’ll be the April 2nd now) but the post that made me think this could be the real deal was from the Lady Licensing blog (If you haven’t already subscribed to her blog do it…it’s very helpful!)

Product Licensing Changes to SharePoint Designer, PerformancePoint Server and Forms Server – April 2009

The post outlines that SharePoint Designer will become free (if you have software assurance you can get a copy of Expression Web 2 in replacement) and also says that Performance Point Server 2007 and Forms Server 2007 are to be retired

I’ve got a few thoughts on this so lets start with SharePoint Designer

If this is true then i think it’s a move that makes sense. When your working with SharePoint you have three ways to customise your site

1) Through the browser

2) With SharePoint Designer (SPD)

3) Using developer tools such as Visual Studio

SharePoint designer is sort of pitched as an advanced end user tool but in reality (based on my own experience) someone with specific knowledge would do this on your behalf (such as your IT consultant)

If you want to do anything more advanced then you’d get developers involved who would side step SPD completely so I’ll bet Microsoft aren’t selling too many copies of SPD and I imagine the people who are at this level are using Microsoft Office SharePoint Server (MOSS) as opposed to Windows SharePoint Services (WSS) so they are already paying customers

Since WSS is free making it a part of that adds to the overall feature set and wont make too much of a difference to the Microsoft bottom line

Just for the ability to create workflows alone i think SharePoint Designer is an excellent tool, that’s before you look at any of the other stuff it can do

While it can be quite a scary product (people worry about “breaking” their SharePoint sites) it is extremely powerful and worth getting to know (Penny Coventry’s book on the subject is great!)

Forms Server 2007 and PerformancePoint

According to the information on Emma’s post both of these products are being retired

PerformancePoint is being rolled up into the next release of Sharepoint and will be called “PerformancePoint Services for Sharepoint” (see here for official details). They did the same thing with Content Management Server which helps build on the feature set of MOSS

Forms Server though is an odd one which falls under the same category of InfoPath

I personally think Microsoft missed a trick with InfoPath and the small business market. InfoPath is a very useful tool when combined with SharePoint but it’s one of those products no one knows about. (I have the same argument about OneNote – but that’s for another time)

This is because of the way it is bundled. InfoPath 2007 comes with Office 2007 Professional Plus and Office Enterprise (we’ll just pretend “Office Ultimate” doesn’t exist) which means buying it under a volume license agreement. While we do have customers who buy office in this way lots of them prefer to buy their office software OEM with a new PC

You can buy InfoPath separately but that means boxed product (expensive) or via volume licensing but if you going to do that you may as well get Office Professional Plus right?

So if you don’t already know InfoPath lets you create electronic forms which are much more powerful than anything you could create in Word (which is where most people create their forms!)

They also integrate nicely with SharePoint so you can unlock the data within them and when combined with workflows you can create powerful solutions (timesheets and expense claims i always think are a good examples)

The downside to this is that obviously you must have InfoPath installed to work with the forms. This is where Forms Server steps in. You can create a form and then convert it into a web form so all you need to fill it in is a browser (you can even create forms for your mobile device!)

This functionality is “baked in” to MOSS so the product is aimed for those people using WSS but with the need for web based forms. Generally this means smaller businesses so as an example what costs more?

10 user system

1 copy of InfoPath  + Forms server 2007 and CALs

or

10 copies of InfoPath

I imagine Microsoft aren’t selling too many copies of Forms Server so i can see why this is being retired. I couldn’t find any official announcement so I’m not sure if that’s the end of Forms Server and if you want that functionality you’ll have to move to MOSS or if we’ll see some Forms Server functionality in the next version of WSS (fingers crossed!)

It’s April 1st tomorrow so i guess we’ll at least find out something then!

Useful Commands – CmdKey

A couple of weeks ago I saw a post on Mark Wilson’s blog talking about CMDKEY. At the time I thought “that’s one to remember” but wondered if I’d ever actually need it

Turns out today I did!

CmdKey is used for managing cached credentials

Someone called today as they were working with the documents on their SharePoint site and the “created by” field was a different user to the one who was actually logged into the PC

What had happened was when the user had been created they initially didn’t have permissions to access the site so they were presented with a username and password prompt

They put in a username and password combination that DID have permissions to the site and put the tick in the box for “remember my password”

So I used the following commands

CMDKEY /List

This gave me a list of the cached credentials on this PC

I found the one I wanted and used

CMDKEY /Delete:TARGET – where target was the name of the server/site I needed to delete

I closed the browser and restarted and all was well!

The CMDKEY command is included on Server 2003 / Server 2008 and Windows Vista but NOT XP

However, you can copy it from a server 2003 machine onto an XP machine and it’ll work just fine

Creating a multi-column search form in SharePoint v3 with SharePoint Designer

This post isn’t anything ground breaking or new but I remember the first time I tried to do this! Also every time I do it I always forget something

The requirement here is simple

Create a form that allows the user to enter some search criteria and have it search across specific, multiple columns

For example,

There may be a Customers list (which may not be too different to the Northwind database in Access 2007…wink, wink). This list holds lots of details including the customers name, address, phone number, post code, current balance, account number

When the customer calls you need to be able to call up their details quickly. To identify the customer you may ask for an account number, a post code, etc (even just a part of that information – first three digits of their post code for example) which is why this needs to work across multiple columns

You may be thinking “why don’t you just use the built-in search functionality?”

The built-in search functionality in SharePoint is still a great tool (and we’ll leave Search Server Express out of this for now!) but the main problem in this scenario it doesn’t work as we as I only want to search on specific fields

So to get this running first create a Web Part Page

Click “site actions”, “create”, “Web part page”

Enter a name for your page (We’ll use “customer search” here), select a layout template and select a document library to store the page in and click OK

Once you’ve done that you will be taken to the new page. It will be in edit mode ready for you to add some web parts!

Click “Add a web part”, select a “Form Web Part” and click OK

If you just wanted to search on one specific column you could add your list to the page, setup a web part connection and never have to touch SharePoint designer!

But why do things the easy way?

Open SharePoint Designer, then open your site and then open your Customer Search Web Part page (if you want to take a copy of that file, now is the time to do it before we mess with it!)

Click on the “Data View” menu and select “Insert Data View”. The data source task library task pane will open and ask you to choose a list.

Select your customer list and click “Show Data”

Hold down your CTRL button and pick some fields. I’m going to pick

First Name, Last Name, Company, Business Phone and Zip/Postal Code

Then click the “Insert Selected Fields as” menu and select the “Multiple Item View” option

  The data view will then be inserted into your page.

Click the > button for the data view so you get the common tasks menu and select the “parameters” option. In the Data View Parameters dialog select “New Parameter”

Give your parameter a name (i’m using “SearchValue”) and change the parameter source to CONTROL and the CONTROL ID to the ID of your FORM WEB PART (If your following these instructions or haven’t changed the ID it’s more than likely T1) and click OK

Then, from the command task menu of the data view select “Web Part Connections”. In the first page of the wizard select the “Get Parameters From” option.

On the second page leave the default “connect to a web part on this page” option selected

On the third page set the target web part to be form web part and the target action as “Provide form values to”

On the fourth page look for your previously created parameter on the right and match this up with your form web part ID

On the final page click finish

The last thing to do is to tell the data view to apply the filter. On the common tasks menu select the FILTER option

Add as many clauses as you need to customise how your search works. You can even work with fields that aren’t actually displayed

My example looks for text that contains my search criteria in any fields (using the OR option)

Remember to pick your previously created parameter as your value

Save the page!

Go to the page using your browser and test it out

Other things you may want to go and change on the common tasks menu include the sort order and the amount of items displayed per page

Finally, what point is there in displaying a limited set of fields if we can’t drill into the full record?

On the common tasks view click “Show Example Data”

Click on the one of the fields you want to have as your link and change the format as option to HYPERLINK

Select “Hyperlink Options”

Change the “text to display field” and the address field (you can you the function builder to help )

Build the Address field so that it looks similar to the example below

/{@FileDirRef}/DispForm.aspx?ID={@ID}

There may be a better way of doing this but it works for me. If you know of a better way please let me know!

You can also change DispForm to EditForm if you want to be able to go straight to editing the record

Keep an eye on the “Text to display field” as this tries to mirror the address field and this isn’t always what you want

Save the page again and marvel at your finished project!

The northwind demo data is a bit pants for testing this to be honest but it does the job

As you know the latest version of SharePoint (v3 / 2007) is going to be a part of SBS 2008 and there is a lot of potential for helping your clients make the most of the product so get learning!

SharePoint, Incoming Emails, Automatic Workflow’s, SORTED!

Yesterday’s post talked about how I’d tried to apply the Infrastructure Update for WSS so that I could get workflow’s to start automatically from incoming emails (history here and here) and I ended up killing my server!

This morning I applied service pack 1 and then the update (after taking a complete backup of course    )

I was expecting it to all work this time around as I’m basically doing it on a brand new install and I’m pleased to report the updates applied ok!

So this then led me back to the original reason for applying the update in the first place.

In the Description of the Infrastructure Update for Windows SharePoint Services 3.0: July 15, 2008 page there was another link, Issues that are fixed in Windows SharePoint Services 3.0 by the Windows SharePoint Services 3.0 Infrastructure Update in here there was one specific issue I was interested in

A declarative workflow that is configured to start automatically when e-mail enabled items are created does not start automatically after you install Windows SharePoint Services 3.0 Service Pack 1

This describes the exact problem I was having.

When an e-mail arrives from an external source they are submitted as the system account. SP1 stopped the service account from being able to run workflow’s automatically

The fix is to use impersonation. The workflow will run under the account of the user who initially created the workflow association. As there may also be security implications to consider they don’t turn it on by default

The KB article I mentioned above (KB953289) states the following

“To enable workflow’s to start using the impersonation scheme, a network farm administrator must understand the impersonation scheme and its implications. The network farm administrator must enable the workflow with the following command by using the Stsadm.exe command-line tool:

stsadm -o setproperty -pn declarativeworkflowautostartonemailenabled -pv true

Administrators can also create a special user account for creating workflow’s that all workflow authors can use. By doing this, administrators can prevent other people from impersonating specific workflow authors.”

If your happy with that disclaimer run the command on your server

I’ve done it and I can confirm it works!

Finally!!

The joy after the pain

You can download the update from here – Infrastructure Update for Windows SharePoint Services 3.0 (KB951695)