File attachment and Infopath 2003 SP1

We have been working on a small project using Infopath 2003 and MOSS to create a faster way to input information in a list. Among the challenges (which was quite a few despite it was software from MS on both "sides") faced was how to enable uploading a file while submitting information to a list.

Infopath 2003 provides a File Attachment Control which enables the end-user to select and attach a file to the form. Infopath automatically encodes the file in Base64 and adds a dynamic header in the front of the file. So we wanted to use the "AddAttachment" web service available to post the file, one of the parameters is a Base64 encoded string. Great! Well, the header added by Infopath is also included in the encoding. We only have Jscript or VBScript to use in Infopath 2003 if we don´t want to start to deploy extra code on the clients (which I try to prefer to stay away from) and the support for decoding/encoding files in those languages are not to great.



So our solution is to extract the filename from the header (code below) and the attach a list item event receiver for "ItemAttachmentAdded" which decode the file, removes the header and saves the file again serverside.



Here is the code for extracting the filename from an Infopath Base64 encoded attachment:



dim node

Set node = XDocument.DOM.documentElement.selectSingleNode("/my:myFields/my:FileAttchmentControl")



If(node.text <> "") Then


'typecast the node to base64 as Infopath doesn't do this in node definition

node.DataType = "bin.base64"

'convert base64 node value to binary and store in nodeValue byte array

nodeValue = node.nodeTypedValue



dim convertByteArrayToHexString

convertByteArrayToHexString = ""

For intCounter = 1 to LenB(nodeValue)

convertByteArrayToHexString = convertByteArrayToHexString & Right("0" & Hex(AscB(MidB(nodeValue, intCounter, 1))), 2)

If(intCounter = 100) Then 'Assuming that the header is no longer that 100 bytes
exit for
end if

Next

dim filenameBuffer
dim filename
filename = ""
filenameBuffer = 0

'The length of the filenamebuffer is stored on pos 20-24 (byte), which means 41.47 in Hex and 1 as startpos for Arrays

filenameBuffer = getFilenameBuffer(convertByteArrayToHexString,41,47)



'Read the filename from position (49 to filenamebuffer * unicode - "\n") as end char

filename = getFileName(convertByteArrayToHexString,49,filenameBuffer*4 + 49 - 2)

Function getFilenameBuffer(data,startLoop,stopLoop)
dim k

dim filenameBuffer

for k = startLoop to stopLoop step 2

dim y1
dim y2
dim y3

y1 = Mid(data,k,2)
y2 = CLng("&h" & y1)
y3 = y3 + Int(y2)

filenameBuffer = y3

next

getFilenameBuffer = filenameBuffer

End Function

Function getFileName(data,startLoop, stopLoop)
dim k

dim filename

for k = startLoop to stopLoop step 4

dim h1
dim h2
dim h3

h1 = Mid(data,k,2)
h2 = CLng("&h" & h1)
h3 = Chr(h2)

if(h3 <> "") then
filename = filename & Cstr(h3)
end if

next

getFileName = filename
End Function

SharePoint Designer - now for free

Microsoft offers SharePoint Designer for free. See the SharePoint Team blog post for more information.

Additional user information import with BDC

Been working on an secondary connection to a SQL Server database that will be used to add more information to the user profile properties. Solution includes the BDC application but foremost setting the correct access permission for the account accessing the BDC application.

I use "RevertToSelf" in the ADF which makes the access to the BDC run with the account for the service "Office SharePoint Search Server".

So the account needs "Execute" permissions for the BDC application and all entities, DBDataReader to the SQL Server (or any other read permission) and finally "Manager User Profiles" under "Personalization Permissions". When troupbleshooting this the ULS is yor friend.

Make sure that no other accounts gets permission to the application in BDC, you want to avoid to expose the BDC application to the built-in web parts.

Moving to WSPBuilder

Attended the first Sweden SharePoint User Group Malmö meeting (phew, that was long) yesterday. Karsten was there and talked about his tools WSPBuilder and SPM2007.

We have been using STSDev for sometime now, but it feels like the development have stalled and other participants also noted that you need to fix bugs in the tool to make it work in different situations. So we have decided to switch to WSPBuilder instead for coming projects. Hopefully it cuts down the time developers needs to write code in the OM.

Response to "JQuery for Everyone: Total Calculated Columns"

Finally there is somekind of light-weight workaround for Calculated Columns and Totals using jQuery and the beloved CEWP. Find the original post here: http://www.endusersharepoint.com/?p=1106 (great work by the way :) ).

I have made an extension to this, so instead of just calculating the last column one can add any column for a total sum.
I use the sortable element ID to find the cellIndex for all rows that needs to be calculated (diidSortMean = a calculated column with the namne "Mean"). I use IE Dev Toolbar for that purpose.

The script currently calculates a total for three columns, but can easily modified in the CalcGroup function. My list is also expanded as default, will do more testing in the weekend.

If you find bugs, please leave a comment.


<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js" type="text/javascript"></script>
<script type="text/javascript">

function addCommas(nStr) {//formats number
nStr += '';
x = nStr.split('.');
x1 = x[0];
x2 = x.length > 1 ? '.' + x[1] : '';
var rgx = /(\d+)(\d{3})/;
while (rgx.test(x1)) {
x1 = x1.replace(rgx, '$1' + ',' + '$2');
}
return x1 + x2;
}

function getCellIndex(elId)
{
var myColumn = $(elId);
var myParent = myColumn.parents('th');
var result = myParent.attr("cellIndex");
return result;
}

function getArrayList(varIndex, groupName)
{

return $("#tbod"+groupName+"> tr").find(">td:eq(" + varIndex+ ")").get(); //array for groups based on cellIndex
}

function getArrayForOutput(varIndex, groupName)
{
return $("#aggr"+groupName+"> tr:first> td:eq(" + varIndex + ")");
}

function CalcGroupPerColumn(groupName,varIndex)
{
var x = 0;
var m = "";
var p1 = "";
var p2 = "";
if (groupName == '') {
$("table.ms-listviewtable:first> tbody:eq(2)").attr("id","tbod");
}

var arrayList = getArrayList(varIndex,groupName);

$.each(arrayList, function(i,e){
x += Number($(e).text().replace(/\$|,|\)/g, "").replace(/\(/g,"-"));
if ($(e).text().indexOf("$") >= 0) { m = "$" };


});
if (x < 0) {//format for negative numbers
p1 = "(";
p2 = ")";
x = Math.abs(x);
}

var aggEl = getArrayForOutput(varIndex,groupName);

if (arrayList.length > 0) {
aggEl
.css("text-align","right")
.html("<b>Total = "+p1+m+addCommas(x.toFixed(2))+p2+"</b>");
}
}


function CalcSqrtPerColumn(groupName, varIndex, sourceValue)
{
var aggEl = getArrayForOutput(varIndex,groupName);
var sqrtVal = Math.sqrt(sourceValue);

if( sqrtVal > 0 )
{
if (aggEl.length > 0) {
aggEl
.css("text-align","right")
.html("<b>Sqrt = "+ addCommas(sqrtVal.toFixed(2)) + " </b>");
}
}

}


function CalcGroup(groupName) {//sums money in specific list column

var meanCellIndex = getCellIndex("#diidSortMean");

var stdDevCellIndex = getCellIndex("#diidSortStd_x002e_dev");

var varianceCellIndex = getCellIndex("#diidSortVariance");

CalcGroupPerColumn(groupName,meanCellIndex);
CalcGroupPerColumn(groupName,varianceCellIndex);
CalcGroupPerColumn(groupName,stdDevCellIndex);

}
//rewrite of WSS function
function ExpGroupRenderData(htmlToRender, groupName, isLoaded)
{
var tbody=document.getElementById("tbod"+groupName+"_");
var wrapDiv=document.createElement("DIV");
wrapDiv.innerHTML="<TABLE><TBODY id=\"tbod"+groupName+"_\" isLoaded=\""+isLoaded+"\">"+htmlToRender+"</TBODY></TABLE>";
tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
if (CalcGroup) {
CalcGroup(groupName+"_");
}
}
$(function() {
CalcGroup('');
});
</script>


New employeer

Hi all,

Back after a nice christmas vacation and with a new job. I have started to work as a SharePoint consultant for MMP AB in Malmö, where I´ll lead a new focus area regarding SharePoint.

// Henrik

off-topic: My grandmother Hilda celebrates her 100:th birthday

Link to the local magazine in Varberg

// Henrik

ChiliRec

This is the far coolest I have seen for quite some time. A digital recorder for online radio stations. http://www.chilirec.com. Check it out!

Playlists, top lists, browse per country...

The next thing I need to solve is to have this thing run on SqueezeCenter / SqueezeBox or Popcorn Hour. Might there be a SDK somewhere?

SJ - Internet ombord

For the next trip, http://www.ombord.info/SJ/v5/default.asp, trying to use X1 as a browser.

SPD Workflows

Error message in SharePoint Designer:
This website does not support workflows created by SharePoint Designer

My solution:
Only site collection administrators was able to create the workflows. Add the user as a site collection administrator.

Getting picky in CAML - Good

I have code in Excel that uses XMLHTTP to call SharePoints web services. The code contains a CAML query where I had a field reference to a Choice field , , this column has been converted to a Text (Single line of text) column and the query didn´t return any items anymore when running against MOSS 2007 instead of Wss 2.0. Then I updated the field reference to and the query returned items again.

Sad

Patrick Tisseghem has suddenly passed away and left a big hole in the Sharepoint community. He was one of the first bloggers I found before the release of MOSS 2007 and I was fortune to once meet him during a training session. My thoughts goes out to his family.

// Henrik

STSADM and SharePoint Designer operations failed

I was getting error messages (cannot delete folder "WEBNAME") for deleteweb operation for stsadm and when opening the web in SharePoint Designer (SD) I got "There is no web "/XXX/YYY". Couldn´t delete documents but the web was browsable in SD (error code 16388). I finally tried to attach to the database in Visual Studio and got an error message about full transaction logs on the database server. In this case we configured "simple mode" instead and all operations completed sucessfully.

So know your DBA and have them close :)

// Henrik

Multiple methods in BDC

ok, I have been looking for an answer to this question:
How can one entity contain several methods in BDC?

The answer is to define a MethodInstance with a type of GenericInvoker, this enables you to define a method on your entity which can be accessed via code but that is not exposed for the OOB BDC webparts. See http://agiledirect.wordpress.com/2007/08/09/bdc-writing-to-bdc-using-genericinvoker/ for an example.

You can also use the type ViewAccessor to return a different set of data which can be used in the OOB BDC webparts. See http://znetsys.spaces.live.com/blog/cns!ED65D09C41E55E06!115.entry for an example.

Crawling BDC applications

Errors during crawling BDC apps? bdc2:// and (access denied by BDC) errors? Check this article, still problem? Search the log file in 12HIVE/Logs for "Access denied" to see which account that is casuing the problem. Still problem? Export the ADF and uncheck "Permissions", delete the Application and import the ADF again (still finally solved my problem)

Why BDC columns doesn´t work in Office / Doc Mgmt

Using BDC columns in document libraries feels as a great option, but when trying to use it for any serious document management (using mostly OOB functions) it fails for the following reasons:



1. A BDC column can not be used as a field reference in Word. If you want to display information in header or footer the field reference can not be used.



2. The BDC column can not be configured as a site column (MacroView is workaround, but it can not be edited from the DIP). Major drawback to any usage of content types.




4. If you click the refresh button in the document library all your documents will be checked out again.


We are developing a workaround based on "Choice" columns and eventhandlers instead.

BDC on the other hand is great if you want to display information in web parts :)

Off topic: Memories

I was watching a trailer for the swedish program Vetenskapsprogrammet and beside the presenter they had something running on a screen that really looked like the old Spaceballs demo State of the Art, a great demo 1992 for Amiga, it was one thing that made me want new hardware :). I still was using my good old C64, but great demos was also released 1992, just check this list:

Prometheus Unbound by Flash Inc
Red Storm by Triad
Crest Avantgarde by Crest
Wonderland IX by Censor Design
The Last Traktor III by Horizon
Legoland 2 by Fairlight

Get your C64 emulator from Team Vice and start downloading from The C-64 Scene Database.

- Dealer Quality Software -

Off topic: New hardware arrived

The quest to get rid of the media plastic (CD/DVD) continues. Today a Popcorn Hour (PCH) got installed and hooked up to the TV. The PCH will read video files from the MCE, show pictures and sometimes play music. But it also comes packed with web services and other features. So now we can enjoy all lunatics on YouTube and Google Video, use the built-in Torrent client, and use the PCH as NAS if needed, read RSS feeds in the living room and so on. And whenever we decide to get a HD TV the PCH can playback HD movies directly.

So a quick summary: one MCE with touch screen in the kitchen which holds all media and for kitchen TV and sound, one SqueezeBox 3 for music playback and Internet Radio and now a PCH for movie playback and other online goodies.

The nice thing with all the hardware is that the vendors provide a possibility to add software and extend the OOB experience.

Which I´m most impressed with? The SqueezeBox was a really nice experience, mature product, easy to install, works OOB and can be enhanced with modules from the SqueezeBox community.

Swedish SharePoint bloggers

http://www.dewe.net/sharepoint/2008/02/svenska-sharepoint-bloggare.html

GUID changes

Hi,

Been a while...

Currently involved in a large migration of WSS v2 to MOSS. Found a thing that can be good to know. If you use the export and import actions in stsadm (WSS V3/MOSS) your lists will get new GUIDs when imported. This has consequences for dataviews (use this trick to remove the web part from the page) or custom web parts that binds to the GUID.