‘SharePoint as a Help Desk’ On Tour–Manchester

I may have mentioned once or twice over the last couple of weeks I’d been invited to the Manchester SBSC group to deliver the presentation I gave at AMITPRO a few months ago.

So last Thursday I made the trip up to Manchester and as I’ve done every time I’ve presented to a user group I thought it’d critique myself again (in the interests of self improvement of course!)

But before that the rest of the event.

First I’d like to thank Simon Belt for organising the event and for very kindly hanging around for a couple of beers afterwards.

Next, thanks to the group members for their kind welcome and for not heckling.

Finally the other speaker of the night, Gary Duffield of QA. Garry attended WPC this year and gave a great presentation which summarised the key points that came out of the event and where Microsoft and their partners are heading in the near future. The session was very much discussion based and it was great to see that interactivity and gave everyone some good ideas to think about.

Garry also has a blog you can check out.

So onto my presentation.

On the whole I was pretty happy. I’ve had some nice follow up comments which I always appreciate.

What did I think though?

Preparation

I mentioned in my previous review that I’d used OneNote to put together an outline to work against and I was pleased with how it helped me last time. Because of the way i put it together it made the presentation much easier to pick up and and repeat (it’s the first time I’ve ever done a talk twice!). That said the first time around I created the presentation and then delivered it shortly after I’d finished it so it meant I knew everything inside out. While I had gone back through my notes when preparing I didn’t quite know it in the same way as first time around. Luckily the notes helped so while I did forget to do a couple of things it was very easy to pick that up.

As a result I actually didn’t build-up the application as much as last time. I did do enough to get my point across though even if I did race through a lot of it. I don’t think there is a lot I can do about this other make sure I’ve got enough time as possible and know the steps as well as I can. Building a fully working SharePoint application and explaining everything along the way in a hour is an ambitious thing to do but worth racing through if it gets people thinking about how they could do it.

Speed

When I gave this presentation for AMITPRO I didn’t use a slide deck (it wasn’t actually intentional) this time since very few people in the room knew me I started with the slides to introduce myself. There weren’t many slides but because of how much content there is in the demo to get through it stole a bit of my time. I’ll bear it in mind next time if it’s a packed agenda and maybe go back to the PowerPoint free presentation Guy praised me for last time.

Demo

I know a good workman shouldn’t blame his tools but I’m going to anyway. My laptop is a couple of years old now. It’s got an Intel mobile chip (so no virtualisation extensions!) with 3GB of RAM. While it just about ran the two virtual machines I used it very obviously struggled at times and it slowed the presentation quite a bit which wasn’t helpful when I had so much to get through. When I purchased the laptop I never really expected to be using it for running virtual machines for demoing SharePoint to a user group. Times have moved on though and user group presentations aside, the ability to run virtual machines on the go has lots of other benefits. I’m not planning on rushing out and getting a new laptop but when my next hardware refresh comes around I’ll be able to factor it in.

Not too much to complain about on the whole!

It was also good to see how another user group runs. Other than AMITPRO the only other time I’d been to another group was when I visited the Bristol group for the SBS 2008 launch. That was a slightly different format because of the SBS launch so I’m counting this as my first time!

Thanks again for to everyone who attended the meeting. Would be great to hear any comments you have!

Creating an Activity Log with SharePoint

I was reminded of an old Notepad trick on LifeHacker yesterday.

Basically if you create a new text file, add .LOG as your first line and then save the file, every time you open it after that the date and time is automatically added for you.

LifeHacker then suggest you can use this as an activity log to help keep you focused on productivity. At the end of the day you can take a look and see what you’ve done with your time!

It also suggested that you added a shortcut to your taskbar so you can get to the log really quickly.

I’m all for improving productivity but decided I could improve on this a little so created something similar in SharePoint.

On our companyweb site I also have my own site that I use for testing things out and keeping track of information specific to myself (http://companyweb/andy) so this seemed the perfect place for it.

First thing was to create a new custom list called “Daily Activity Log”

Adding it to the quick launch bar means I can get at it quickly while on my site.

One new list!

Next I created a new standard view

From here I made my new view the default view, displayed only the "Created” and “Title” fields (in that order) and set the sort order to “Created" and Descending (newest on top)

Scrolling down the page I set the filter options to only show the entries for today.

After that I clicked the NEW button to display a form so I could fill out an activity entry.

You could also rename the “Title” field to something more useful but I haven’t here as this post will be long enough!

TADA!

I now have a complete list of entries from today and the “created” field gives us the same timestamp effect we had in notepad.

To mirror the LifeHacker suggestion I then created a new shortcut on my desktop.

The target is:

"C:\Program Files\Internet Explorer\iexplore.exe" http://companyweb/andy/Lists/Daily%20Activity%20Log/NewForm.aspx”

(This path will be different if using the 32-bit version of Internet Explorer on a 64-bit operating system)

You can get the URL by clicking the NEW button and copying the URL from the address bar.

I also changed the icon to make sure it looked different to the main Internet Explorer icon!

I needed to create the shortcut to Internet Explorer as you can’t pin internet shortcuts to the taskbar – Windows tries to pin them directly to Internet Explorer instead.

Once pinned to my taskbar I now have one click access to creating a new activity entry.

To improve on this some more I created another view. Instead of using a new standard view I used my previous view as a base.

I set the name to “Entry History” and scrolled down to the “group by” options and grouped by the “Created” column and set it to a descending sort (newest on top) with a 30 groups per page which effectively gives us months worth of history at a time.

I left the default grouping at “collapsed” but this is just my preference.

I only created this today so only have one groups worth of information.

All done!

This means I’ve got a tracking system for my daily activities where I can easily add entries, view what I’ve achieved today as well as go back and look at previous days.

There are plenty of ways to improve on this further. Some examples that spring to mind.

• You could add a category field if you wanted to track types of activity. You could then create views and filters around these. (You may want to see how many times you made a phone call during a day for example)
• You could add a “minutes spent” field and have SharePoint total up this field. This would let you know how many minutes of productivity you’re getting out of your day.
• You could share the list with a team of people and use the “created by” field to group and filter on usernames so you can see what each person is getting up to
• If you had personal site like I do you could drop the list onto the web part page so the current list is always displayed – dashboard style!

Just a couple there. The whole idea is that it’s quick and easy to use so you wouldn’t want to customise it too much.

Possibly a little overkill when compared to the simplicity of the notepad based solution but once this is setup it’s done and your old data is very nicely tidied away but you still get easy access to it.

SharePoint as a Help Desk–On Tour!

I’ve been waiting to post this as I wanted to make sure it was shared on the official site first!

As you may or may not know (if you read this blog or know me you should!) I can quite happily ramble on about SharePoint in a small business context for a long time. So much so that I get to stand up in front of the AMITPRO members and bore them from time to time

So after the last time Simon Belt from the North West SBS group asked if I’d go up to Manchester to bore them too and give a repeat performance and I was happy to say yes.

The meeting is on August 19th at 18:30 at QA’s training facilities on the 3^rd Floor, Westminster House, Minshull Street, off Portland Street, Manchester M1 3HU

Full details of the event are here

Let me know if you’re planning on attending?

I’ll be staying overnight so if anyone wants to chat about all things SBS-related I’ll be happy to do so.

Also if you’re in and around the North West region and have never visited an SBS user group meeting why not make this your first?

I’ve been told there will be pizza!

SharePoint as a Help Desk @ AMITPRO

Urgh….I meant to do this a lot earlier but I’ve had a crazy week since the AMITPRO meeting last week. I’ve got a back log of posts I want to put together but between holidays, hospitals and a couple of major client projects I’ve been at full tilt since then.

Anyway, back to the point!

Last time I spoke at an AMITPRO meeting I posted a brief “critique” of myself so I could figure out where I went right and wrong so I thought it was worth doing again.

First of all though a big thank you to everyone that attended!

We had a really great turn out and as well as our regular crowd there were a lot of new faces as well as appearances from some familiar faces we haven’t seen for a while. Thanks everyone for making the effort.

So what did I think?

Demo – Something I’d mentioned in my last critique was that I’d wanted to have a lot more structure in the demo itself. This was even more important subject matter and I’d used OneNote to great effect to give myself an outline to work against which meant everything went far more smoothly.

Speed - I didn’t quite feel as rushed as last time. By that I mean myself. I really did go through the demos quite quickly because I had a lot to try and do in a short space of time but I felt a lot more relaxed when speaking which will be down to experience and the fact it’s not the first time I’ve presented to the group.

Slides – Even though Guy very kindly made it look like I’d dropped the dreaded slides I had put together a very small deck that I didn’t get to use. I was still running the Office 2010 beta on my laptop and when I tried to open the file I got an error. Because I was literally minutes from speaking I just made the decision to drop them. When I checked it again afterwards it turned out I could have clicked through the error and used the slides. Which brings me onto my next point.

Organisation (1)– There had been a mix up at the venue about the room we’d booked and we were in a room for 10-15 with almost 30 people. I’d already started to setup in the smaller room when we needed to move. Also since I’m involved with the organisation of the event I had to help sort the new room,  get everyone moved and setup again. I then ended up speaking first instead of second so I was in a massive rush to get started as we were now running late.

Organisation (2) – While the previous point was a little out of my control this wasn’t. I’d setup my demo on my home setup with the intention of copying it to my laptop I’d use on the day. On the morning of the meeting I took my virtual machines to the offices with intention of tweaking my machines and going over my notes. We had such a busy day at the office that it just didn’t work out that way.

At around half three I realised I needed to setup a scheduled task to make everything easier. However, my server machine was running like a dog and generally unresponsive. I’d run a couple of VM’s on my laptop before no problem but was now panicking. After a bit of investigation I found a couple of articles that indicated that laptop hardware can go into reign in the CPU when VMs are running as it thinks the system is actually idle (though I don’t know if this actually true!)

So in my panic I uninstalled Virtual PC 2007 and installed Windows Virtual PC so I could make a setting change that would overcome this. At the same time I totally forgot that the VM’s aren’t directly compatible so it was almost four thirty (i.e time to leave!) and I had no way to run the demo! I quickly got rid of Windows Virtual PC, loaded Virtual PC 2007 back up and left for the meeting.

On the way I realised in my rush I hadn’t copied my slide deck onto the laptop. Once at the meeting I had to fight a dodgy 3G signal to try and remote back to the office and grab the file. My intention was to get everything prepped while the other speaker was on but I ended up going on FIRST!

I was quite lucky in that other than the slides everything from a demo perspective went to plan.

The lesson to learn was that I should have included the actual laptop the demo would run from as part of demo build instead of just assuming it would all work as expected.

Thankfully I’d prepared my demo and notes in such a way that I was able to get back on track quickly even though I was quite flustered at the beginning

All in all I was pleased with how it went.

I still don’t think I’m a natural presenter but hopefully anyone who was there learning something. If they did then it was mission accomplished.

I got some very nice comments from people in attendance and via Twitter and e-mail the following day which were much appreciated.

There was a TON of stuff I’d actually prepared that I just didn’t even cover because of time and the fact I didn’t want to go on for too long and have everyone lose interest. I joked during the presentation that I can talk for HOURS about SharePoint but in all honesty I was being serious!

Andy and SharePoint @ June AMITPRO Meeting

Yeah i know….me again.

We’ve had a speaker drop out at short notice so I’m going to speaking at AMITPRO this month. The topic is one you’ll probably know is something I quite like talking about – SharePoint!

My presentation will be titled – SharePoint as a Helpdesk

I’ve had the idea for the while so it’s something Guy and I have been sort of keeping in reserve just in case we had any issues with speakers so I finally get to roll it out this month.

So what exactly do I mean by SharePoint as a Helpdesk?

One thing I’ve found when talking to other partners is that because SharePoint is such a flexible (large?) product is that many of them find it difficult to get into quickly. I can understand this as it can be an awful lot to take in.

However, I really do think many partners are missing out on revenue opportunities because of this! Now that don’t necessarily mean you need to instantly became a master in SharePoint but if you have a basic understanding of what it can do then you can always partner with a company that does if you have a client who might benefit from such a solution.

Partnering with other companies is something SBSC partners are good at!

So….what I’m going to try to do in the time I’ll have is to create a basic helpdesk. All IT companies know what a helpdesk is and what sort of functionality it needs so we can create a quick and dirty SharePoint application that showcases what SharePoint can do.

I’ll be touching on,

Lists and Libraries.

Columns and Views.

Content Types and E-mail Integration.

Web Part Pages and Web Parts.

SharePoint designer and Workflows.

Office integration and Mobile access

And if there is time another topic I like to shout about…InfoPath

Time depending of course!

Our other speaker is Dell Quinn from Microsoft.

She’s going to talk about the changes to the Microsoft Partner Network and how it’ll impact Small Business Specialists.

All this as well as the usual pre and post event banter that goes on at any AMITPRO event!

As always guests are more than welcome. If you’ve never attended a user group event we’ve love for you to come along.

If you’d like to know more than leave a comment here, visit the AMITPRO site or get in touch with me directly (or on Twitter)

Look forward to seeing you there!

SharePoint as a Twitter Client. Sort of – Part Five

In part four I mentioned that there would be a problem in the future with this solution.

In the second part of this series we created a simple function that generated a HTTP request to send to the Twitter API.

As part of that request we sent a username and password.

This known as “basic authentication”

Unfortunately just after I got this working the Twitter development team announced on google groups they are dropping support for basic authentication.

our plan is to turn off basic authorization on the API by june 30, 2010 — developers will have to switch over to OAuth by that time.  between now and then, there will be a *lot* of information coming along with tips on how to use OAuth Echo, xAuth, etc.  we really want to make this transition as easy as we can
for everybody.

Twitter even created a site called “Countdown to oAuth”

I can understand why Twitter have done this as it’ll make the system far more secure.

Unfortunately this breaks my solution so I’ll need to take a look to see how i can use a different authentication method.

I’ve got till the end of June so hopefully I’ll get something sorted soon!

Just one final post on the way to wrap this up.

SharePoint as a Twitter Client. Sort of – Part Four

In part three I said there was bit of functionality left to clear up.

We have to run the process manually.

We actually did a little bit of the ground work in part two.

One of the pieces of code was the main function that brings all the bits and pieces together – it was called “Fetch_Tweets”

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

Take a look at the first word of this code block.

PUBLIC

By adding this we can reference the function outside of Access so I put together a vbscript to run the function. (essentially…programmer types will argue this isn’t what Public means but for the layman it’ll do here!)

Dim TweetDB

TweetDB = "C:\TweetPoint\Twitter.ACCDB"

Dim myAccess
Set myAccess = CreateObject("Access.Application")

myAccess.OpenCurrentDatabase TweetDB, False
myAccess.AutomationSecurity = msoAutomationSecurityForceDisable

myAccess.Run("Fetch_Tweets")

myAccess.CloseCurrentDatabase
myAccess.Quit acQuitSaveNone
Set myAccess = Nothing

So just breaking this down.

The vbscript is essentially doing exactly the same as if you were running this manually.

First we create an instance of Access. (The same as opening the application)

Then we open the database we need.

Then we tell Access to run our “Fetch_Tweets” function.

The we close the database and close the application.

Simple!

The only caveat here is macro security. If you have your security in Access set to high or prompt then the vbscript won’t work.

Since I’m just doing this a proof of concept I turned the macro security off.

You’ll need to make your own decision about how to deal with this.

One suggestion though is to look at something called “Trusted Locations”

This article explains how to set that up.

We can then take this vbscript and run it as a scheduled task to run as often as you need.

The major downside to this is that the system that will run the vbscript will need to have Access installed.

I don’t think the Access runtime would be enough (I only tried it very briefly)

So that’s all of our basic functionality problems sorted

We can ask Twitter to return the tweets for a given person, have them displayed in a SharePoint list automatically and it will be intelligent enough to not give us duplicates.

I’ll follow this up with a couple of posts that shows how to add a little more polish and cover a problem that I’ll need to look at going forward. I haven’t figured out what to do about it yet though!

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!