Erhard Smit

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.