06 February 2008

Controlling the Result Type for Calculate Fields

I recently was faced with the problem of rendering a calculated field using a feature. The feature creates and provisions a set of columns and content-types available for use in the site. However, one of the problems I faced was creating a calculated field that needed to render it's output as currency. Since SharePoint users the defaults when defining new fields, it was performing the calculations correctly, but displaying them as raw, unformatted text. When looking through the SDK, there doesn't appear to be any way to control the content (or it has not yet been documented). So, I decided I'd let SharePoint help me out. I created a new list and added several currency columns to the list. I then created a calculated column that would subtract the last currency number from the first currency number. Next, I saved the list as a list definition and downloaded it to my local computer. The .stp file that is created is nothing more than a CAB file. I appended the filename with .cab and then opened it up. Inside is a manifest.xml file that contains the definition of the list (if you're ambitious enough, you could create your own list definitions and upload them by reversing the process). Opening the manifest.xml file, I then locate all of the field definitions for the list. This is where I found the payload. There is an attribute that can be added to a calculated column definition to control the result of the calculation. It is the ResultType attribute. So, here's my definition:

          <Field Type="Currency" DisplayName="Val1" Required="FALSE" 
                Decimals="2" LCID="1033" ID="{66df31d6-dd22-4a17-8d4f-5f4b9a1be790}" 
                SourceID="{02d65904-8cbb-407d-9668-67441581ed38}" 
                StaticName="_x0056_al1" Name="_x0056_al1" ColName="float1" 
                RowOrdinal="0" Version="1"/>
          <Field Type="Currency" DisplayName="Val2" Required="FALSE" 
                Decimals="2" LCID="1033" ID="{66c6624c-88fb-4d26-9c5b-3615548b76db}" 
                SourceID="{02d65904-8cbb-407d-9668-67441581ed38}" 
                StaticName="_x0056_al2" Name="_x0056_al2" ColName="float2" 
                RowOrdinal="0" Version="1"/>
          <Field Type="Calculated" DisplayName="Var" Format="DateOnly" 
                Decimals="2" LCID="1033" ResultType="Currency" ReadOnly="TRUE" 
                ID="{a415e859-b8f9-4168-a63d-e5fbcbbfba7b}" 
                SourceID="{02d65904-8cbb-407d-9668-67441581ed38}" StaticName="Var" 
                Name="Var" ColName="sql_variant1" RowOrdinal="0">
                <Formula>=_x0056_al2-_x0056_al1</Formula>
                <FieldRefs>
                   <FieldRef Name="_x0056_al1"/>
                   <FieldRef Name="_x0056_al2"/>
                </FieldRefs>
          </Field>
As you can see, the calculated field's result type is controlled by the ResultType attribute. I took this new found knowledge back into my project, plugged it in and everything worked just as expected!

01 February 2008

Create a Zero Code Web-Based Training Site

One of the best tips I've come across recently has been the use of a Creative Commons Attribution 3.0 License javascript library called LyteBox. This library allows you to incorporate some pretty cool effects into SharePoint using a simple content editor web part. Using this javascript library, a content editor web part and SharePoint Designer, my plan is to walk through creating a simple training site that gives users a Web 2.0 feel to training content.

First, download the scripts from the link above. After you have downloaded the scripts, create a new blank site. We're going to add our own lists and libraries to this site, so there is no need for us to have any existing content.

Getting LyteBox Setup

When you're new site has been provisioned, create a new document library called LyteBox and place all of the scripts and images into this library. Be sure to place all of the images in a new folder in this library called images.

 

Now, edit the page of your site and add a new content editor web part to top-right zone of the homepage (default.aspx). Edit the shared web-part and include the following lines using the source editor:

<script type="text/javascript" language="javascript" src="lytebox/lytebox.js"></script>
<link rel="stylesheet" href="lytebox/lytebox.css" type="text/css" media="screen" />

Change the Appearance of the web part and turn off the chrome. This way, the web part will not be displayed at all. If you'd like, export this web-part for reuse in other places in SharePoint.

Creating Training Libraries and Lists

Next, we need to create a few libraries and lists to use for our site. I need a library to contain basic HTML pages and resources for those pages, a list to create categories for the training labs and a list to contain the actual training labs.

Create a new document library called Resources and upload a few sample HTML pages (or create them with SharePoint Designer or a similar tool). This will be the place that you can upload all of the materials needed for your training labs. Our organization will be creating Camtasia projects for the users to review. You could include anything that is HTML based for your users in the library.

Next, create a custom list to contain a list of categories. I named this list Categories and added the following items: Excel 2007, Word 2007, SharePoint 2007. This list will be used as a lookup for categorizing your training sessions (and as a connected web-part for filtering the list of available labs).

Now, the next list is the "meat and potatoes" list because this is where you will be organizing the labs that are available. Create another custom list called Training Labs and add the following columns to the list:

  • Category, Lookup, Required -- linked to Categories - Title
  • Description, Multiline
  • Width, Number
  • Height, Number
  • Resource Link, HyperLink/Picture -- set to hyperlinks

At this point, you may want to create some items for testing when we get into SharePoint designer. The idea is each item will provide a link to the resource using the resource link column. This will be linked to an HTML page in the Resources library. The width and height columns will be used to control how large the LyteBox window becomes using some cool SharePoint designer items.

Now the basic lists are available and ready to use for your site. Its time to start wiring up all the pieces with SharePoint Designer.

SharePoint Designer

Open the site using SharePoint designer and then double click default.aspx to open the page. In the toolbox (or click Task Panes), go to the Data Source Library and locate the Training Labs list. Click on the list and choose Show Data. In the list, highlight the title column and drag this over to the default.aspx page right zone. Drop it in place below the content editor we placed in the page earlier.

Now, using the Common Data View Tasks Widget, modify the columns you wish to display. I added in the description and category fields to my display. Click on the widget for the title column and change it into a hyperlink column. For the hyperlinks properties, make sure the Text to Display is set to {@Title} and the Address is set to {substring-before(@Resource_x0020_Link,', ')}. The easiest way to do this is to click on the Fx button for Address and choose the Resource Link from the list and then do the same for the Text to display.

 

Now for the fun part. You will need to make some modifications to the tag properties of the hyperlink so the LyteBox script will execute on the link. In the rel attribute, set it to lyteframe. Then locate the rev attribute and choose the function box. Make sure to use the following function in the dialog:

concat('width: ',@Width,'px; height: ',@Height,'px; scrolling: no;') 

Now, your properties should look something like the following:

 

You should be ready to save your page and begin testing it out. If you have been successful, your resources should load up in a Web 2.0 feeling box that dims the surrounding area and displays the content in the box.

Filtering Content

The final step to make this site more useful is to allow your users to filter the content based on the category. To do this, in SharePoint, edit your page and add a web part for your Categories list to the Right zone (you can also do this from inside of SharePoint Designer, but I prefer SharePoint's ability to set the properties of the web part).

Go back into SharePoint designer and reload the page. Right click on your Categories Web Part and choose Web Part Connections.

  • Set the connection to Provide Row To and click Next.
  • Choose to Connect to a Web Part on this Page and click Next.
  • Choose the Target Web Part as your Training Labs DataView and the Target Action to Get Filter Values From
  • Match up the columns between the Categories List and the Training Labs List. This should be to set the Categories List to Title on the same row as the Training Labs category row.
  • Click on Finish.

Now save the page and load it up in your Browser. Test that the filter gets applied and you can now review training labs based on the way your filter is set.

I'll see if I can't upload the template I've created for you to dissect and play with. My final template looks like the following:

 

24 January 2008

Word 2007 Mail Merges with SharePoint Lists

Here's an interesting tip that I was able to contribute to EndUserSharePoint.com. This might be a good tip for developers as well as end users. The post describes how to make use of a SharePoint list in Word by connecting to an Access 2007 database.

23 January 2008

ExpGroupBy(obj) Javascript Function

There are times that I am glad to be a SharePoint developer. Today is one of those days! I have been working on a web part that needs to roll-up a lot of data from multiple sub-sites for a dashboard style view. The view needs to group the data by two separate criteria (Unit and Category). Additionally, the desire of the project sponsor is to have all of the items be in a collapsed view with the ability to expand those items on demand. I begin thinking to myself, "Great -- I get to play with Javascript!" Now, repeat that statement with as much sarcasm as you can muster and you have a good idea of my state of mind.

I then remember that I can use SharePoint Designer to create a view similar to my target view. Okay, let's create a list and add several items to it. I now have my "test data" to begin my experiments. Opening up SharePoint Designer, I create a dataview of my list and then specify two levels of grouping. I save the page and then open up the page in IE.

I play with the grouping and find that it is exactly how I want to implement this functionality for my client. So, the fun work of dissecting code begins. During my investigation, I notice that there is a call to a javascript function ExpGroupBy being issued when I click on the handy 'plus' and 'minus' images. After further investigation, I learn that this is a function available in the core.js script file. Hurray! I can use this!

So, I make the necessary modifications to my web site and create my headers and footers for each group, assign unique ID's and then test it in IE... crash! The javascript doesn't work. Here's a sample of the table I was using:

<tr id="group0" style="display: block">
<td class="ms-gb" style="background: #cccccc;" colspan="8">
<a href="javascript:" onclick="javascript:ExpGroupBy(this);return false;">
<img src="/_layouts/images/minus.gif" border="0" alt="expand" name="collapse"></a>
UNIT 10000</td>
</tr>
<tr id="group1" style="display: none;">
<td class="ms-gb" style="background: #cccccc;" colspan="8">
<a href="javascript:" onclick="javascript:ExpGroupBy(this);return false;"><img src="/_layouts/images/minus.gif" border="0" alt="expand" name="collapse"></a>PERSONNEL EXPENSE</td>
</tr>
<tr>
<th class="ms-vh">Cost Center</th>
<th class="ms-vh">Description</th>
<th class="ms-vh">Prev. Budget</th>
<th class="ms-vh">Prev. Actual</th>
<th class="ms-vh">Current Budget</th>
<th class="ms-vh">YTD Actual</th>
<th class="ms-vh">Proposed Budget</th>
<th class="ms-vh">Increase/Descrease</th>
</tr>
<tr class="ms-alternating" style="display: none;">
<td>
10-10000-1000002</td>
<td>
<a href="http://sharepointdev/sites/budgtest/10000/Lists/Cost Centers/EditForm.aspx?ID=3&Source=/sites/budgtest/Shared Documents/Centers.aspx">Sample Cost Center</a></td>
<td align="right">
$25,365.00</td>
<td align="right">
$25,365.00</td>
<td align="right">
$25,365.00</td>
<td align="right">
$4,289.39</td>
<td align="right">
$0.00</td>
<td align="right">
$29,500.00</td>
</tr>

.....

<tr id="group2" style="display: none"><td class="ms-gb" style="background: #cccccc;" colspan="2"> PERSONNEL EXPENSE Totals: </td> <td class="ms-gb" style="background: #cccccc;" align="right"> $439,370.00</td> <td class="ms-gb" style="background: #cccccc;" align="right"> $439,370.00</td> <td class="ms-gb" style="background: #cccccc;" align="right"> $439,370.00</td> <td class="ms-gb" style="background: #cccccc;" align="right"> $141,865.06</td> <td class="ms-gb" style="background: #cccccc;" align="right"> $0.00</td> <td class="ms-gb" style="background: #cccccc;" align="right"> $29,500.00</td> </tr> <tr id="group3" style="display: none"><td class="ms-gb" style="background: #cccccc;" colspan="2"> PERSONNEL EXPENSE Totals: </td> <td class="ms-gb" style="background: #cccccc;" align="right"> $439,370.00</td> <td class="ms-gb" style="background: #cccccc;" align="right"> $439,370.00</td> <td class="ms-gb" style="background: #cccccc;" align="right"> $439,370.00</td> <td class="ms-gb" style="background: #cccccc;" align="right"> $141,865.06</td> <td class="ms-gb" style="background: #cccccc;" align="right"> $0.00</td> <td class="ms-gb" style="background: #cccccc;" align="right"> $29,500.00</td> </tr>

So I loaded this up in IE and to my surprise it ALMOST worked. When I clicked on the unit expand/collapse, the entire table would be collapsed from that place. So if I had multiple units, I would only see a single unit. The same thing would happen if I collapsed a category -- all units and categories below would also collapse.

After further investigation, I learned that the function works by examining the ID of the table row containing the group header and footer. This is how it determines the current level you are viewing to collapse the correct items. So, for my example I needed two group ID's, group0 and group1. Each first level group row header/footer (Unit) was assigned the ID group0 while all second level group rows header/footer (Category) were assigned the ID group1. Once I figured this out, the function began working properly!