Excel Services and External Data Ranges

Excel Services cannot render a workbook that contains external data ranges displayed as a table, as mentioned in the documentation.

I had to display a couple of fields contained in a few database tables, in order to use them as a value to be compared against.

The solution: I connected the table as a PivotTable report, with no grouping. It shows the same numbers, but it can be processed by Excel Services on the SharePoint server side.

CKS:IEE Forms-Based Authentication is now in Pre-Beta

Well, it’s been few months ago since I joined Community Kit for SharePoint (CKS) project on CodePlex. To me it seems like yesterday…

Finally we begin to see the fruits of our hard work. CKS: Internet-Extranet Edition (CKS:IEE) has been launched in pre-beta version, with the most of the Forms-Based Authentication (FBA) up and running. I worked on a few webparts (Change & Recover Password), ULS logging module and user import feature (which will be added really soon).

If you want to try it out, you can find the setup with installation guide on https://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=CKS&ReleaseId=7675

I’ll soon post some screenshots and walkthroughs.

Add JavaScript Date Validation into List Item forms

Another undocumented piece of SharePoint.

I want to validate two fields on a new list item form by invoking JavaScript custom function. They are two date fields and I want to ensure that the end date can’t happen before the start date. My first idea was to attach a validation function on the onclick event of the Submit button.

I started by inspecting the generated HTML of the form. The Submit button already has a onclick() code which is:

if (!PreSaveItem()_) return false;WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions("ctl00$ctl13$g_740c1963_b0da_4b45_9b71_0dcca5d082b0$ctl00$toolBarTbl$RightRptControls$ctl00$ctl00$diidIOSaveItem", "", true, "", "", false, true))

Searching in the SharePoint JavaScript files in the LAYOUT folder, I found the definition of PreSaveItem function in FORMS.JS file. It simply invokes PreSaveAction function, if defined.

Finally, it was just a matter of inserting a custom function named PreSaveAction in a <SCRIPT> block of the NewForm.aspx (and EditForm.aspx). I also used the date parse code from this forum.

The code I put in NewItem.aspx is like this (note that I use getTagFromIdentifierAndTItle function from my older blog entry):

function PreSaveAction()
    var date1 = getTagFromIdentifierAndTitle("INPUT","DateTimeFieldDate","Contract Date"); 
    var date2 = getTagFromIdentifierAndTitle("INPUT","DateTimeFieldDate","Contract End Date");
    var arrDate1 = date1.value.split("/");
    var useDate1 = new Date(arrDate1[2], arrDate1[1]-1, arrDate1[0]);
    var arrDate2 = date2.value.split("/");
    var useDate2 = new Date(arrDate2[2], arrDate2[1]-1, arrDate2[0]);
    if(useDate1 > useDate2)
        alert("The end date cannot happen earlier than the start date");
        return false; // Cancel the item save process
    return true;  // OK to proceed with the save item

Reassign a SPD Workflow to Another List

One of the main drawbacks of SharePoint Designer-based workflows is that you cannot reuse an existing workflow in another list.

Recently, I accidentaly broke the list functionality editing the forms in SharePoint Designer. I had no way back except deleting and recreating the list. However, my pretty big SPD workflow would be lost…..


Well, not exactly. I took careful notice of the existing list GUID and the new list GUID. Then, I opened the XOML workflow file in text mode (see below). I then replaced all the ocurrences of the old list GUID with the new list GUID. I did the same for the wfconfig.xml file.


And, it worked! My old workflow works flawlessly assigned to the new list.

Of course, the fields that your workflow uses must keep the same name in the new list.

“Access Denied” when Editing Task in a Workflow

This one was *really* weird stuff.

– There is a SPD-created workflow that creates successive approval tasks, using Collect Data From a User action
– This workflow is hosted on a subsite that has unique security
– After the workflow is created, I add a new user with Full Control permissions over the site, let’s call him johndoe
– Johndoe initiates a workflow, as he wants to try the workflow
– The workflow creates a task for another user, as expected
Johndoe clicks the task in the task list, it shows the task data, as expected (johndoe has Full Control over the site, right?)
Johndoe clicks the “Edit Item” to impersonate the user
“Access denied” message appears!

I wandered why a user who has Full Control rights over the site cannot edit a task. The task itself had inherited permissions from the host site, giving johndoe Full Control over itself. Extensive Googling took me to this MSDN Forum post.

I tried the suggested solution of publishing the site to a local disk. After this, johndoe can edit the task. It smells like a bug inside SharePoint workflow infrastructure.

Vaio, Sigmatel sound card and Vista

As I recently reinstalled Vista (as I explained in my previous post), all the drivers for my Vaio laptop had to be reinstalled too. It all went well, except for the sound card. It’s a Sigmatel card (High Definition Audio Device).

The problem was that the sound output was OK, except when the headphones were plugged in. In that case no sound was heard, neither from the speakers nor from the headphones.

The solution was not so easy to find, but it’s simple enough:

  • Plug the headphones in
  • Right-click the speaker icon in the system tray and choose “Playback Devices”
  • Select the headphones and click “Set Default” button

Common Pitfalls in SharePoint Designer Custom Actions

I crafted a handful of custom workflow actions for SharePoint Designer recently and I noticed several recurrent errors:

  • When you select a custom activity from the Actions drop-down, nothing happens
    • Usual suspect: the class name and assembly reference do not match in ACTIONS file and DLL. Please check that your class reference in the Action tag in the ACTIONS file exactly matches your DLL full name.
    • Not so usual suspect: SPD has a cached reference of your old DLL. Exit SPD, delete ApplicationDataMicrosoftWebSite Cache folder and restart SPD.
    • Even less usual suspect: You’ve correctly placed the ACTIONS file and the activity DLL, but you haven’t added an authorizedType element into your web application web.config file. Add the correct entry (you can check the whole process here) and try again.
  • When you bind a property to a value, it doesn’t get set
    • Usual suspect: the demoted property name in your DLL and in ACTIONS file do not match. Please check that all your properties in the code exactly match the Parameters section elements in your ACTIONS file.

I hope that these simple advices save you valuable time. Happy programming in the WF world!

Email a Link to the Current List Item from SPD Workflow

If you use the built-in activity of SharePoint Designer workflow “Send email”, you can embed the URL of the current item (“Absolute Encoded URL”). However, this approach makes a URL of this kind


which is wrong.

The workaround is to use the SharePoint DispForm.aspx page and append an ID from the current item:


(of course, being ListItem your own list name in SharePoint Designer)

Patch for SharePoint Workflow Time-based Activities

Today I’ve been working on a “Early Warning” workflow designed with SharePoint Designer. It is supposed to run on every item change and to suspend itself until few days before item’s expiry date (it’s just a date column). But, it didn’t work as expected.

I remembered reading that there are some glitches in the way Workflow Foundation dehydrates and rehydrates the “sleeping” workflows. There is a patch available at http://www.microsoft.com/downloads/details.aspx?FamilyID=6096ce0f-d21e-47ac-afe2-d4e1c2fce670&displaylang=en.

I downloaded it and it worked like a charm!

Authentication when consuming a web service with Integrated Authentication

The most convenient way is to pass the user credentials from the client to the web service, providing a DefaultCredential object to the Credentials property of the web service proxy:

ws.Credentials = System.Net.CredentialCache.DefaultCredentials;

before invoking the web service.