Erhard Smit

Unless you’ve been living under a rock, you’ve probably noticed that Microsoft SharePoint is on a serious roll. More and more companies and government institutions are implementing SharePoint and it seems that SharePoint skills are in shortage.

I get a number of enquiries from companies every other day to find out if I am available for SharePoint Development. I always respond: “Yes, but not for full-time employment.” and then hear the disappointment on the other end followed by the hang up tone.

Why are companies so interested to employ a fulltime developer when they really just need a freelance SharePoint developer to configure and customize their Portal or Collaboration needs at the beginning? Thereafter they need the occasional customization and in-house training for a few technical personal to attend to the basic SharePoint administration tasks.

It may be the case that many businesses would like to retain the skills so they are ensured of support later, but then again, freelancers aren’t going anywhere?

I am in the freelancing business so obviously I recommend contractors to implement your SharePoint requirements. But, whether you are looking for a fulltime or part-time SharePoint developer, here are a few things you need to look out for:

• A good SharePoint developer must have professional work experience as an ASP.NET developer in C# with extensive Microsoft SQL Experience. Many people will differ and say this is not a requirement, but my experience tells me otherwise.
• Experience with workflows and business process automation.
• Microsoft SQL Reporting Services
• XML and CAML experience to develop web parts, anything beyond “Hello World”
• Web Development experience and understanding of good UI design principles. SharePoint offers many great UI features out-of-the-box, however you need to understand to use them effectively.
• Obviously, previous SharePoint experience.

I’ve been using SharePoint for a number of projects, ranging from smaller team collaboration sites to major company portals. If you are looking for an affordable SharePoint developer in Pretoria, Joannesburg or Gauteng region contact me today.

This may be common knowledge to all the SharePoint specialists but I battled to find the correct answer. In some cases you may not want to categorize documents in different folders or libraries so you end up with one huge list of documents. I encountered a similar scenario where the client wanted all documents listed in one library for over 100 branches. They wanted the ability to browse through either all documents or view documents per branch. The obvious solution might be to create a view but then you would have to create 100 views for each branch which sounds like a lousy solution.

To overcome the problem I found two very handy parameters that you can include as part of the URL for any Document Library, List of View called FilterName and FilterMultiValue. By adding these you automatically tell SharePoint to filter the results based on the column name (FilterName) and the value to filter on (FilterMultiValue).

I created a single view with the fields I needed to display but specified no filter in the view settings. To call the view I passed the two query string parameters as part of the URL that added a dynamic filter to the view to display only the documents for a particular branch:

http://server…/AllItems.aspx?FilterName=Branch&FilterMultiValue=Branch%20Name%20Here

Following a client request I decided to write this short how-to on automating processes from Optitask by reading information from a Sharepoint List.

My client created a new custom list with several fields, one of the fields was a checkbox that had to be checked during a certain stage of a business process. If the checkbox was not checked the user in the list had to be reminded via SMS message to his mobile phone every day to complete the process. Once the checkbox was ticked another SMS message would be sent to his superior informing her that the process was completed.

Instead of dealing with the web services provided with Sharepoint, I decided to dig into the Sharepoint SQL database directly to retrieve the information I would need to implement such a solution. After a few searches on Google I found that the tables of interest with the list data were called Lists and UserData.

Automating the Reminders

To automate the reminders, I would need to pull 3 fields from the list and validate on a fourth. The first three fields from the select would return the first and last name and the third field the mobile phone number of the user to remind. In there were clause I added the name of the Sharepoint List to retrieve the information from and also added a second check to return only unprocessed records.

The SELECT statement to read unprocessed records from a custom Sharepoint List:

SELECT
  dbo.UserData.nvarchar1,
  dbo.UserData.nvarchar4,
  dbo.UserData.nvarchar9
FROM dbo.Lists
INNER JOIN dbo.UserData ON dbo.Lists.tp_ID = dbo.UserData.tp_ListId
WHERE (tp_Title = 'The Name of the List')
AND (dbo.UserData.bit1 = 0) – 0 means that it’s unprocessed.

It takes two Optitask actions to automate this process, the first is a SQL Task component that executes the SQL query above and return a result set and the second a child-task to the first that send out the SMS message to the user.

I also added a trigger component from Optitask to run this task weekly on Monday mornings at 8AM.

 Automating Confirmation Messages

To automate the confirmation part of the system I need a slightly modified version of the SQL query that returns all the records that have been processed. We’ll use the monitor component from Optitask to determine if it’s a new or old record.

The SELECT statement to read processed records from a custom Sharepoint List:

SELECT
  dbo.UserData.nvarchar1,
  dbo.UserData.nvarchar4,
  dbo.UserData.nvarchar9
FROM dbo.Lists
INNER JOIN dbo.UserData ON dbo.Lists.tp_ID = dbo.UserData.tp_ListId
WHERE (tp_Title = 'The Name of the List')
AND (dbo.UserData.bit1 = 1) – 1 means that it’s processed.

By adding this SQL statement inside the SQL monitor action Optitask will create a snapshot of processed records and check for any additions on regular intervals.

If a new record is found a second child SMS action with a filter is used to send out a confirmation SMS.