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…..

 wkfReassign2

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.

 wkfReassign1

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.