Archive | SharePoint

Start Visual Studio form PowerShell

I am moving on to a new project – our migration from SharePoint 2007 to 2010. First thing I wanted to do was to upgrade my Solutions/Features from 2007 to 2010. I installed Visual Studio and started looking at how to recreate my Delegate JQuery Control (more on that later). The first thing I found annoying was that every time I added an “Empty SharePoint Project”, I received a warning “This task requires the application to have elevated permissions”. That is annoying. Since I usually leave a PowerShell prompt running as administrator (It has a red background to distinguish it), I wanted to quickly open VisualStudio from that PowerShell prompt. Here is my wrapper script to open VisualStudio 2010 from PowerShell:

function JBMURPHY-Start-VisualStudio {
Start-Process "C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe" -workingdirectory "C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\"

Reporting Services report from related data in SQL and a SharePointList via SSIS

This is an older project that I wanted to talk about. I worked on this last June. It was a longer post, so i procrastinated.

I wanted to create a “mash up” of data from a SharePoint List and a set of SQL tables. I tried several things, somehting that should be easy, was not (at least for me).

  • First I tried using SharePoint as a DatasSource in a SQL Server Reporting Services. That worked, but I could not figure out how to combine that with a different datasource in a report. Basically I could not figure out how to use “SQL Joins” between two different data sources in SSRS.
  • Second I decided to look into combining my data inside SQL server. If I can establish my relationships inside SQL, then the report would be easy. The most efficient way (AFAIK) would be to use a liked server to the SharePoint list. That way the data stays in the SharePoint list, and I am just querying it. I found several articles talking about ODBC or OLEDB connections to a SharePoint list. I just could not get it work. I saw another article strongly recommending against it (I can’t find that link right now). So I scrapped that idea.
  • I Finally settled on grabbing the data out of the list and putting into a SQL Table. I am not a SQL guru, and I could not figure out how to use a temp table with SSRS, so I ended just adding a table. This table would be an exact replica of the data in the SharePoint list (not many rows). The data in the table would be erased and then repopulated every X number of hours. The question was how to get the data into SQL.

I decided to use SQL Server Integration Services (SSIS) to pull the data from the SharePoint list. I found the add-in to SSIS that made it easy to get data from a SharePoint List – SharePointListAdaptersSetupForSqlServer2005.msi. I ended up with a SSIS control flow that looked like this:

The first part deletes the contents of the destination table, second is the DataFlow piece:

The Data Conversion piece changes a “double-precision float” to a “unicode string”. All this is put together and creates a dtsx file (LoadSPList.dtsx) that can be executed by a Scheduled task:

DTEXEC.EXE /FILE “C:\LoadSPList.dtsx” /CONNECTION “connectionName”;”\”Data Source=DBName;Initial Catalog=DBName;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\”” /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING EWCDI

Finally, I just to created a report that contained a SQL query to relate the two different SQL tables.

Fun project, using three different technologies, SSIS, SQL and SharePoint.

Pseudo workflow approval with out SharePoint Workflows.

I wanted to have a form that a person would submit (for an example a request of some type) and then it would be approved by the appropriate people. I find the built in workflow functionality too limiting and too difficult for users, so this is what I came up with:

I would have one list that contained fields for both the user to enter data and for each approver to approve or disapprove. In addition, there would be a “Final Approval” field that cold only be set once everyone approved or disapproved and an “I agree” to terms checkbox.

First up, I hid all the fileds on the NewForm.aspx that were related to the approval process. Just the data that was to be collected was shown. I did this with jquery and a CEWP:

<script type="text/javascript">
$('tr:has(select[title="User 1"])').not('tr:has(tr)').hide();
$('tr:has(select[title="User 2"])').not('tr:has(tr)').hide();
$('tr:has(select[title="User 3"])').not('tr:has(tr)').hide();
$('tr:has(select[title="User 4"])').not('tr:has(tr)').hide();
$('tr:has(select[title="User 5"])').not('tr:has(tr)').hide();
$('tr:has(select[title="Final Status"])').not('tr:has(tr)').hide();
$('nobr:contains("User 1 Comments")').closest('tr').hide();
$('nobr:contains("User 2 Comments")').closest('tr').hide();
$('nobr:contains("User 3 Comments")').closest('tr').hide();
$('nobr:contains("User 4 Comments")').closest('tr').hide();
$('nobr:contains("User 5 Comments")').closest('tr').hide();

Once this is submitted by the user, and email is sent by a SharePoint workflow (I still don’t like them) telling the approvers to look at the request and approve it (link to the edit page in the email)

Then on the edit page, I used the jquery below to hide the “I agree” so the user could not go back and change it. Also I hid the final approval until all drop downs (Approvals) have changed from “Pending” to Approved or Declined. Obviously there would be security issues, but this is a small use case and we weren’t worried about malicious users (famous last words) – people were on their honor.

<script type="text/javascript">
$('tr:has(select[title=IAgree])').not('tr:has(tr)').append('<tr><td nowrap="true" valign="top" width="190px" class="ms-formlabel"><H3 class="ms-standardheader">I Agree</H3></TD><td valign="top" class="ms-formbody" width="400px">'+$('select[title=IAgree] :selected').text()+'</td></tr>');

$('select :selected').each(function(){
if($(this).text() == "Pending") {
$('tr:has(select[title=Final Status])').not('tr:has(tr)').hide();


Using ajax to query XML in SharePoint Doc Lib, for use in a form’s input autocomplete

Long title, but I wanted to get across what I was trying to do. If I had and XML file in a document library (you could mail enable the doc lib, and send XML from a query in SQL server using the “FOR XML” statement!), could I use jquery to add Autocomplete values to an input field in a newform.aspx? Ended up being not that difficult.

  1. Once the ajax call sucessfully retrievs the xml file, the parseXml routine is called.
  2. Seems there is an issue that IE will not think the file is XML, but rather as txt, so there some quick code to load the file as XML
  3. Then I just look in the file and grab the values I want, and append them to an array
  4. Then just set the array to be used as the autocomplete source
<script type="text/javascript">
$(document).ready(function() {
	type: "GET",
	url: "http://URL/To/xmlfile.xml",
	dataType: ($.browser.msie) ? "text" : "xml",
	success: parseXml
function parseXml(data)
    var xml;
     if (typeof data == "string") {
       xml = new ActiveXObject("Microsoft.XMLDOM");
       xml.async = false;
     } else {
       xml = data;
 	var results = []; 
		var ClientName = $.trim($(this).find('Name').text());
		var ClientCode = $.trim($(this).find('ZipCode').text());
		results[results.length] = ClientName + "(" + ClientCode + ")"

	source: results,
	minLength: 3

jQuery, SharePoint Web Services and adding thumbnail to a List

We have a SharePoint list with thumbnails attached to the list items. The request was to show a thumbnail of the image that is attached. I believe this can be done via SharePoint designer, but I thought jQuery would be easier. I added a Content Editor WebPart to the top of the page. Next I inserted the jQuery code below. This code queries the SharePoint Web Services for every item (not tested on a large list), and then loops through them. If there is an attachment, jQuery takes the ID and the path to the attachment and replace the ID column (has to the first column) for the item with the tumbnail image.

The tricks were:

  1. The soapEnv has to have “<IncludeAttachmentUrls>TRUE</IncludeAttachmentUrls>”
  2. Finding the right selector to get to the ID column:
$(" tr:has(").find("td:first").filter(function() {
			return $(this).text().toLowerCase() == ID;
			}).html("<img src='" + url[1] + "' width=150 height=100 />");

Here is the rest of the code:

$(document).ready(function() {

function querySPWebServices() {
        var soapEnv = "<soapenv:Envelope xmlns:soapenv=''> \
		<soapenv:Body> \
		<GetListItems xmlns=''> \
		<listName>Projects</listName> \
		<viewFields> \
		<ViewFields> \
		<FieldRef Name='Title' /> \
        <FieldRef Name='Body' /> \
		<FieldRef Name='ID' /> \
		<FieldRef Name='Attachments' /> \
		</ViewFields> \
		</viewFields> \
		<query> \
		<Query /> \
		</query> \
		<queryOptions> \
		<QueryOptions> \
		<IncludeAttachmentUrls>TRUE</IncludeAttachmentUrls> \
		</QueryOptions> \
		</queryOptions> \
		</GetListItems> \
		</soapenv:Body> \

        	async: false,
            url: "",
            type: "POST",
            dataType: "xml",
            data: soapEnv,
            complete: processResult,
            contentType: "text/xml; charset=\"utf-8\""
function processResult(xData, status) {
	$(xData.responseXML).find("z\\:row").each(function() {
		if ($(this).attr("ows_Attachments") != 0) {
		var url = $(this).attr("ows_Attachments").replace(/#/g, "").split(';');
		var ID = $(this).attr("ows_ID");
		$(" tr:has(").find("td:first").filter(function() {
			return $(this).text().toLowerCase() == ID;
			}).html("<img src='" + url[1] + "' width=150 height=100 />");

Hide a field in a SharePoint edit form based on other values

We have a form (list) that we want to have everyone edit and give comments. Once everyone has edited the item, we want an “overall status” field to trigger a workflow (send extended email). Be we did not want the overall status to be changed until everyone has chimed in. To achieve this, we created a list and there was a dropdown with everyone’s name to Approve the item. The default of the dropdown was “Pending”. I used the jquery below to hide the “overall status” until there were no dropdowns that said “Pending”

$('select :selected').each(function(){
if($(this).text() == "Pending") {
$('tr:has(input[title=Overall Status])').not('tr:has(tr)').hide();

The hide code is code that Paul Galvin published here.
jquery is cool.

How to make a field in a SharePoint Edit form readonly

Paul Galvin showed how to hide a field in a SharePoint. I wanted to use this code to make a field “read only” once a from has been submitted. Forgetting about DataSheet view (for now), we can put a Content Editor Webpart on the top of the EditFrom.aspx page (to add a CEWP to the top of the edit from, append the URL to this -> EditForm.aspx?ToolPaneView=2) and include his code:


This will hide the filed based on the Column Name (Disclaimer). Taking this a step further, we can get the selected value (a drop down box in this case), and append a column after the hidden column with some formatting, a column name and the selected value. Effectively making the column readonly.

<script type="text/javascript">
$('tr:has(select[title=Disclaimer])').not('tr:has(tr)').append( '<tr><td nowrap="true" valign="top" width="190px" class="ms-formlabel"><H3 class="ms-standardheader">Disclaimer Accepted:</H3></TD><td valign="top" class="ms-formbody" width="400px">'+$('select[title=Disclaimer] :selected').text()+'</td></tr>');

How I created a “Copy to new item” functionality for a SharePoint list – Part 2

Second part. First Part can be found here. On the second page (NewForm.aspx), I grabbed out of the querystring the values for the source list item and the name of the list.
I used this person’s query string parser.

Then I used the following SOAP query :

$(document).ready(function() {
 var sourceID = getQuerystring('SourceID');
 var listName = getQuerystring('ListName');

 var soapEnv = &quot;&lt;soapenv:Envelope xmlns:soapenv=''&gt; \
&lt;soapenv:Body&gt; \
&lt;GetListItems xmlns=''&gt; \
&lt;listName&gt;&quot; + listName + &quot;&lt;/listName&gt; \
&lt;viewName&gt;{GUID}&lt;/viewName&gt; \
&lt;viewFields /&gt; \
&lt;ViewFields /&gt; \
&lt;query&gt; \
&lt;Query&gt;&lt;Where&gt; \
&lt;Eq&gt; \
&lt;FieldRef Name='ID' /&gt; \
&lt;Value Type='Integer'&gt;&quot; + sourceID + &quot;&lt;/Value&gt; \
&lt;/Eq&gt; \
&lt;/query&gt; \
&lt;/GetListItems&gt; \
&lt;/soapenv:Body&gt; \

 async: false,
 url: &quot;;,
 type: &quot;POST&quot;,
 dataType: &quot;xml&quot;,
 data: soapEnv,
 complete: processResult,
 contentType: &quot;text/xml; charset=\&quot;utf-8\&quot;&quot;

 function processResult(xData, status) {
 $(xData.responseXML).find(&quot;z\\:row&quot;).each(function() {
 $(&quot;select[title='DropDown']&quot;).val($(this).attr(&quot;ows_DropDown&quot;)).attr(&quot;selected&quot;, &quot;selected&quot;); 

In the last three lines I changed the input box, textbox and dropdown boxes to their values in the results fromt the SOAP query.

Kinda fun. Just need to figure out how to do check boxes!

How I created a “Copy to new item” functionality for a SharePoint list – Part 1

I wanted to create a “copy to new item” functionality for a SharePoint list.

Steps I came up with:

  1. The first thing I had to do was add a link to the context menu (I learned that it is called an ECB) and have it point to the NewForm.aspx.
  2. Once I got that, I could append a querystring variable to the url that contained the “Source ID” of the item to copy and the name of the current list.
  3. Then I would grab that querystring value on the other side – in the NewForm.aspx page
  4. Next I would use that variable to query the SharePoint List via SOAP
  5. Inject the results the  into the form

Here is my script (add to a CEWP, I already have the jquery pointers to google in a Delegate control)

<script language="javascript">
function Custom_AddListMenuItems(m, ctx) {
var editURL = window.location.protocol + "//" + + ctx.listUrlDir + "/NewForm.aspx?SourceID=" + currentItemID + "&amp;ListName=" + ctx.ListTitle;
 CAMOpt(m, "Copy To New Item" ,"window.location=('" + editURL + "');" , "/_layouts/images/Copy.GIF");

More to come.